First DBMS_SQL Example : dbms_sql « System Packages « Oracle PL / SQL

First DBMS_SQL Example

  
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE RecreateTempTable (
  2    p_Description IN VARCHAR2) IS
  3
  4    v_Cursor        NUMBER;
  5    v_CreateString  VARCHAR2(100);
  6    v_DropString    VARCHAR2(100);
  7    v_NumRows       INTEGER;
  8  BEGIN
  9    v_Cursor := DBMS_SQL.OPEN_CURSOR;
 10
 11    v_DropString := 'DROP TABLE MyTable';
 12
 13    BEGIN
 14      DBMS_SQL.PARSE(v_Cursor, v_DropString, DBMS_SQL.V7);
 15      v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
 16    EXCEPTION
 17      WHEN OTHERS THEN
 18        IF SQLCODE != -942 THEN
 19          RAISE;
 20        END IF;
 21    END;
 22    v_CreateString := 'CREATE TABLE MyTable ' || p_Description;
 23    DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.V7);
 24    v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
 25
 26    DBMS_SQL.CLOSE_CURSOR(v_Cursor);
 27  EXCEPTION
 28    WHEN OTHERS THEN
 29      DBMS_SQL.CLOSE_CURSOR(v_Cursor);
 30      RAISE;
 31  END RecreateTempTable;
 32  /

Procedure created.

SQL>

Related examples in the same category

1.	Pass a query statement to a stored procedure
2.	Use dbms_sql.open_cursor create a cursor
3.	Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
4.	Use dbms_sql.bind_array to bind array to a cursor
5.	Use dbms_sql.parse to bind a select statement to a cursor
6.	Call dbms_sql.describe_columns2 to get info for a column
7.	Non-Query DML and DDL Statements
8.	Use DBMS_SQL with the RETURNING clause.
9.	DBMS_SQL.NATIVE;
10.	DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
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