PARSE v17
The PARSE
procedure parses a SQL command or SPL block. If the SQL command is a DDL command, it executes immediately and doesn't require that you run the EXECUTE
function.
PARSE(<c> NUMBER, <statement> VARCHAR2, <language_flag> NUMBER)
Parameters
c
Cursor ID of an open cursor.
statement
SQL command or SPL block to parse. 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 EDB EDB Postgres Advanced Server form.
Examples
This anonymous block creates a table named, job
. DDL statements are executed immediately by the PARSE
procedure and don't require a separate EXECUTE
step.
DECLARE curid NUMBER; 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 NUMBER; 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
This anonymous block uses the DBMS_SQL
package to execute a block containing two INSERT
statements. The end of the block contains a terminating semi-colon. In the prior example, each INSERT
statement doesn't have a terminating semi-colon.
DECLARE curid NUMBER; 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;
- On this page
- Parameters
- Examples