twisted_kiwi
asked on
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_adPerS yncLog
SELECT * FROM yyuniv.adPerSyncLog@UNIVER SE;
DELETE FROM yyuniv.adPerSyncLog@UNIVER SE;
END;
/
but I get lovely errors
PL/SQL: ORA-04052: error occurred when looking up remote object
YYUNIV.ADPERSYNCLOG@UNIVER SE
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
This is my procedure
CREATE OR REPLACE PROCEDURE sp_SyncStart
AS
BEGIN
INSERT INTO care_admin.coventry_adPerS
SELECT * FROM yyuniv.adPerSyncLog@UNIVER
DELETE FROM yyuniv.adPerSyncLog@UNIVER
END;
/
but I get lovely errors
PL/SQL: ORA-04052: error occurred when looking up remote object
YYUNIV.ADPERSYNCLOG@UNIVER
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
Check the NLS_LANG on both your databases.
Also what's the version of your remote db and the other DB?
Also what's the version of your remote db and the other DB?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
:)
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?
:)
>> 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.
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.
ASKER
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.
Thanks for the help.
I know this thread is super old, but wanted to share my resolve on this issue. Had public DB link from Oracle to SQL Server. Select query worked fine, but the same query embedded in stored procedure gave error about not being able to find the remote public object. Spent almost an entire day banging on this with no solution. Turns out the SQL Server view I was querying contained field names that exceeded 30 chars. Apparently Oracle doesn't appreciate that. Created new view with only the fields need and none that exceeded 30 chars. Procedure now compiles with no error and works as intended.
ASKER