PARSE procedure - Parse an SQL statement
The PARSE procedure parses an SQL statement.
If the SQL command is a DDL command, it is immediately executed and does not require running the EXECUTE procedure.
Syntax
Parameters
- c
- An input argument of type INTEGER that specifies the cursor ID of an open cursor.
- statement
- The SQL statement to be parsed.
- language_flag
- This argument is provided for Oracle syntax compatibility. Use
a value of 1 or
DBMS_SQL.native
.
Authorization
EXECUTE privilege on the DBMS_SQL module.
Usage notes
This procedure can be invoked using function invocation syntax in a PL/SQL assignment statement.
Examples
Example 1: 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.SET SERVEROUTPUT ON@
BEGIN
DECLARE curid INTEGER;
CALL DBMS_SQL.OPEN_CURSOR(curid);
CALL DBMS_SQL.PARSE(curid, 'CREATE TABLE job (jobno DECIMAL(3),
' || 'jname VARCHAR(9))', DBMS_SQL.native);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END@
This example results in the following output:
SET SERVEROUTPUT ON
DB20000I The SET SERVEROUTPUT command completed successfully.
BEGIN
DECLARE curid INTEGER;
CALL DBMS_SQL.OPEN_CURSOR(curid);
CALL DBMS_SQL.PARSE(curid, 'CREATE TABLE job (jobno DECIMAL(3), ' ||
'jname VARCHAR(9))', DBMS_SQL.native);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END
DB20000I The SQL command completed successfully.
Example
2: The following inserts two rows into the
job
table. BEGIN
DECLARE curid INTEGER;
DECLARE v_sql VARCHAR(50);
DECLARE v_status INTEGER;
CALL DBMS_SQL.OPEN_CURSOR(curid);
SET v_sql = 'INSERT INTO job VALUES (100, ''ANALYST'')';
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.EXECUTE(curid, v_status);
CALL DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
SET v_sql = 'INSERT INTO job VALUES (200, ''CLERK'')';
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.EXECUTE(curid, v_status);
CALL DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END@
This example results in the following output:
BEGIN
DECLARE curid INTEGER;
DECLARE v_sql VARCHAR(50);
DECLARE v_status INTEGER;
CALL DBMS_SQL.OPEN_CURSOR(curid);
SET v_sql = 'INSERT INTO job VALUES (100, ''ANALYST'')';
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.EXECUTE(curid, v_status);
CALL DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
SET v_sql = 'INSERT INTO job VALUES (200, ''CLERK'')';
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.EXECUTE(curid, v_status);
CALL DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END
DB20000I The SQL command completed successfully.
Number of rows processed: 1
Number of rows processed: 1
Example 3: The
following anonymous block uses the DBMS_SQL module to execute a block
containing two INSERT statements. Note that the end of the block contains
a terminating semicolon, whereas in the prior examples, the individual
INSERT statements did not have a terminating semicolon.
BEGIN
DECLARE curid INTEGER;
DECLARE v_sql VARCHAR(100);
DECLARE v_status INTEGER;
CALL DBMS_SQL.OPEN_CURSOR(curid);
SET v_sql = 'BEGIN ' || 'INSERT INTO job VALUES (300, ''MANAGER''); '
|| 'INSERT INTO job VALUES (400, ''SALESMAN''); ' || 'END;';
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END@
This example results in the following output:
BEGIN
DECLARE curid INTEGER;
DECLARE v_sql VARCHAR(100);
DECLARE v_status INTEGER;
CALL DBMS_SQL.OPEN_CURSOR(curid);
SET v_sql = 'BEGIN ' || 'INSERT INTO job VALUES (300, ''MANAGER''); ' ||
'INSERT INTO job VALUES (400, ''SALESMAN''); ' || 'END;';
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END
DB20000I The SQL command completed successfully.