EXECUTE IMMEDIATE in Stored Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: EXECUTE IMMEDIATE in Stored Procedure

  1. #1
    Join Date
    Jun 2001
    Posts
    28

    Red face

    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

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jun 2001
    Posts
    28

    thanks

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width