Home » SQL & PL/SQL » SQL & PL/SQL » ORA-04052
ORA-04052 [message #195607] Fri, 29 September 2006 12:20 Go to next message
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 #195682 is a reply to message #195607] Sun, 01 October 2006 02:56 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Oracle cannot see the remote object. Try to enable ODBC tracing on the gateway machine to see what is happening here.
Re: ORA-04052 [message #195722 is a reply to message #195682] Sun, 01 October 2006 18:23 Go to previous messageGo to next message
bvenkatrajan
Messages: 4
Registered: September 2006
Junior Member
Thank you I will try that ...
Re: ORA-04052 [message #195723 is a reply to message #195607] Sun, 01 October 2006 19:39 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Output in Text File
Next Topic: row data in a column Format with Table_name and Column Name.
Goto Forum:
  


Current Time: Wed Dec 07 18:12:46 CST 2016

Total time taken to generate the page: 0.09253 seconds