Topic: SQL*Plus and PL/SQL >> Urgent help needed Pls....
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Urgent help needed Pls.... |
| Priya |
Posted: Feb 08, 2008 01:33:02 AM |
Total Post: 19
Joined: Jul, 2007
|
Hi,
We have a cursor query which will fetch the records from tables in two diff schemas when a particular column value is not equal.
SELECT A.ORACLE_HR_ID,A.PROCESS_CODE,A.ASSIGNMENT_INITIATION_DATE,A.HEAD_COUNT,A.CC2_CODE,
Getoucdid_Ohr(A.PROCESS_CODE),A.SUPERVISOR_ID
FROM gecis_hr.XX_GENPACT_OHR_EDB_PRIM_VEW@EDB_OHRPROD.LOCALDOMAIN A,RL_OUEMPLOYEE B
WHERE A.ORACLE_HR_ID = B.EMP_ID AND Getoucdid_Ohr(A.PROCESS_CODE) <> B.OU_ID
AND TO_DATE(A.ASSIGNMENT_INITIATION_DATE,'DD/MM/RRRR') = TO_DATE(SYSDATE,'DD/MM/RRRR') - 8
AND A.ORACLE_HR_ID = B.EMP_ID
AND A.ORACLE_HR_ID IN (SELECT EMP_ID FROM MS_EMPLOYEE WHERE APT_FLAG = 1 AND STATUS = 0)
AND B.PRIMARY_PROCESS= 'Y'
AND A.BUSINESS_GROUP_ID IN (82,84);
This query is not returning any rows. But when i add an emp id to the query at the last like A.ORACLE_HR_ID = '703022089' whose value is differing, it is returning a row.
Please help me how to optimize this query.
Thanks in advance.
Regards,
Priya |
|
|
 Orcl.sg |
| Posted: Feb 08, 2008 01:13:48 PM |  |
|
Total Post: 12
Joined: Dec, 2007
|
1)gecis_hr.XX_GENPACT_OHR_EDB_PRIM_VEW@EDB_OHRPROD.LOCALDOMAIN A,RL_OUEMPLOYEE B <== Instead of using DB link you could try to make the data available localy within the schema
2) Look into explain plan and try to eliminate full table scans by creating appropriate indexes
3) Use bind variables and avoid hard parsing
SELECT A.ORACLE_HR_ID,A.PROCESS_CODE,A.ASSIGNMENT_INITIATION_DATE,A.HEAD_COUNT,A.CC2_CODE,
Getoucdid_Ohr(A.PROCESS_CODE),A.SUPERVISOR_ID
FROM gecis_hr.XX_GENPACT_OHR_EDB_PRIM_VEW@EDB_OHRPROD.LOCALDOMAIN A,RL_OUEMPLOYEE B <== Instead of using DB link you could try to make the data available localy
WHERE A.ORACLE_HR_ID = B.EMP_ID AND Getoucdid_Ohr(A.PROCESS_CODE) <> B.OU_ID
AND TO_DATE(A.ASSIGNMENT_INITIATION_DATE,'DD/MM/RRRR') = TO_DATE(SYSDATE,'DD/MM/RRRR') - 8
AND A.ORACLE_HR_ID = B.EMP_ID
AND A.ORACLE_HR_ID IN (SELECT EMP_ID FROM MS_EMPLOYEE WHERE APT_FLAG = 1 AND STATUS = 0)
AND B.PRIMARY_PROCESS= 'Y'
AND A.BUSINESS_GROUP_ID IN (82,84);
If the query is not returning any rows then A.ORACLE_HR_ID,A.PROCESS_CODE,A.ASSIGNMENT_INITIATION_DATE,A.HEAD_COUNT,A.CC2_CODE,
Getoucdid_Ohr(A.PROCESS_CODE),A.SUPERVISOR_ID is not having matching columns A.ORACLE_HR_ID = B.EMP_ID and so on ...
|
|
|
|
|
| Garlapati |
| Posted: Feb 12, 2008 05:10:04 AM | |
|
Total Post: 30
Joined: Apr, 2007
|
Hi,
try this query i think this is ok.
i think no need to use dblink. this is used when ever u want to connect from database to another db.
u r in the different scehmas so that u can use like schema.tablename
i think it works fine .
let me know any mistake.
SELECT
A.ORACLE_HR_ID,
A.PROCESS_CODE,
A.ASSIGNMENT_INITIATION_DATE,
A.HEAD_COUNT,
A.CC2_CODE,
Getoucdid_Ohr(A.PROCESS_CODE),
A.SUPERVISOR_ID
FROM
gecis_hr.XX_GENPACT_OHR_EDB_PRIM_VEW@EDB_OHRPROD.LOCALDOMAIN A,
--- ====>schema.tablename
RL_OUEMPLOYEE B
WHERE
A.ORACLE_HR_ID = B.EMP_ID
AND Getoucdid_Ohr(A.PROCESS_CODE) <> B.OU_ID
AND TO_DATE(A.ASSIGNMENT_INITIATION_DATE,'DD/MM/RRRR') = TO_DATE(SYSDATE,'DD/MM/RRRR') - 8
AND A.ORACLE_HR_ID = B.EMP_ID
AND A.ORACLE_HR_ID IN (SELECT EMP_ID FROM MS_EMPLOYEE WHERE APT_FLAG = 1 AND STATUS = 0)
AND B.PRIMARY_PROCESS= 'Y'
AND A.BUSINESS_GROUP_ID IN (82,84);
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
