Search This Blog

Tuesday, June 10, 2008

Global Temporary Table + ORA-01031

If we are trying to create GLOBAL TEMPORARY TABLE inside a PROCEDURE then we are getting following error

SQL> create or replace procedure PRO_TEST
2 as
3 begin
4 execute immediate 'create global temporary table GTT_TEST (no number)';
5 end;
6 /
Procedure created.

SQL> exec PRO_TEST;

BEGIN PRO_TEST; END;
*

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.PRO_TEST", line 4
ORA-06512: at line 1

Reason: PLSQL stored procedures execute with the base privs of the definer (owner) of the routine meaning that ROLES are not enabled.

It is very bad idea to execute DDL statement (create Global Temporary Table inside procedure) without any compling reason.

Solution: Grant explicit "CREATE TABLE" privilege to Schema which Create & Execute Procedure inside Global Temporary Table.

SQL> conn system@orcl
Enter password:
Connected.
SQL> grant create table to scott;
Grant succeeded.

SQL> create or replace procedure PRO_TEST
2 as
3 begin
4 execute immediate 'create global temporary table GTT_TEST (no number)';
5 end;
6 /
Procedure created.

SQL> exec PRO_TEST;
PL/SQL procedure successfully completed.


2 comments:

Nil_The_Action_Lover said...

one query i had .. for performing the task of theglobal temp table do we need to have knowledge of PL/SQL program as this examples shows the stuffs of PL/SQL macros.
pls do reply ...

EgYpCiO said...

Thank you!