PARSE v12
The PARSE
procedure parses a SQL command or SPL block. If the SQL command is a DDL command, it is immediately executed and does not require running the EXECUTE
function.
PARSE(<c> INTEGER, <statement> VARCHAR2, <language_flag> INTEGER)
Parameters
c
Cursor ID of an open cursor.
statement
SQL command or SPL block to be parsed. A SQL command must not end with the semi-colon terminator, however an SPL block does require the semi-colon terminator.
language_flag
Language flag provided for compatibility with Oracle syntax. Use DBMS_SQL.V6, DBMS_SQL.V7
or DBMS_SQL.native
. This flag is ignored, and all syntax is assumed to be in EnterpriseDB Advanced Server form.
Examples
The following anonymous block creates a table named, job
. Note that DDL statements are executed immediately by the PARSE
procedure and do not require a separate EXECUTE
step.
DECLARE curid INTEGER; BEGIN curid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(curid, 'CREATE TABLE job (jobno NUMBER(3), ' || 'jname VARCHAR2(9))',DBMS_SQL.native); DBMS_SQL.CLOSE_CURSOR(curid); END;
The following inserts two rows into the job
table.
DECLARE curid INTEGER; v_sql VARCHAR2(50); v_status INTEGER; BEGIN curid := DBMS_SQL.OPEN_CURSOR; v_sql := 'INSERT INTO job VALUES (100, ''ANALYST'')'; DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native); v_status := DBMS_SQL.EXECUTE(curid); DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status); v_sql := 'INSERT INTO job VALUES (200, ''CLERK'')'; DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native); v_status := DBMS_SQL.EXECUTE(curid); DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status); DBMS_SQL.CLOSE_CURSOR(curid); END; Number of rows processed: 1 Number of rows processed: 1
The following anonymous block uses the DBMS_SQL
package to execute a block containing two INSERT
statements. Note that the end of the block contains a terminating semi-colon, while in the prior example, each individual INSERT
statement does not have a terminating semi-colon.
DECLARE curid INTEGER; v_sql VARCHAR2(100); v_status INTEGER; BEGIN curid := DBMS_SQL.OPEN_CURSOR; v_sql := 'BEGIN ' || 'INSERT INTO job VALUES (300, ''MANAGER''); ' || 'INSERT INTO job VALUES (400, ''SALESMAN''); ' || 'END;'; DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native); v_status := DBMS_SQL.EXECUTE(curid); DBMS_SQL.CLOSE_CURSOR(curid); END;