Re: Dynamic SQL problem in PL/SQL block run from SQL*Plus

From: Craig or Diana Duncan <art2mis_at_apk.net>
Date: 1997/01/26
Message-ID: <01bc0b2e$c4577b80$af08b7ce@zeus>#1/1

In order to use dbms_sql for a query, there are some additional steps. You need to define_column and column_value. I'm not sure how that will work for select *.

Also, in order to view anything out of PL/SQL, you must dbms_output the result. The following code will word for selecting one row with one varchar2 column.

By the way, select text from all_source where name = 'DBMS_SQL' for pretty good documentation.

    dbms_sql.parse(sql_cursor, sql_string, dbms_sql.v7);
    dbms_sql.define_column(sql_cursor, 1, outdescr, 2000);
    rows_processed := dbms_sql.execute_and_fetch(sql_cursor);
    dbms_sql.column_value(sql_cursor, 1, outdescr);
    dbms_sql.close_cursor(sql_cursor);
    dbms_output.put_line(outdescr);

Diana Duncan
art2mis_at_apk.net

Brian Gastineau <bgastine_at_giveblood.org> wrote in article <01bc094c$05f8fee0$b90110ac_at_gastineau1.giveblood.org>...
> Any suggestions for why the following PL/SQL block will output the
 "put_line" results to
> the screen but won't output the "parse" results would be appreciated.
>
> -------------------
> DECLARE
> CURSOR Table_List is SELECT Table_Name
> FROM All_Tables
> WHERE Owner = 'GASTINEAU';
> cid INTEGER;
> BEGIN
> cid := dbms_sql.open_cursor;
> FOR Current_Table IN Table_List LOOP
> dbms_output.put_line('SELECT * FROM GASTINEAU.' ||

 current_table.table_name);


>     dbms_sql.parse(cid, 'SELECT * FROM GASTINEAU.' ||

 current_table.table_name,

> dbms_sql.v7);
> END LOOP;
> dbms_sql.close_cursor(cid);
> END;
> -------------------------
>
> The screen output is a list of SQL commands of the form
>
> SQL> @mysearch
> 20> /
> SELECT * FROM GASTINEAU.MYTABLE1
> SELECT * FROM GASTINEAU.MYTABLE2
> SELECT * FROM GASTINEAU.MYTABLE3
> ...
> PL/SQL procedure successfully completed.
> SQL>
>
> The SERVEROUTPUT setting is ON in SQL*Plus and the block is being run
 from the GASTINEAU
> account.
>
> My goal is to view the data contained in all tables. If there is a more
 appropriate way
> to access this information, that process would also be helpful.
>
> Thanks
> Brian Gastineau
> bgastine_at_giveblood.org
>
Received on Sun Jan 26 1997 - 00:00:00 CST