Re: use of variables in pl/sql

From: Mark Styles <lambicm_at_yahoo.com>
Date: Tue, 03 Nov 1998 16:14:20 GMT
Message-ID: <363f29eb.24363813@news.intra.bt.com>

Tue, 03 Nov 1998 09:58:42 -0500, Peter Shankey <shankeyp_at_charlestoncounty.org> rambled:

>Given the block:
>
>declare
> cursor tab_cursor is select table_name from sys.dba_tables where
>owner = 'CHADBA';
> my_table_name varchar2(30);
>BEGIN
> open tab_cursor;
> loop
> fetch tab_cursor into my_table_name;
> exit when tab_cursor%notfound;
> dbms_output.put_line('table name is: ' || my_table_name);
> select * from my_table_name;
> end loop;
> close tab_cursor;
>END;
>/
>
>Why can not I substiutue the var my_table_name on the select statement
>and get a dump of the tables? Is there a way to do this?

Try something like:

declare

     cursor tab_cursor is 
         select table_name from sys.dba_tables where owner = 'CHADBA';
     c_source number;
     v_query_on_table varchar2(10000);

BEGIN
    for r_table in tab_cursor loop

        c_source := dbms_sql.open_cursor;
        -- build a query for the table here in v_query!
        dbms_sql.parse(c_source, 
           v_query_on_table, dbms_sql.v7);

        loop
            if dbms_sql.fetch_rows(c_source) <= 0 then
               -- no more rows
               exit;
            end if;
            -- call dbms_sql.column_value a bunch of times here
            -- e.g. dbms_sql.column_value(c_source,1,v_mycol);

            dbms_output.put_line(mycol);
        end loop;

end;

Mark Styles
Spam my account, lose your account. Clear enough? Religious tolerance is an oxymoron. Received on Tue Nov 03 1998 - 10:14:20 CST