Re: Help! PL/SQL Question

Hi

If you want make the table name in the from clause of a sql statement dynamic, try to use the DBMS_SQL package.

Cheers

Juerg Fuchs

sjoshi_at_ingr.com wrote:

> Hello All
> I have a cursor which returns tableNos from another table, like this
> cursor getNos is
> select partition_no from pd_pframe.pdtable_113
> where discipline_indx_no=1;
>
> Now for each of these nos, I need to query another table whose name is
> like dd_<the no returned from the above cursor>. For eg: if the above
> cursor returned 12, 20, then I need to query a field called line_id in
> tables dd_12 and dd_20 and then update the line_id with another value.
> Now how do I do that? I tried to concat the no to the table_name but it
> doesn't work.
> Any help is appreciated. Here is what I tried
>
> DECLARE
>
> varTableName VARCHAR2(32) :='dd_';
> varTotalName VARCHAR2(32);
> varLineID VARCHAR(24);
> varTableNo NUMBER;
>
> cursor getNos is
> select partition_no from pd_pframe.pdtable_113
> where discipline_indx_no=1;
>
> BEGIN
> OPEN getNos;
> LOOP
> FETCH getNos INTO varTableNo;
> EXIT WHEN getNos%notfound;
> varTotalName := varTableName || LTRIM(TO_CHAR(varTableNo,'9999'));
> --dbms_output.put_line(varTotalName);
>
> FOR LINEID IN (Select line_id from varTotalName) LOOP
> dbms_output.put_line(LINEID); --does not work
> END LOOP;
>
> varTotalName := '';
>
> END LOOP;
> CLOSE getNos;
> END;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Feb 17 2000 - 09:39:02 CST