DB2 Version 9.7 for Linux, UNIX, and Windows

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


>>-DBMS_SQL.PARSE--(--c--,--statement--,--language_flag--)-----><

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.