Use native dynamic SQL to re-create MyTable.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE RecreateTempTable (
2 p_Description IN VARCHAR2) IS
3
4 v_CreateString VARCHAR2(100);
5 v_DropString VARCHAR2(100);
6 BEGIN
7 v_DropString := 'DROP TABLE MyTable';
8
9 BEGIN
10 EXECUTE IMMEDIATE v_DropString;
11 EXCEPTION
12 WHEN OTHERS THEN
13 IF SQLCODE != -942 THEN
14 RAISE;
15 END IF;
16 END;
17
18 v_CreateString := 'CREATE TABLE MyTable ' || p_Description;
19 EXECUTE IMMEDIATE v_CreateString;
20 END RecreateTempTable;
21 /
Procedure created.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
SQL>
Related examples in the same category