Home » SQL & PL/SQL » SQL & PL/SQL » Looping through dblinks with a cursor (Oracle 12.2.0.1, RHEL 6.10)
Looping through dblinks with a cursor [message #678953] Thu, 23 January 2020 13:08 Go to next message
scoogie1973
Messages: 5
Registered: January 2020
Junior Member
Hello all,

I have searched Google, but cannot find a direct hit on what I am trying to do. I'm hoping someone can help.

I have 20 database links in my "source" system. I am trying to populate a local table with user information from all the "target" databases over dblinks. I am able to output the names of all the dblinks in my "source" system with dbms_output.put_line, but when I reference the cursor in my INSERT/SELECT statement, I receive the following ::

ORA-04052: error occurred when looking up remote object SYS.DBA_USERS@DBLINK_REC.DBLINK
ORA-00604: error occurred at recursive SQL level 1
ORA-02019: connection description for remote database not found

Below is the code

BEGIN
for dblink_rec in (
select db_link from sys.dba_db_links where db_link !='SYS_HUB.CDS.MRO.COM')
OPEN link_cursor;
LOOP
--dbms_output.put_line (dblink_rec.db_link);
--end loop;
--end;
insert into master_users2 (username, account_status, profile, host_name)
select u.username, u.account_status, u.profile, i.host_name
from sys.v$instance@dblink_rec.dblink i
,sys.dba_users@dblink_rec.dblink u
where i.con_id=d.con_id
order by 1;
commit;
end loop;
end;

How can I reference the dblink appropriately instead of having Oracle view "dblink_rec.dblink" as the name of the dblink?

Thanks in advance...
Re: Looping through dblinks with a cursor [message #678954 is a reply to message #678953] Thu, 23 January 2020 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 80 characters.

1/ The fact that a dblink is in dba_db_links does not mean that the db link is valid
2/ ORA-02019 means that the db link connect string is not in the database server tnsnames.ora
3/ You need to use dynamic query (execute immediate instruction) to do what you want

That is something like:
BEGIN
  for dblink_rec in (
    select db_link from sys.dba_db_links where db_link !='SYS_HUB.CDS.MRO.COM')
--  OPEN link_cursor;  <-- THIS IS INVALID
  LOOP
    execute immediate   -- NOTE: THIS IS db_link NOT dblink IN THE STATEMENT
    'insert into master_users2 (username, account_status, profile, host_name)
        select u.username, u.account_status, u.profile, i.host_name
        from sys.v$instance@'||dblink_rec.db_link||' i 
            ,sys.dba_users@'||dblink_rec.db_link||' u
        where i.con_id=d.con_id' ;
--        order by 1'; <-- order by IS POINTLESS IN AN insert STATEMENT
--    commit;  <-- DO NOT COMMIT INSIDE A CURSOR LOOP
  end loop;
end; 

[Updated on: Thu, 23 January 2020 13:25]

Report message to a moderator

Re: Looping through dblinks with a cursor [message #678955 is a reply to message #678954] Thu, 23 January 2020 13:38 Go to previous messageGo to next message
scoogie1973
Messages: 5
Registered: January 2020
Junior Member
Thank you, Michel. Coding is not my strong suit :/

I rewrote as per your suggestions, and am now getting "PLS-00306: wrong number or types of arguments in call to '||'"
Re: Looping through dblinks with a cursor [message #678956 is a reply to message #678955] Thu, 23 January 2020 13:48 Go to previous messageGo to next message
scoogie1973
Messages: 5
Registered: January 2020
Junior Member
Michel,

Here is the entire code. I didn't share all of it earlier as I did not think it was pertinent; however, now that the "execute immediate" syntax in included, which uses single-quotes, this may have introduced another issue since I am using a single-quote with my instr function.

Thanks again for your help!

BEGIN
for dblink_rec in (
select db_link from sys.dba_db_links)
LOOP
execute immediate
'insert into master_users2 (username, account_status, profile, host_name, custname, environment)
select u.username, account_status, profile, i.host_name,
inv.custname
substr(inv.custname,1, instr(inv.custname,'-')-1) as custname
, inv.environment
from sys.v$instance@'||dblink_rec.db_link||' i
,sys.v$database@'||dblink_rec.db_link||' d
,sys.dba_users@'||dblink_rec.db_link||' u
,dba_chklist.tb_orainvent inv
where i.con_id=d.con_id
and i.host_name=inv.primary_hostname
order by 1';
--commit;
end loop;
commit;
end;


Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 5
ORA-06512: at line 5
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
Re: Looping through dblinks with a cursor [message #678957 is a reply to message #678955] Thu, 23 January 2020 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You surely missed something, copy and paste your SQL*Plus session; don't forget to before read the link I gave you above.

Re: Looping through dblinks with a cursor [message #678958 is a reply to message #678956] Thu, 23 January 2020 13:49 Go to previous messageGo to next message
scoogie1973
Messages: 5
Registered: January 2020
Junior Member
...I don't know why my formatting doesn't post appropriately. Sorry for the eye chart Smile
Re: Looping through dblinks with a cursor [message #678960 is a reply to message #678958] Thu, 23 January 2020 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
scoogie1973 wrote on Thu, 23 January 2020 20:49
...I don't know why my formatting doesn't post appropriately. Sorry for the eye chart Smile
Because you didn't read How to use [code] tags and make your code easier to read.

There:
SQL> BEGIN
  2    for dblink_rec in (
  3      select db_link from sys.dba_db_links)
  4    LOOP
  5      execute immediate
  6          'insert into master_users2 (username, account_status, profile, host_name, custname, environment)
  7              select u.username, account_status, profile, i.host_name,
  8              inv.custname
  9              substr(inv.custname,1, instr(inv.custname,'-')-1) as custname
 10              , inv.environment
 11                  from sys.v$instance@'||dblink_rec.db_link||' i
 12                  ,sys.v$database@'||dblink_rec.db_link||' d
 13                  ,sys.dba_users@'||dblink_rec.db_link||' u
 14                  ,dba_chklist.tb_orainvent inv
 15              where i.con_id=d.con_id
 16              and i.host_name=inv.primary_hostname
 17              order by 1';
 18      --commit;
 19    end loop;
 20    commit;
 21  end;
 22  /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 5

[Updated on: Thu, 23 January 2020 14:00]

Report message to a moderator

Re: Looping through dblinks with a cursor [message #678961 is a reply to message #678960] Thu, 23 January 2020 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The errors comes from the quote you have in your statement and a missing comma.
You must either double these quotes or use the quoted string syntax in the first part of the statement:
BEGIN
  for dblink_rec in (
    select db_link from sys.dba_db_links)
  LOOP
    execute immediate
        'insert into master_users2 (username, account_status, profile, host_name, custname, environment)
            select u.username, account_status, profile, i.host_name,
            inv.custname,
            substr(inv.custname,1, instr(inv.custname,''-'')-1) as custname
            , inv.environment
                from sys.v$instance@'||dblink_rec.db_link||' i
                ,sys.v$database@'||dblink_rec.db_link||' d
                ,sys.dba_users@'||dblink_rec.db_link||' u
                ,dba_chklist.tb_orainvent inv
            where i.con_id=d.con_id
            and i.host_name=inv.primary_hostname';
--            order by 1'; -- DO NOT DO THAT
    --commit;
  end loop;
  commit;
end;
/
BEGIN
  for dblink_rec in (
    select db_link from sys.dba_db_links)
  LOOP
    execute immediate
        q'[insert into master_users2 (username, account_status, profile, host_name, custname, environment)
            select u.username, account_status, profile, i.host_name,
            inv.custname,
            substr(inv.custname,1, instr(inv.custname,'-')-1) as custname
            , inv.environment
                from sys.v$instance@]'||dblink_rec.db_link||' i
                ,sys.v$database@'||dblink_rec.db_link||' d
                ,sys.dba_users@'||dblink_rec.db_link||' u
                ,dba_chklist.tb_orainvent inv
            where i.con_id=d.con_id
            and i.host_name=inv.primary_hostname'; 
--            order by 1'; -- DO NOT DO THAT
    --commit;
  end loop;
  commit;
end;
/
Re: Looping through dblinks with a cursor [message #678962 is a reply to message #678961] Thu, 23 January 2020 14:09 Go to previous messageGo to next message
scoogie1973
Messages: 5
Registered: January 2020
Junior Member
Thanks for your input, Michel. I will keep working with it.

And just FYI, I also couldn't follow how to post a formatted version of my code; I tried following the instructions you sent.

Thanks again!
Re: Looping through dblinks with a cursor [message #678966 is a reply to message #678962] Fri, 24 January 2020 01:45 Go to previous message
John Watson
Messages: 8172
Registered: January 2010
Location: Global Village
Senior Member
with regard to the formatting, you need to use [code] tags. As described in the instructions you have been asked to read.
Previous Topic: XMLQUERY: ORA-01427: single-row subquery returns more than one row
Next Topic: Design Question
Goto Forum:
  


Current Time: Sat Feb 29 05:20:58 CST 2020