I have a sql to pull all payments from vendors for a specific time period; however, now tasked to only show the Address of each Tax Reporting Site. I have tried several commands but have not been successful.
View 7 RepliesI have two tables emp , emp address as below
emp table :
emp_id emp_name,
1 rajesh
2 suresh
emp address table :
emp id emp_addresstype emp address
1 O kukatpally
1 H Hitech
2 O Kolkata
2 H hydar nagar
I need the query to display the Office address & House address & emp id in single query
ex: 1,Kukatally,hitech
2,kolkata,hydarnagr
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
View 16 Replies View RelatedI am using Oracle 11G. I have set of tables in a schema - DDXX_UTIL.
Table Names wll be with the high level qualifiers DDXX_REJ_EMP*
DDXX_REJ_EMPLOYEE
DDXX_REJ_EMPLOYEE_DEPEND
DDXX_REJ_EMPLOYEE_ADDRESS
DDXX_REJ_EMPLOYEE_SAL
DDXX_REJ_EMPLOYEE_EXP
My requirement is to get the number of rejections happened on that day after the batch cycle for the tables with the high level qualifier DDXX_REJ_EMP* and list out each table followed by the rejection cause and count.
Expected Result
-----------------------------------------------------------------
TABLE NAME : DDXX_REJ_EMPLOYEE
REJECTION_CAUSE COUNT
ID LIST MISSING 25
MANDAORY FIELDS IS NULL 56
TABLE NAME : DDXX_REJ_EMPLOYEE_DEPEND
REJECTION_CASUE COUNT
ID LIST MISSING 25
MANDAORY FIELDS IS NULL 56
Given below is the create statement for one of the table.
CREATE TABLE DDXX_UTIL.DDXX_REJ_EMPLOYEE
(
REJECTION_CAUSE VARCHAR2(510 BYTE),
INTEG_REJ_DATE VARCHAR2(10 BYTE)
);
SAMPLE_DATA
DDXX_REJ_EMPLOYEE
REJECTION_CAUSE INTEG_REJ_DATE
INVALID ID 31-OCT-10
INCORRECT FIELD 31-OCT-10
INVALID ID 31-OCT-10
INCORRECT FIELD 31-OCT-10
I need to group by the rejection cause and integ_rej_date for all the tables.
I am using OEM Grid control 10g for monitoring/reporting purposes and a bit new to OEM.I am able to generate database alerts sent via email say for DB_abc1 and also generate reports.I installed Oracle 10g agent on a another box and lets say with DB_abc2 running on it,and did not realise that there was a database control already installed on that box, so I
1.Uninstalled database control on DB_abc2 box.
2.Uninstalled Oracle 10g agent and re-installed it on DB_abc2 box.
2.Was able to configure the DB_abc2 database from OEM Grid control gui ie the dbsnmp user password and was able to connect to it.
But when I put in alert for things like ORA-XXXX errors I am not getting any emails, also when I put in database usage reports(to be sent via emails) the reports are not picking up any data, they are empty.
I checked the agent on the box in question and the heart beat is OK, and I manually uploaded using "emctl upload" and it is sucessful.The only alerts I am getting for DB_abc1 is the agent up/down thats it.
DB_abc1 is a 9208 database and DB_abc2 is 10201 database.
this Topic was already discussed in "Physical standby on oracle 11g - Reporting needs" but without finishing.
View 5 Replies View RelatedI have a table PRODUCT with following structure:
Productcode Productname Quantity
AF Ade Fgh 100
LO Ldo Ope 50
SK Ske Kro 47
....
There may be any number of records in the table. Now I need to print distinct products in different columns such as.
AF LO SK
100 50 47
There may be any number of products...I need to print all those products as different columns as shown above. How to write the Stored procedure for this?
I need write a query based on a bunch of user supplied IDs. The IDs will be pasted as plain text, one per row, by end user in a memo field in the reporting environment, and I need to do something like this:
SELECT PHONE_NUMBER FROM TELEPHONE
WHERE
ID IN('MEMO_ID1', 'MEMO_ID2',.....)
Reporting environment does not provide any tools to automatically convert plain text into IDs.
We are copying our transaction tables data into another database for our reporting applications (say every day midnight refresh will happen).
The Transaction Database has some 30tables. Existing system is following below points and it is taking 2hours to complete.
1) Truncate data from reporting database (or schema)
2) Direct path Insert into reporting database (or schema) as select * from transaction tables.
3) Rebuild index and Enable constraints.
Note: Each tables data will vary from 30lakhs to 50lakhs. Dump/import/export is not advised by the client.
I want to cut down the time i.e., below 2hours. Instead of above method. Can go for a field in each table specifying the time of each records update/insert operation and then pick the modified records only and copy into reporting db.
I would like to know the Replication method which is fast and the best approach,we need two schemas to be moved/replicated to a new reporting database.It appears that data is to be flown in one way,do we proceed with Materialized view replication or please clarify about Oracle Streams and Advanced replication. what are the factors to decide the replication method.
View 3 Replies View RelatedI want to audit user connection on my reporting database, and send a report to application team on monthly basis, with a list of users who are not connected for a month and remove them.
What would be best method, i know there is LOGON trigger, or database level auditing.
My DBA gave me a table with only one date column say Table1.Date. Its in the format of Date and say it is = 7/23/2012.
Now i have to create my own Reporting View(which is used for reporting) based on that date column like below:
It should be a column with values in the following format =
2012-07
2012-06
2012-05 etc....upto
2010-01
So i started out my creating like this:
select
to_char(Table1.Date,'yyyy-mm')
from Table1
Union
[Code]....
.and so on till i get 2010-01.
there has to be a better way to do this.
So usually joining a local table and a remote table (much larger table), the best practice is using a /*+ DRIVING_SITE(remote table) */ hint.
*CASE1:
INSERT INTO another_local_table
SELECT /*+ DRIVING_SITE(remote_table) */
FROM local_table
,remote_table@remotedb
WHERE join condition
*CASE2:
However I saw this particular
FOR x IN Select id From local_table l
LOOP
INSERT INTO another_local_table
[code]...
So far I haven't seen the explain plan of both cases since most of the tables are Global temporary tables. But in terms of the logic of the two cases and the common best practice, logically doesn't CASE1 should have a better performance?
For me, its like taking a trip to a grocery. CASE1 buys all that you need, one time, it will take you a half-day trip perhaps. However CASE2 is like quickly buying a grocery item, one at a time, for several short trips. You'll save on gas on CASE1 right.
I get below error when i tried to add the master site to the master definition site.
SQL> BEGIN
2 DBMS_REPCAT.ADD_MASTER_DATABASE(
3 gname => 'TEST',
4 master=> 'testdb2',
5 use_existing_objects => FALSE,
6 copy_rows => FALSE,
[code].......
I have installed SQL navigator 5.0 version in my PC. Once it has been successfully installed, I clicked on SQL navigator icon in my PC after that it's prompted me for asking authorization Key. I have entered the following key.
3-37993-18701-28024-78347
Once I have entered the key is is prompting me for asking site message. what message I should enter.
m trying to create replications of data from the M.V. site. to master site
/* AT MASTER SITE */
1. MASTER REPLICATE GROUP
2. MASTER REPLICATE OBJECT
3. REPLICATION SUPPORT
4. REPLICATION ACTIVITY
5. M.V. LOG
/* AT M.V. SITE */
1. MATERIALIZED VIEW (M.V)
2. DBMS_REFRESH.MAKE (REFRESH GROUP)
AND I AM NOT ABLE TO CREATE
1. CREATE_MVIEW_REPGROUP
2. CREATE_MVIEW_REPOBJECT
IN M.V. SITE , and i am getting following error while creating REPGROUP
CODE
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
3 gname => 'emp_repg',
4 master => 'orc1',
[code]..
why we seem to be unable to report against the ap invoice hold release date using Discoverer in Oracle 11.5.10? the person who wrote our current report used a decode statement to look at the last update date of the release lookup code to create a release date, but i am trying to recreate this in a different tool (Qlikview) and just wanted to understand why we seem to be unable to report on the field as is!
View 4 Replies View Relatedwhile replicating the master site via materialized views, in mViewGroups i issued:
BEGIN
DBMS_REFRESH.MAKE (
name => 'mvadmin.hamza_refg',
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440', -- for test purpose i used such a small interval
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
in my previous attempt it worked all fine... i had a change in senerio, so i did my replication again from scractch using the same old spool in a new installation but even after 3 tries on replicating from scratch my objects are not getting refreshed automatically. even when i attempt to refresh the group it does not work. it works only when i refresh each of the participating object of the group manually.
I have a strange problem of connecting Oracle Forms6i from our Remote site.actually we change our IP address of lease line, we connect to it.but when we connect locally,forms are running without any problem,but when we connect globally or from other sites it can't able to connect,even sql prompt and toad also connect from remote site,but when we try to connect forms(forms runtime) it gives error message(ORA-12203: TNS:unable to connect to destination).when we connect this application from local application(Forms) it can connect well but from globally it can't able to connect.
View 1 Replies View RelatedOracle 10gR2 on red hat.
I have an application that writes blobs via pro*c. It is running at several sites without errors for a few years now. One of the newer sites has started reporting "ora-01578 ORACLE data block corrupted".
I've tried using rman backup validate - it does not report any errors.
I've tried using rman restore and recover to correct the problem.
I've tried deleting the file with the corrupt block and using rman restore and recover to recreate it.
In any of the above cases, I do not see any errors being encountered, and when I restart the application, it works for a while and then starts reporting "ora-01578 ORACLE data block corrupted".
I've had the client do a physical check of the disk and it indicated the media was okay.
We've failed over to the standby system, so I have a wee bit of breathing room - but I'm going to be away for 4 days and will have to get back on this when I get back. I'm a little puzzled that I can fix it for a short while and then it breaks again.
Do we have any plan to create DR ( disaster site) for ORACLE 10G standard version. Since, Data Guard is not available with Standard version, can we have any another plan for the same.
View 5 Replies View Relatedi want to create tab menu like in sumneva site URL.....
View 13 Replies View Related1.What are the basic things we need to analyze before we recommend the business to go for oracle Dataguard for DR setup?
2. Do you know any technology that will replicate the schema from one datacenter[one primary database ] to another datacenter [secondary or replication database]?
3. What are the various option to configure the disaster recovery plan at schema level?
I working on a Production Environment, where our Database is running on a IBM-AIX platform.I need to implement Logical standby database for reporting purposes, in order to improve the performance.Now the transaction and the reporting is done at the same site itself.I want to know that, operating system and platform architecture (RAM size, no. of CPUs, ORACLE_HOME etc..) on the primary and standby systems must be same or can be different. complete configuration for Dataguard. My os version is,
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
I am new to Oracle and a beginner in Data Guard.
1) How to find archive log gap from Primary Database site?
2) Do I need the DB_UNIQUE_NAME when setting the LOG_ARCHIVE_DEST_2 ? What is the purpose of DB_UNIQUE_NAME in LOG_ARCHIVE_DEST parameter?
3) If archivelog gap happens standby db goes out of sync with the primary database. What does out of sync actually mean?
4) Primary DB knows where to transport redo data based on the location mentioned in LOG_ARCHIVE_DEST_n of Primary DB. Am I correct in my understanding?
We have an Oracle application 12.1.1 running with oracle database 10.2.0.4 and we're using HP-UX itanium 11i v3 servers for both application and database, our requirement is to establish a Disaster Recovery site out of our building to ensure data protection.
What is the best option we can use to reduce the cost and to implement the DR site? can we use data guard ? and will be the configurations and requirements for it?
I want to remove the master site which is in the multi master replication environment.
I have a doubt here. When I try to suspend the master activity,I need to give the gname.
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'NAGADMIN');
END;
/
Where gname=master group name.But I have 9 master groups in my database. If i give one of the gname present in the master site will suspend the replication of the entire database from the replication.
eg; i am only giving NAGADMIN gname in the suspend activity script.. I have other gnames like, NAGUSER,NAGAUTH, etc....
I want to know how can I find/take from In parameter who is xmltype an email address ?
My function: (clob is cast to xmltype)
function submit(session_id in number, claim in clob) return xmltype
For example - I have more e-mail addresses in this:
<emails>
wtfn00b1@yahoo.com
wtfn00b2@inbox.com
wtfn00b3@mail.com
...
</emails>
I know that there is `extract` function for xml. How can I know if there is break or space between the emails ?
How to get ip address in forms 6i ?
View 6 Replies View RelatedI intend to write procedures/functions in PL/SQL to parse Name field into various fields : Title, FirstName, MiddleName, LastName, Gender.
E.g:
Nguyen Van A
Nguyen Thi B
After parsing, the result will be shown as:
TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale
Parsed.gif ( 4.94K )
Number of downloads: 2
I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender = "F". Otherwise, Title = "Mr", and Gender = "M".
2/ Another procedure/function is [i]ParseAddress with the requirement as:[/i]Address field is divided into Street, Group, Area, Ward, County fields
E.g.:No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London
The result:
StreetGroupArea Ward County
No 6 Sum StreetGroup 8Area 2ABCD London
I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->it doesn't work.