PARSE Procedure

This procedure parses the given statement in the given cursor. All statements are parsed immediately. In addition, DDL statements are run immediately when parsed.

There are two versions of the PARSE procedure: one uses a VARCHAR2 statement as an argument, and the other uses a VARCHAR2S (table of VARCHAR2) as an argument.

Caution: Using DBMS_SQL to dynamically run DDL statements can result in the program hanging. For example, a call to a procedure in a package results in the package being locked until the execution returns to the user side. Any operation that results in a conflicting lock, such as dynamically trying to drop the package before the first lock is released, results in a hang.

The size limit for parsing SQL statements with the syntax above is 32KB.

Syntax

DBMS_SQL.PARSE (
   c                  IN INTEGER,
   statement          IN VARCHAR2,
   language_flag      IN INTEGER);

The PARSE procedure also supports the following syntax for large SQL statements:

Note: The procedure concatenates elements of a PL/SQL table statement and parses the resulting string. You can use this procedure to parse a statement that is longer than the limit for a single VARCHAR2 variable by splitting up the statement.

DBMS_SQL.PARSE ( 
   c                  IN INTEGER, 
   statement          IN VARCHAR2S, 
   lb                 IN INTEGER, 
   ub                 IN INTEGER, 
   lfflg              IN BOOLEAN, 
   language_flag      IN INTEGER); 

Parameters

Table 62-2 PARSE Procedure Parameters

Parameter	Description
c	ID number of the cursor in which to parse the statement.
statement	SQL statement to be parsed. Unlike PL/SQL statements, your SQL statement should not include a final semicolon. For example: DBMS_SQL.PARSE(cursor1, 'BEGIN proc; END;', 2); DBMS_SQL.PARSE(cursor1, 'INSERT INTO tab values(1)', 2);
lb	Lower bound for elements in the statement.
ub	Upper bound for elements in the statement.
lfflg	If TRUE, then insert a linefeed after each element on concatenation.
language_flag	Determines how Oracle handles the SQL statement. The following options are recognized: V6 (or 0) specifies version 6 behavior. NATIVE (or 1) specifies normal behavior for the database to which the program is connected. V7 (or 2) specifies Oracle7 behavior.

Note: Because client-side code cannot reference remote package variables or constants, you must explicitly use the values of the constants. For example, the following code does not compile on the client: DBMS_SQL.PARSE(cur_hdl, stmt_str, dbms_sql.V7); -- uses constant dbms_sql.V7 The following code works on the client, because the argument is explicitly provided: DBMS_SQL.PARSE(cur_hdl, stmt_str, 2); -- compiles on the client

VARCHAR2S Datatype for Parsing Large SQL Strings

To parse SQL statements larger than 32 KB, DBMS_SQL makes use of PL/SQL tables to pass a table of strings to the PARSE procedure. These strings are concatenated and then passed on to the Oracle server.

You can declare a local variable as the VARCHAR2S table-item type, and then use the PARSE procedure to parse a large SQL statement as VARCHAR2S.

The definition of the VARCHAR2S datatype is:

TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;

Exceptions

If you create a type/procedure/function/package using DBMS_SQL that has 
compilation warnings, an ORA-24344 exception is raised, and the procedure is still 
created.