SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
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 BEGIN
8 v_Cursor := DBMS_SQL.OPEN_CURSOR;
9
10 v_DropString := 'DROP TABLE MyTable';
11
12 BEGIN
13 DBMS_SQL.PARSE(v_Cursor, v_DropString, DBMS_SQL.NATIVE);
14 EXCEPTION
15 WHEN OTHERS THEN
16 IF SQLCODE != -942 THEN
17 RAISE;
18 END IF;
19 END;
20
21 v_CreateString := 'CREATE TABLE MyTable ' || p_Description;
22 DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.NATIVE);
23
24 DBMS_SQL.CLOSE_CURSOR(v_Cursor);
25 EXCEPTION
26 WHEN OTHERS THEN
27 DBMS_SQL.CLOSE_CURSOR(v_Cursor);
28 RAISE;
29 END RecreateTempTable;
30 /
Procedure created.
SQL> drop table MyTable;
Table dropped.
SQL>