In Oracle (11gR2), I have created a procedure:
CREATE OR REPLACE PROCEDURE myproc
s_sql_delete := 'DROP TABLE mytab1';
EXECUTE IMMEDIATE s_sql_delete;
s_sql_insert := 'CREATE TABLE test2 (f1 date)';
EXECUTE IMMEDIATE s_sql_insert;
The delete table works but the create table does not ..
The user has create table privilege. And it can create table on sql prompt.
I get the error :
ORA-01031: insufficient privileges
ORA-06512: at "myproc", line xx
ORA-06512: at line 3