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

Posted on 2006-07-20
Medium Priority
Last Modified: 2011-08-18

This is my procedure



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


but I get lovely errors

PL/SQL: ORA-04052: error occurred when looking up remote object
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.

Question by:twisted_kiwi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Author Comment

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.
LVL 14

Expert Comment

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

Accepted Solution

sathyagiri earned 225 total points
ID: 17147980
Also make sure the grants on the remote table are explicitly assigned to the user.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 18

Expert Comment

ID: 17149306

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.

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?

LVL 16

Expert Comment

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.

Author Comment

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.

Featured Post

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!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

777 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