| "dynamic" cursor [message #189444] |
Thu, 24 August 2006 11:01  |
wiesmeier
Messages: 2
Registered: August 2006
|
Junior Member |
|
|
Hello
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?
thanx
|
|
|
|
| Re: "dynamic" cursor [message #189448 is a reply to message #189444] |
Thu, 24 August 2006 11:40  |
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 ...';
loop
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 |
wiesmeier
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||'''';
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
