Problem with db link usage
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Problem with db link usage

  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Problem with db link usage

    Hi,

    I have a PL/SQL code which will try to list a set of tables from the remote database & try displaying. When I ran the code, I am getting the error

    declare

    cursor table_list_c is
    (select table_owner||'.'||table_name tab_name from scr_process@scr_link
    MINUS
    select owner||'.'||table_name tab_name from dba_tables@scr_link
    );

    table_name_v scr_process.table_name@scr_link%type;

    begin
    for table_name_v in table_list_c
    loop
    dbms_output.put_line(table_name_v.tab_name);
    end loop;
    end;

    declare
    *
    ERROR at line 1:
    ORA-04052: error occurred when looking up remote object
    SYS.X$KSPPI@SCR_LINK.US.ORACLE.COM
    ORA-00604: error occurred at recursive SQL level 1
    ORA-02068: following severe error from SCR_LINK
    ORA-03113: end-of-file on communication channel

    DB link(scr_link) is working properly & even the inner sql query when I run separately, it is working fine.

    Please suggest what is going wrong here? What can I do to make it to work?

    Thanks in advance

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    sounds like you had an ora-600 or ora-7445 on the remote host.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    cursor table_list_c is
    (select table_owner||'.'||table_name tab_name from scr_process@scr_link
    where (owner||'.'||table_name ) not in(
    select owner||'.'||table_name tab_name from dba_tables@scr_link
    ));

    Try the above.

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    A work around is create a synonym for the object using db link and try if it works.

    regards
    anandkl
    anandkl

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