Home » SQL & PL/SQL » SQL & PL/SQL » "dynamic" cursor
"dynamic" cursor [message #189444] Thu, 24 August 2006 11:01 Go to next message
Messages: 2
Registered: August 2006
Junior Member

I want to send identical statements with a cursor on different identical servers.
For the connection I use database links.

A Statement to the server N1 would be like:
select <column> from <table>@dbl_N1 where ...
The Statement to the server S2 would therefore be
select <column> from <table>@dbl_S2 where ...
Since the Statements are always identical and I have a lot of servers I would like to declare a "dynamic" cursor where I can change the database link within procedure runtime.
I tried something like this:
cursor ABFRAGE (in_Link varchar2) is
select <column> from <table>@dbl_in_Link where ...

but this didn´t work....

has anybody some ideas?


Re: "dynamic" cursor [message #189448 is a reply to message #189444] Thu, 24 August 2006 11:40 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You are on the right track with "dynamic". You need to use dynamic SQL.

You won't be able to use a static cursor. Instead:

v_rc sys_refcursor
open v_rc for 'select ... from t@' || in_link || ' .. where ...';
  fetch v_rc into <some variable(s)>;
  exit when v_rc%notfound;
  -- do something
end loop;
close v_rc;
Re: "dynamic" cursor [message #189619 is a reply to message #189444] Fri, 25 August 2006 04:54 Go to previous message
Messages: 2
Registered: August 2006
Junior Member
muchas gracias!!!!

the resulting code is:

procedure ss_AktProgrammLZA_erstellen is

type sys_refcursor is REF CURSOR;
crs_VORHANDENE_TABELLEN sys_refcursor;

open crs_VORHANDENE_TABELLEN for 'select TNAME, TABTYPE from tab@dbl_'||Zentrale||' where TABTYPE=''TABLE'' and substr(TNAME,1,17)= ''AKT_PROGRAMM_LZA_'' and TNAME > ''AKT_PROGRAMM_LZA_'||Tagesstempel||'''';
Previous Topic: Regarding Rownum
Next Topic: Date Format
Goto Forum:

Current Time: Thu Mar 30 09:41:43 CDT 2017

Total time taken to generate the page: 1.16808 seconds