• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9949
  • Last Modified:

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

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
twisted_kiwi
Asked:
twisted_kiwi
1 Solution
 
twisted_kiwiAuthor Commented:
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
 
sathyagiriCommented:
Check the NLS_LANG on both your databases.
Also what's the version of your remote db and the other DB?
0
 
sathyagiriCommented:
Also make sure the grants on the remote table are explicitly assigned to the user.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rbrookerCommented:
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
 
MohanKNairCommented:
>> 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
 
twisted_kiwiAuthor Commented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now