Title: Dynamically Executing "Create Tables"

Author: Sripad Kamat, an Oracle DBA and developer for Quantum Technologies in
Sunnyvale, California.

We use the dbms_sql package to execute SQL statements dynamically. When I tried to create a sequence(or table) using
dbms_sql package through a procedure, I received the error message "insufficient privilege". Therefore, I made use of the
package dbms_sys_sql for creating objects dynamically either through anonymous or named blocks.

Note : if i make use of dbms_sql.parse instead of dbms_sys_sql.parse_as_user
i get insufficient privileges error.

Below is the code :

create or replace procedure test as
          k number;
          stat number;
begin
        k := dbms_sql.open_cursor;
        dbms_sys_sql.parse_as_user(k,'create sequence test_seq',dbms_sql.NATIVE)
;
        stat := dbms_sql.execute(k);
        dbms_sql.close_cursor(k);
        k := dbms_sql.open_cursor;
        dbms_sys_sql.parse_as_user(k,'drop sequence test_seq',dbms_sql.NATIVE);
        stat := dbms_sql.execute(k);
        dbms_sql.close_cursor(k);
end;
/