execute immediate giving in ORA-01031: insufficient privileges
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: execute immediate giving in ORA-01031: insufficient privileges

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Posts
    120
    hi all,
    i have to execute a create tabel statement in pl/sql which i could do it will

    execute immediate statement
    but when i execute it give
    ORA-01031: insufficient privileges

    i gave sysdba privileges also but it is still giving it.

    procedure secondproc as
    a number;
    procedure hello as
    begin
    execute immediate 'create table testvariables(id number,name1 varchar2(12))';
    -- execute immediate 'insert into testvariables values (1,''dd'');';
    end;
    begin
    hello;
    -- htp.ps('In secproc');
    -- execute immediate 'select id into a from testvariables';
    -- htp.ps(a);
    htp.ps('hello');
    end;


    please help
    Saurabh Garg
    OCP 9i

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Run first as SYS:

    Code:
    Grant Create Any Table to User;
    It should be given directly for execute immediate, not via a role.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Nov 2001
    Posts
    2
    I have a simmilar problem when creating function based indexes using the execute immediate.

    I have granted 'create any index' to the user and it is possible to create conventional indexes using the execute immediate but when I use a function based index I have the 1031 error.

    What priveleges need to be granted to create a function based index?

    Any clues?

  4. #4
    Join Date
    Sep 2001
    Posts
    34
    I have similar kind of problem while creating function based indexs. When I use execute immediate it gives "-1031 ORA-01031: insufficient privileges". What privileges does this need. It creates conventional indexes but not function based indexes. Even create index statement gives the same error.

    Thanks in advance.

  5. #5
    Join Date
    Jun 2002
    Posts
    4
    U need the query rewrite priv to create functional indexes. Grant it directly to a user and it will work!

  6. #6
    Join Date
    Sep 2001
    Posts
    34
    Thanks so much.

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