Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    32

    Unanswered: execute procedure failed

    iam trying to execute procedure called s2 and it failed with errors

    create or replace procedure s2
    as
    begin
    EXECUTE IMMEDIATE 'create table ss(name1 varchar2(100))';
    end;


    SQL> execute s2;
    BEGIN s2; END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "CIBC_ICI.S2", line 5
    ORA-06512: at line 1


    After that i gave 'CREATE ANY TABLE' privilege to that user from system , then it went fine.

    Need clarification : user has got CREATE TABLE privilege, then why should i give 'CREATE ANY TABLE' privilege. Please clarify my doubt.

    Regards
    Sarov

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    probably has something to do with where the table got created (tablespace). Since you are not specifying storage parameters, but you are calling a procedure to create the table, you might be creating the table in the same tablespace/schema as the procedure owner.

    Check the stats on the table to see where it is located, etc.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    Hi

    By default, a stored procedure executes with the privileges of its definer, not its invoker.

    The definer must have the right to create a table in the schema specified in the CREATE TABLE.

    However, the AUTHID clause enables a stored procedure to execute with the privileges of its invoker (current user).

    create or replace procedure s2
    as

    AUTHID CURRENT_USER

    begin
    EXECUTE IMMEDIATE 'create table ss(name1 varchar2(100))';
    end;

Posting Permissions

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