味味A

假設有一個procedure，實作動態產生TEMPORARY TABLE的程式碼，compiler都ok 但是一執行就會有錯誤訊息。

ORA-01031: insufficient privileges

CREATE OR REPLACE PROCEDURE TEST2
IS
   sql_stmt   VARCHAR2 (4000);
BEGIN
   sql_stmt :=
         'CREATE GLOBAL TEMPORARY TABLE '
      || ' t10'
      || ' ON COMMIT PRESERVE ROWS  AS (SELECT * FROM '
      || ' t9'
      || ' WHERE 1=2) ';
   EXECUTE IMMEDIATE sql_stmt;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

怎麼會權限不足?

這個user已經給了dba權限啊? 且在SQLPLUS 直接執行 相同的CREATE GLOBAL TEMPORARY TABLE …SQL 是可以正常執行的，表示權限OK

有個觀念要先知道:

1.TEMPORARY TABLE 是產生在預設的TEMPORARY TABLESPACE 內
而非使用者的TABLESPACE SCHEMA內。

2.PROCEDURE內執行CREATE 非自己 SCHEMA 的Table，
除非有 CREATE ANY TABLE TO <SCHEMA>…
否則權限會不足，(原因會再去了解再補上)，
有另一種方法很省事是在 CREATE PROCEDURE
加上關鍵字 AUTHID CURRENT_USER 即可。

CREATE OR REPLACE PROCEDURE TEST2
   AUTHID CURRENT_USER
IS
   sql_stmt   VARCHAR2 (4000);
BEGIN
   sql_stmt :=
         'CREATE GLOBAL TEMPORARY TABLE '
      || ' t10'
      || ' ON COMMIT PRESERVE ROWS  AS (SELECT * FROM '
      || ' t9'
      || ' WHERE 1=2) ';

   EXECUTE IMMEDIATE sql_stmt;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;