| ORA-04052 [message #195607] |
Fri, 29 September 2006 12:20  |
bvenkatrajan
Messages: 4
Registered: September 2006
|
Junior Member |
|
|
I am running the sql and it works fine over a dblink while I put the same query inside a cursor and am creating a procedure there is an error that gets generated during compilation. The link connects a Oracle system to a Sybase system.
1 CREATE OR REPLACE PROCEDURE SP_DAILY_REPORT_BY_COLLECTOR AS
2 V_MEL_STRT_TIME VARCHAR2(8);
3 V_MEL_END_TIME VARCHAR2(8);
4 V_CALL_HOUR VARCHAR2(11);
5 V_HR_STRT_TIME VARCHAR2(8);
6 V_HR_END_TIME VARCHAR2(8);
7 V_CTR NUMBER:=7;
8 BEGIN
9 LOOP
10 INSERT INTO VENKAT.DIALER_AGENTSUMMARY@DW_PRD.WORLD(AGENT_ID,AGENT_NAME,LOGON,TYPING,OUB_CNT,IN
11 B."Agent_ID" AGENT_ID,B."Agent_Name" AGENT_NAME,
12 SUM(LOGON_DURATION) LOGON,
13 SUM(TYPING_DUR) + SUM(INTYPING_DUR) TYPING,
14 SUM(OUTCNX_CNT)+ SUM(CNX_CNT) OUB_CNT,
15 SUM(INCNX_CNT) INB_CNT,
16 SUM(CNX_DUR) + SUM(OUTCNX_DUR) OUB_DUR,
17 SUM(INCNX_DUR) INB_DUR,
18 SUM(IDLE_DUR) IDLE
19 from
20 "mel.agent_states"@DW_MEL.WORLD A,"mel.Agent_Profile"@DW_MEL.WORLD B
21 where
22 A.AGENT_ID=B."Agent_ID"
23 AND B."Group_ID"='GRP5' AND
24 DATE_TIME BETWEEN TO_DATE(TO_CHAR(SYSDATE-1,'MM/DD/YYYY') || '08:00:00','MM/DD/YYYY HH24:MI:SS
25 TO_DATE(TO_CHAR(SYSDATE-1,'MM/DD/YYYY') || '09:00:00','MM/DD/YYYY HH24:MI:SS')
26 AND RECORD_TYPE='INTERVAL'
27 GROUP BY
28 B."Agent_ID",B."Agent_Name";
29 V_CTR:=V_CTR + 1;
30 EXIT WHEN V_CTR=20;
31 END LOOP;--when loop
32 COMMIT;
33 EXCEPTION
34 WHEN OTHERS THEN
35 DBMS_OUTPUT.PUT_LINE(' EXCEPTION ENCOUNTERED:');
36 DBMS_OUTPUT.PUT_LINE(' ' || SQLCODE || ': ' || SQLERRM );
37* END;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE SP_DAILY_REPORT_BY_COLLECTOR:
LINE/COL ERROR
-------- --------------------------------------------------------
0/0 ORA-04052: error occurred when looking up remote object
mel.mel.Agent_Profile@DW_MEL.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-28500: connection from ORACLE to a non-Oracle system returned
this message:
[Generic Connectivity Using ODBC
Can anyone help me resolve this issue. THis is urgent and any help will be greatly appreciated.
Thanks.
[Updated on: Fri, 29 September 2006 13:10] Report message to a moderator |
|
|
|
|
|
|
|
| Re: ORA-04052 [message #195723 is a reply to message #195607] |
Sun, 01 October 2006 19:39  |
bvenkatrajan
Messages: 4
Registered: September 2006
|
Junior Member |
|
|
|
FYI:The error for some reason was eliminated when the owner was removed from the table name reference. I am not sure why it requires the owner information when the SQL is run but when I put it into the procedure it does not recognize it. In both cases I am coinnected to using hte same db link and as the same user.... But anyways its working... Thanks anyways.
|
|
|
|
| Re: ORA-04052 [message #201875 is a reply to message #195723] |
Tue, 07 November 2006 02:58 |
marut
Messages: 2
Registered: September 2006
Location: Vadodara
|
Junior Member |
|
|
|
In your registry enter string LOCAL and in value enter the SID. you dont have to write the owner name after it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
