Solved

PL/SQL: ORA-04052: error occurred when looking up remote object

Posted on 2006-07-20
6
9,318 Views
Last Modified: 2011-08-18
Hi,

This is my procedure

CREATE OR REPLACE PROCEDURE sp_SyncStart
AS

BEGIN

      INSERT INTO care_admin.coventry_adPerSyncLog
      SELECT * FROM yyuniv.adPerSyncLog@UNIVERSE;
            
      DELETE FROM yyuniv.adPerSyncLog@UNIVERSE;

END;
/

but I get lovely errors

PL/SQL: ORA-04052: error occurred when looking up remote object
YYUNIV.ADPERSYNCLOG@UNIVERSE
ORA-00604: error occurred at recursive SQL level 1
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this
expression is incomplete or malformed
ORA-02063: preceding 2 lines from UNIVERSE

I tried running the insert statement directly via SQL Plus and it worked correctly.  Not sure why it works interactively but not contained in a procedure.

Any ideas anyone?  Thanks in advance for you help.

Cheers,
Chris
0
Comment
Question by:twisted_kiwi
6 Comments
 

Author Comment

by:twisted_kiwi
ID: 17147779
I have now found out about the catrpc.sql script (formally KGLR.SQL) and have run that on the remote universe database but I still get the same error.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17147970
Check the NLS_LANG on both your databases.
Also what's the version of your remote db and the other DB?
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 75 total points
ID: 17147980
Also make sure the grants on the remote table are explicitly assigned to the user.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:rbrooker
ID: 17149306
Hi,

I assume that the structure of the two tables is the same?
Looking up ORA-06552, the only odd thing that i can find is that one of the column names is a datatype.

eg,
create table coventry_adPerSyncLog (
  "DATE" date,
  text1 varchar2(20),
  text2 carchar2(30)
)

having column1 called DATE ( which is a datatype ) can result in this error.
can this be the case?

:)
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17151585
>> I tried running the insert statement directly via SQL Plus and it worked correctly.
There must be some permission issues with tables. As sathyagiri  suggested assign the grants on the remote table explicitly instead of using roles.

If the procedure is executed by a different user then specify "AUTHID CURRENT_USER"  to run the procedure with the rights of the owner executing the code.
0
 

Author Comment

by:twisted_kiwi
ID: 17286443
It turns out that a stored procedure does not take into account and access granted via roles.  So even though the user had the correct rights via their roles, these were not used when the stored proc was run.  I granted the rights explicitly to the user and it works fine.  

Thanks for the help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 79
Oracle database T-1 Setup 7 28
oracle query 3 26
MS SQL Server Management Studio R2 4 32
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question