DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-01031 error executing stored procedure

  1. #1
    Join Date
    Aug 2000
    Posts
    13

    ORA-01031 error executing stored procedure

    Hi all. I have a simple stored procedure that I'd like to run, but I get an insufficient privileges error, even though the user running the procedure owns all the objects involved:

    Here is the error:
    SQL> exec UPDATE_ITEMS
    BEGIN UPDATE_ITEMS; END;

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

    and the source for the procedure: (again, this user owns both table and index)
    CREATE OR REPLACE PROCEDURE UPDATE_ITEMS as
    begin
    execute immediate 'DROP INDEX ITEMS_INDEX';
    execute immediate 'DROP TABLE ITEMS';
    execute immediate 'CREATE TABLE ITEMS as SELECT * FROM STG_ITEMS';
    execute immediate 'CREATE INDEX ITEMS_INDEX ON ITEMS(CONTRACT_NUMBER, ITEM_NUMBER, ITEM_ID)';
    end;
    /

    Thanks in advance for any advice.

  2. #2
    Join Date
    Aug 2000
    Posts
    13
    problem is solved.

    ok. i had initially granted the 'create any table', 'create any index', etc role to this user through a role. after granting these privileges directly to the user, the problem is fixed. it isn't very important now, but if anyone could explain why this is the case, i would appreciate it. thanks.

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by sgm3u
    i had initially granted the 'create any table', 'create any index', etc role
    First of all these are not roles, but are called SYSTEM Privilages.

    granted the 'create any table', 'create any index', etc role to this user through a role
    Roles are disabled for any Stored Program.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Aug 2000
    Posts
    13
    sorry, typo. i meant that i granted those privileges to that role and granted that role to the user in question. i am guessing, however, that your final statement is the answer to my question...

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