Re: Create table problem using Dynamic Query

Ach C via DBMonster.com wrote:
> 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;
> /[/CODE] 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[/CODE] While I can create that table using the same user
> without any problem!
> [B]My question is:[/B]What privilege should I grant to user(minimum of
> privileges please! ) to execute that procedure successfuly?
> -Thanks
>
>

create global temporary table?

But... why?!? Please do yourself a favor, and look up global temporary tables on tahiti.oracle.com, and you will probably find you just need to create a GTT once - and all sessions will have their own -empty- copy!

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...