Re: Dynamic cursor ?

From: metzguar <urs_at_ursmetzger.de>
Date: Tue, 03 Jul 2007 05:40:34 -0700
Message-ID: <1183466434.641649.214270@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 Tue Jul 03 2007 - 07:40:34 CDT