DB LINK error when run PL/SQL

[ckwan]

Hi All,
I am facing error when execute the PL/SQL which I use the dblink to retrieve data from remote database.

The error as following :
ORA-04052 : error occurred when looking up remote object PUBLIC....
ORA-00604 : error occurred at recursive SQL Level 1
ORA-00372 : file 1 cannot be modified at this time
ORA-01110 : data file 1: ....
ORA-02062 : preceding .....

For ur information, my global_names is set to FALSE.
I am able use SQL statement

for example
SELECT ...
FROM tab@dblink;

This will retrieve the result I want.

But does not work in PL/SQL

Anyone has clue on this ? Thanks

[bensr]

I am not familiar with the problem, but I can imaging 2 things:
1 Are you the owner of the PL/SQL you tried to perform.
If not it may be a matter of grants.

2 If the PL/SQL is a procedure or package
, then it is started on the server.
In that case the TNSNAMES on the server must also contain
an entry of the linked-database.

[prakashs43]

u might be hitting one of these .

1) check password
2) select * from global_name;
check if there is any extension ??
3) Use database link name same as GLOBAL NAME

last option .

4) run catlog.sql & catproc.sql on both the servers ( if there is any data migration from different versions , above srcipts will call KGLR.SQL script ) .

[pnrdba]

Is your DB link public/private, B'coz in your post, you said some thing,
ORA-04052 : error occurred when looking up remote object PUBLIC....


Thanks

[alexherm]

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.

[MitchelRi]

I always count on fat burners to help me and 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.

Is there a workaround that would force it to accept field names that exceed 30 characters?

[alexherm]

I would use the same workaround as mentioned in previous post. Create a view on the SQL Server side and alias the field names so they are less than 30 chars.