DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: DB LINK error when run PL/SQL

  1. #1
    Join Date
    Oct 2000
    Posts
    250

    DB LINK error when run PL/SQL

    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

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    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.
    Regards
    Ben de Boer

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 ) .
    Last edited by prakashs43; 11-13-2002 at 06:50 AM.
    siva prakash
    DBA

  4. #4
    Join Date
    Apr 2002
    Posts
    291
    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
    PNRDBA

  5. #5
    Join Date
    Apr 2018
    Posts
    2
    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.

  6. #6
    Join Date
    Mar 2019
    Posts
    1
    Quote Originally Posted by alexherm View Post
    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?
    Last edited by MitchelRi; 09-15-2023 at 09:16 AM.

  7. #7
    Join Date
    Apr 2018
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width