Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Francisco.

Asked: February 15, 2012 - 11:19 am UTC

Last updated: July 11, 2017 - 3:03 pm UTC

Version: 11.2.0.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Hope everything is O.K. for you ......

You know, I am extracting segment_name information for several databases and I am inserting information in a repository table.

I am using next cursor to look for every database:

FOR x in (SELECT apl.id_aplicacion, app.nombre_aplicacion, app.db_link, ser.id_servidor, db.usuario, db.password, apl.tipo_database, apl.id_localidad
FROM aplicaciones apl, productos prod, database_links db, servidores ser, app_db_links app
WHERE apl.id_producto = prod.id_producto
AND apl.id_aplicacion = db.id_aplicacion
AND apl.id_aplicacion = app.id_aplicacion
AND apl.nombre_aplicacion = app.nombre_aplicacion
AND apl.id_servidor = ser.id_servidor
--AND apl.nombre_aplicacion = db.nombre_aplicacion
AND (apl.descripcion like 'Database%'
OR apl.descripcion like 'Base%')
AND (prod.nombre_producto like 'Database%'
OR prod.nombre_producto like 'Ebusiness%')
AND apl.tipo_database = 'PRODUCCION'
AND app.db_link = 'PRODMET')
LOOP

....

FOR j in (SELECT owner, segment_name, segment_type, tablespace_name, nvl(partition_name, 'NA') partition
FROM dba_segments@'||x.db_link||'
WHERE segment_type in ('TABLE','TABLE PARTITION')
AND owner not in ('SYS','SYSTEM'))

I want to use dba_segments@x.db_link in the second cursor dinamically (which is coming from the first one).

Do you know how can I use table_name (db_link) dinamically?

I really appreciate your help Sir.

Thank you very much.

Kind regards,

Francisco

and Tom said...

You will have to use dynamic SQL for this. I recommend this approach:

ops$tkyte%ORA11GR2> declare
  2      cursor template_cursor is SELECT owner, segment_name, segment_type, tablespace_name, nvl(partition_name, 'NA') partition FROM dba_segments;
  3  
  4      l_rec    template_cursor%rowtype;
  5      l_cursor sys_refcursor;
  6      l_dblink varchar2(30) := 'loopback@ora11gr2';
  7  begin
  8  
  9      open l_cursor for q'|
 10      SELECT owner, segment_name, segment_type, tablespace_name, nvl(partition_name, 'NA') partition
 11        FROM dba_segments@|' || l_dblink || q'|
 12       WHERE segment_type in ('TABLE','TABLE PARTITION')
 13         AND owner not in ('SYS','SYSTEM')|';
 14      loop
 15          fetch l_cursor into l_rec;
 16          exit when l_cursor%notfound;
 17          -- process l_rec --
 18      end loop;
 19      close l_cursor;
 20  end;
 21  /

PL/SQL procedure successfully completed.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I really appreciate it.

Francisco Martínez, February 15, 2012 - 12:08 pm UTC

Thank you very much Tom.

I really appreciate your help.

Kind regards,

Francisco

or use views

Laurent Schneider, February 16, 2012 - 2:40 am UTC

dynamic sql is pretty error prone and is a great source of sql injection

you have a limited number of dblinks anyway, personnaly I prefer a "view" approach

SQL> create database link DB01 using 'DB01';

Database link created.

SQL> create database link DB02 using 'DB02';

Database link created.

SQL> create view v as select 'DB01' name, s.segment_name, s.owner from dba_segments@DB01 s union all select 'DB02' name, s.segment_name, s.owner from dba_segments@DB02 s;

View created.

SQL> var b1 varchar2(4000)
SQL> exec :b1 := 'DB01'

PL/SQL procedure successfully completed.

SQL> select count(*) from v where name=:b1;
  COUNT(*)
----------
      5632

SQL> exec :b1 := 'DB02'

PL/SQL procedure successfully completed.

SQL> select count(*) from v where name=:b1;
  COUNT(*)
----------
      7108



Tom Kyte
February 16, 2012 - 7:20 am UTC

then you are assuming that each and every node is always up.


while in general, dynamic sql not using binds (this cannot use a bind) is sql injectable - the database link name is coming from the data dictionary, not from the outside world.

always up

A reader, February 16, 2012 - 7:57 am UTC 

indeed db link is often (mis-)used to compile packages and views when the remote objects are not available. 

about sql injection, the link name come from the app_db_links user table, which well contains code. It does not come from the dictionary.

<code>
SQL> set serverout on size unlimited;
SQL>
SQL> var dblink varchar2(4000)
SQL>
SQL> exec :dblink := 'DB01'

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2    cursor template_cursor is SELECT owner, segment_name, segment_type,
  3  tablespace_name, nvl(partition_name, 'NA') partition FROM dba_segments;
  4    l_rec    template_cursor%rowtype;
  5    l_cursor sys_refcursor;
  6  begin
  7
  8    open l_cursor for q'|
  9      SELECT owner, segment_name, segment_type, tablespace_name,
 10      nvl(partition_name, 'NA') partition
 11      FROM dba_segments@|' || :dblink || q'|
 12      WHERE segment_type in ('TABLE','TABLE PARTITION')
 13      AND owner not in ('SYS','SYSTEM')|';
 14    loop
 15      fetch l_cursor into l_rec;
 16      dbms_output.put_line(l_rec.owner||'.'||l_rec.segment_name);
 17      exit when l_cursor%notfound or 1=1;
 18    end loop;
 19    close l_cursor;
 20  end;
 21  /
OUTLN.OL$

PL/SQL procedure successfully completed.

SQL>
SQL> exec :dblink := '&_CONNECT_IDENTIFIER where 1=0 union all select user,user,user,user,user from dba_segments'

PL/SQL procedure successfully completed.

SQL>
SQL> run
  1  declare
  2    cursor template_cursor is SELECT owner, segment_name, segment_type,
  3  tablespace_name, nvl(partition_name, 'NA') partition FROM dba_segments;
  4    l_rec    template_cursor%rowtype;
  5    l_cursor sys_refcursor;
  6  begin
  7
  8    open l_cursor for q'|
  9      SELECT owner, segment_name, segment_type, tablespace_name,
 10      nvl(partition_name, 'NA') partition
 11      FROM dba_segments@|' || :dblink || q'|
 12      WHERE segment_type in ('TABLE','TABLE PARTITION')
 13      AND owner not in ('SYS','SYSTEM')|';
 14    loop
 15      fetch l_cursor into l_rec;
 16      dbms_output.put_line(l_rec.owner||'.'||l_rec.segment_name);
 17      exit when l_cursor%notfound or 1=1;
 18    end loop;
 19    close l_cursor;
 20* end;
DBAUSER.DBAUSER

PL/SQL procedure successfully completed.
</code>
Tom Kyte
February 16, 2012 - 10:24 am UTC

you are correct, the string would have to be validated.

Laurent Schneider, February 16, 2012 - 8:29 am UTC

indeed dynamic sql is often (mis-)used to compile packages and views when the remote objects are not
available.


How about dynamic dblink without cursor

Cherry, July 10, 2017 - 4:00 am UTC

Hi Tom,
I would like to know how can I implement dynamic dblink without using the cursor? For example below:

If <condition A is true> then
 select column A, B, C from tableA@dblink123;
else
 select column A, B, C from tableA@dblink456;
end if;

Chris Saxon
July 11, 2017 - 3:03 pm UTC

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9535689800346111194
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library