How can I create/drop/truncate a table inside a PL/SQL block ?

The Oracle (tm) Users' Co-Operative FAQ

How can I create/drop/truncate a table inside a PL/SQL block ?

PL/SQL does not allow you to execute any DDL commands, so any attempt at creating a table, dropping one or indexing one for example, will fail, as the following SQL*Plus example shows :

	SQL> begin
	  2  create table test(a number);
	  3  end;
	  4  /
	create table test(a number);
	*
	ERROR at line 2:
	ORA-06550: line 2, column 1:
	PLS-00103: Encountered the symbol "CREATE" when expecting ....

In order to be allowed to create the above table, you must resort to using either the dbms_sql package in Oracle 7, or the new dynamic sql abilities of Oracle 8i. The following is an example of creating a table using Oracle 7 :

	SQL> declare
	  2   c1 binary_integer;
	  3  begin
	  4   c1 := dbms_sql.open_cursor;
	  5   dbms_sql.parse(c1, 'create table test(a number)', dbms_sql.native);
	  6   dbms_sql.close_cursor(c1);
	  7  end;
	  8  /
	PL/SQL procedure successfully completed.
	SQL> desc test
	 Name                            Null?    Type
	 ------------------------------- -------- ----
	 A                                        NUMBER

Under dbms_sql, a call to parse actually executes the command if it is DDL. If it is a DML statement, the parse call would be followed by an execute call. Next, the same example, this time using Oracle 8i :

	SQL> begin                                                
	  2    execute immediate 'create table test(a number)';   
	  3  end;                                                 
	  4  /                                                    
	PL/SQL procedure successfully completed.                  
	SQL> desc test                                            
	 Name                                      Null?    Type  
	 ----------------------------------------- -------- ------
	 A                                                  NUMBER

And that is all there is to it. Note how 8i's execute dynamic is much less cumbersome that the old dbms_sql package? Don't forget, you need explicit create table privileges if you want to create a table using one of the above methods from within a package, procedure or function. You cannot use privileges granted to your user via a role unless you are using straight SQL commands or running them in anonymous PL/SQL blocks.

Further reading:

Oracle Built in packages - Feuerstein, Dye &Beresniewicz (O'Reilly Press).

PL/SQL User's Guide & Reference manual Release 8.1.x, chapter 10 Native Dynamic SQL.