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 |
|
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 |
|
Michel Cadot
Messages: 68650 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 #678956 is a reply to message #678955] |
Thu, 23 January 2020 13:48 |
|
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 #678960 is a reply to message #678958] |
Thu, 23 January 2020 13:59 |
|
Michel Cadot
Messages: 68650 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
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 |
|
Michel Cadot
Messages: 68650 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;
/
|
|
|
|
|
Goto Forum:
Current Time: Sat May 11 10:16:11 CDT 2024
|