Oracle: DBMS_SQL.PARSE

dbms_sql.parse(…) parses the text of an SQL statement and associates it with a cursor opened with dbms_sql.open_cursor).

A DDL statement is immediately executed.

declare cur number; begin cur := dbms_sql.open_cursor; dbms_sql.parse(cur, q'[ SQL STATEMENT GOES HERE ]', dbms_sql.native); -- Do something meaningful … dbms_sql.close_cursor(cur); end; /

Parse twice

The following example demonstrates that parse can be executed multiple time on the same cursor.

declare

   cur number;

   procedure print_column_names(c number) is
      cntCols    number;
      cols       dbms_sql.desc_tab;
   begin

      dbms_sql.describe_columns (c, cntCols, cols);

      dbms_output.new_line;
      for i in 1 .. cntCols loop
          dbms_output.put_line('  ' || cols(i).col_name);
      end loop;

   end print_column_names;

begin


   cur := dbms_sql.open_cursor;

   dbms_sql.parse(cur, 'select * from user_tables', dbms_sql.native);
   print_column_names(cur);

--
-- Use same cursor again:
--
   dbms_sql.parse(cur, 'select * from dual', dbms_sql.native);
   print_column_names(cur);

   dbms_sql.close_cursor(cur);

   dbms_output.new_line;

end;
/

Github repository Oracle-Patterns, path: /Installed/dbms/sql/parse/parse-twice.sql

Oracle: DBMS_SQL.PARSE

Parse twice

See also