Results 1 to 3 of 3

Thread: Create table problem using Dynamic Query

  1. #1
    Join Date
    Jul 2005
    Posts
    35

    Create table problem using Dynamic Query

    Hi all,
    I want to create a temporary table within a stored procedure so I decided to do it using a dynamic query:
    Code:
    create or replace procedure p1
    as
     begin
     execute immediate 'CREATE GLOBAL TEMPORARY TABLE tt(id number(2))';
     end;
    /
    It created successfuly but when I execute that procedure I got:
    Code:
    SQL> exec p1;
    BEGIN p1; END;
    
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "SCOTT.P1", line 4
    ORA-06512: at line 1
    While I can create that table using the same user without any problem!
    My question is:What privilege should I grant to user(minimum of privileges please! ) to execute that procedure successfuly?
    -Thanks

  2. #2
    Join Date
    Aug 2005
    Posts
    29
    hi

    i faced same prob earlier on 9i. user could create table normally but couldn't thru a exec immediate in a procedure.

    it was kind of funny because i simply had to login as system and give the create table permission to user again explicitly.. then it worked.

    hope it works in ur case too.

  3. #3
    Join Date
    Jul 2005
    Posts
    35
    Thanks for your reply,
    I think one should grant "create table" and "execute procedure" directly not through a role to that user.
    This way it can do it perfectly.
    -Regards

Posting Permissions

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