Re: Dynamic cursor ?

From: Maija-Leena <kangasmaki_at_jippii.fi>
Date: Thu, 05 Jul 2007 09:52:40 GMT
Message-ID: <Ib3ji.78$dJ2.4@read3.inet.fi>

Thanks,

this was exactly what I was looking for !!

Maija-Leena

"metzguar" <urs_at_ursmetzger.de> wrote in message news:1183466434.641649.214270_at_g4g2000hsf.googlegroups.com...
> On 3 Jul., 09:01, "Maija-Leena" <kangasm..._at_jippii.fi> wrote:
> > forgot the select database link name befor for loop:
> >
> > "Maija-Leena" <kangasm..._at_jippii.fi> wrote in message
> >
> > news:lrmii.34$t62.12_at_read3.inet.fi...
> >
> > > Hi,
> >
> > > I need to declare a cursor that selects from a table via database link
but
> > I
> > > don't know the name of the link before I select it from another table.
How
> > > do I declare the cursor ? Can I use dynamic sql ?
> >
> > > set serveroutput on
> >
> > > DECLARE
> > > db_link_name VARCHAR2(50);
> > > CURSOR cur1 IS SELECT col1 FROM table1_at_db_link_name;
> >
> > > BEGIN
> > > SELECT link_name INTO db_link_name FROM link_table;
> > > FOR cur IN cur1 LOOP
> > > ...
> > > END LOOP;
> > > END;
> > > /
> >
> > > Regards,
> >
> > > Maija-Leena

>

> DECLARE
> db_link_name VARCHAR2(50) := 'some_database';

>

> TYPE REF_CURSOR IS REF CURSOR;

>


>    cur1           REF_CURSOR;

>    tcol           VARCHAR2(100);

> BEGIN
> OPEN cur1 FOR 'SELECT col1 FROM table1@' || db_link_name;

>


>    LOOP

>       FETCH cur1

>        INTO tcol;

>


>       EXIT WHEN cur1%NOTFOUND;

>       -- Do something with tcol ...

>       DBMS_OUTPUT.put_line(tcol);

> END LOOP;
> END;
> /

>

> hth, Urs Metzger

> Received on Thu Jul 05 2007 - 04:52:40 CDT