未在 oracle 中创建立即执行临时表 ORA-00942 [英] EXECUTE IMMEDIATE Temp table in oracle does not get created ORA-00942

问题描述

基于 这个答案 我试图创建临时表，但是我收到异常 ORA-00942:table or view does not exist 我认为 'CREATE OR REPLACE GLOBAL TEMPORARY TABLE TempQandA(column1 number) ON COMMIT PRESERVE ROWS 有问题' 语句，它在 insert into TempQandA(column1) VALUES (1); 时失败.
请在下面找到 SQL 语句.

DECLARETransactioDetailId 数字 := 3132;HomePhoneNumber varchar(20);手机号码 varchar(20);电子邮件 varchar(20);无论 varchar(20);开始EXECUTE IMMEDIATE 'CREATE or REPLACE GLOBAL TEMPORARY TABLE TempQandA(column1 number) ON COMMIT PRESERVE ROWS';开始SELECT contactvalue 到 HomePhoneNumber FROM customercontact CC内部连接 ​​TRANSACTIONMAIN tm on tm.customerid = CC.customeridtd.transactionid = tm.transactionid 上的内连接事务详细信息 td其中 contacttypeid = 2 和 td.transactiondetailid = TransactioDetailId；例外WHEN NO_DATA_FOUND THEN家庭电话号码:= NULL；开始SELECT contactvalue into MobileNumber FROM customercontact CC内部连接 ​​TRANSACTIONMAIN tm on tm.customerid = CC.customeridtd.transactionid = tm.transactionid 上的内连接事务详细信息 td其中 contacttypeid = 3 和 td.transactiondetailid = TransactioDetailId；例外WHEN NO_DATA_FOUND THEN手机号码:= NULL;开始SELECT contactvalue into Email FROM customercontact CC内部连接 ​​TRANSACTIONMAIN tm on tm.customerid = CC.customeridtd.transactionid = tm.transactionid 上的内连接事务详细信息 td其中 contacttypeid = 1 和 td.transactiondetailid = TransactioDetailId；例外WHEN NO_DATA_FOUND THEN电子邮件:= NULL;开始插入 TempQandA(column1) VALUES (1);结尾;结尾;结尾;结尾;结尾;

解决方案

你动态创建GTT，所以你的INSERT也应该是动态...

请注意，PL/SQL 在执行每个静态查询之前都会对其进行验证.这就是为什么即使在编译时您也会收到 ORA-942 表或视图不存在 错误！

因此，为了逃避这种语义检查，我们必须使调用动态化.

 开始EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TempQandA(column1 number) ON COMMIT PRESERVE ROWS';.....EXECUTE IMMEDIATE '插入 TempQandA(column1) VALUES (1)';结尾;

而且最后，您不应该在运行时创建GTT..以避免此类问题.无论如何，GTT 都会本地到每个会话.

正如 Lalit 所说，GTT 的 DDL 不接受 CREATE OR REPLACE

Based on this answer I was trying to create temp table, however I am getting exception ORA-00942:table or view does not exist I would assume there is something wrong with 'CREATE OR REPLACE GLOBAL TEMPORARY TABLE TempQandA(column1 number) ON COMMIT PRESERVE ROWS' statement, it fails on insert into TempQandA(column1) VALUES (1);.
Please find SQL statement below.

DECLARE 
TransactioDetailId numeric := 3132;
HomePhoneNumber varchar(20);
MobileNumber varchar(20);
Email varchar(20);
whatever varchar(20);
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE GLOBAL TEMPORARY TABLE TempQandA(column1 number) ON COMMIT PRESERVE ROWS';

        BEGIN
        SELECT contactvalue into HomePhoneNumber  FROM customercontact CC
         inner join TRANSACTIONMAIN tm on tm.customerid = CC.customerid
         inner join transactiondetail td on td.transactionid = tm.transactionid
         where contacttypeid = 2 and td.transactiondetailid = TransactioDetailId;
         EXCEPTION
         WHEN NO_DATA_FOUND THEN
         HomePhoneNumber := NULL;
                         begin
                         SELECT  contactvalue into MobileNumber  FROM customercontact CC
                         inner join TRANSACTIONMAIN tm on tm.customerid = CC.customerid
                         inner join transactiondetail td on td.transactionid = tm.transactionid
                         where contacttypeid = 3 and td.transactiondetailid = TransactioDetailId;
                         EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                         MobileNumber := NULL;
                                      begin
                                      SELECT  contactvalue into Email  FROM customercontact CC
                                      inner join TRANSACTIONMAIN tm on tm.customerid = CC.customerid
                                      inner join transactiondetail td on td.transactionid = tm.transactionid
                                      where contacttypeid = 1 and td.transactiondetailid = TransactioDetailId;
                                      EXCEPTION
                                      WHEN NO_DATA_FOUND THEN
                                      Email := NULL;

                                            begin
                                            insert into TempQandA(column1) VALUES (1);
                                             end;
                                     end;
                     end;
         end;
END;

解决方案

You dynamically create the GTT, so your INSERT too should be dynamic..

Note that, PL/SQL validates every static query before even executing it. Thats why you get ORA-942 Table or view doesn't exist error even at compilation time!

So, to escape this semantic check, we have to make the call dynamic.

    BEGIN
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TempQandA(column1 number) ON COMMIT PRESERVE ROWS';

.....

    EXECUTE IMMEDIATE ' insert into TempQandA(column1) VALUES (1)';
    END;

And Finally, you should not be creating the GTT on run time.. To avoid such issues. The GTT is anyway going to local to every session.

EDIT: As Lalit says, GTT's DDL doesn't accept CREATE OR REPLACE

这篇关于未在 oracle 中创建立即执行临时表 ORA-00942的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！