EXECUTE IMMEDIATE in Stored Procedure

[samahit]

Hi All,
Would U don't mind to clear my doubt. I have this sequence of operations on SQLPlus. I am not able use EXECUTE IMMEDIATE statement in a stored procedure.
SQL> create or replace procedure dk_proc is
2 begin
3 execute immediate 'create table a_dk(aa number)';
4 end;
5 /

Procedure created.

SQL> execute dk_proc
BEGIN dk_proc; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "INVENTORY.DK_PROC", line 3
ORA-06512: at line 1


SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
BECOME USER
DROP USER
CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER

PRIVILEGE
----------------------------------------
DROP ANY CLUSTER
CREATE ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
DROP ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
AUDIT ANY
CREATE PROCEDURE

PRIVILEGE
----------------------------------------
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
DROP PROFILE
ANALYZE ANY
CREATE ANY SNAPSHOT
DROP ANY SNAPSHOT
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
CREATE ANY LIBRARY

PRIVILEGE
----------------------------------------
DROP ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
DROP ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
DROP ANY INDEXTYPE
GLOBAL QUERY REWRITE
CREATE ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER

79 rows selected.

SQL>
bye
sm

[jovery]

Is the CREATE TABLE privilege granted via a role? eg DBA.

When executing this type of statement using the exec immediate you must have the privilege granted explictly to the user.

In this case you must grant CREATE TABLE to the user even though the privilege is granted via a role.

Hope that helps

Regards

[samahit]

Thanks a lot for ur reply,I was really struggling for it.
samahit