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.