DBMS_SQL.PARSE and DBMS_SQL.EXECUTE : dbms_sql « System Packages « Oracle PL / SQL






DBMS_SQL.PARSE and DBMS_SQL.EXECUTE

  
SQL>
SQL> SET ECHO  ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2       v_CursorID  NUMBER;
  3       v_CreateTableString  VARCHAR2(500);
  4       v_NUMRows  INTEGER;
  5
  6  BEGIN
  7       v_CursorID := DBMS_SQL.OPEN_CURSOR;
  8       v_CreateTableString := 'CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50))';
  9
 10       DBMS_SQL.PARSE(v_CursorID,v_CreateTableString,DBMS_SQL.V7);
 11       v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
 12
 13  EXCEPTION
 14       WHEN OTHERS THEN
 15            IF SQLCODE != -955 THEN -- 955 is error that table exists
 16                 RAISE;
 17            ELSE
 18                 DBMS_OUTPUT.PUT_LINE('Table Already Exists!');
 19            END IF;
 20       DBMS_SQL.CLOSE_CURSOR(v_CursorID);
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table mytable;

Table dropped.

SQL>
SQL> --

   
  








Related examples in the same category

1.First DBMS_SQL Example
2.Pass a query statement to a stored procedure
3.Use dbms_sql.open_cursor create a cursor
4.Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.Use dbms_sql.bind_array to bind array to a cursor
6.Use dbms_sql.parse to bind a select statement to a cursor
7.Call dbms_sql.describe_columns2 to get info for a column
8.Non-Query DML and DDL Statements
9.Use DBMS_SQL with the RETURNING clause.
10.DBMS_SQL.NATIVE;
11.Dynamic sql statement with variable binding
12.Dynamic select statement and row fetch
13.Execute PL/SQL block
14.DBMS_SQL package: dynamic SQL
15.Pass where clause to a stored procedure
16.Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
17.Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
18.drop the supplied table using dynamic SQL.
19.DBMS_SQL.BIND_VARIABLE_CHAR
20.illustrate the importance of setting out_value_size.
21.Calling a function which uses dynamic SQL from within an SQL statement.
22.Illustrates the interaction of roles and dynamic SQL.
23.illustrates a DDL statement which is built dynamically from the procedure parameters.
24.Use dbms_sql to process query, cursor and value
25.Use dbms_sql.bind_variable to bind variable
26.dbms_sql.varchar2_table type variable