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

From: Brian Gastineau <bgastine_at_giveblood.org>
Date: 1997/01/23
Message-ID: <01bc094c$05f8fee0$b90110ac@gastineau1.giveblood.org>#1/1

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 Thu Jan 23 1997 - 00:00:00 CST