Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: PLSQL and variable Table Names

Re: PLSQL and variable Table Names

From: Bastiaan Schaap <fake_address_at_nomail.com>
Date: Wed, 20 Jun 2001 08:52:32 +0200
Message-ID: <RDXX6.7$k_3.371@psinet-eu-nl>

Hi Annett,

Yes, you can do this..... You can simply accomplish it by using a ref cursor. A small example:
first you declare your type and the cursor itself:

type c_type is ref cursor;
c_test c_type;

Then within your code it is very easy to use:

open c_test for 'select a,b,c,d from ' || v_table loop

   fetch c_test into v_a, v_b, v_c, v_d;    exit when c_test%notfound;

   do your magic here ;-))

end loop;

So for every table you would only have to change the veriable...... There's a 'but' offcourse ;-)) And in this case the limitation is, that you *have* to know what columns you get back from your query. Otherwise (you've already seen this offcourse), your fetch will 'break'. So if you simply would like to do a select(*) for a set of tables, doing this with a ref cursor is very doable... If the query returns a different number and type of columns each time, you should use dynamic sql just like Sybrand and Thomas said...

HTH,

--
Bastiaan Schaap
________________________________
Good generally conquers evil. Unless, of course, good is stupid.
Received on Wed Jun 20 2001 - 01:52:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US