Re: PLSQL and variable Table Names

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.