无法在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语句.

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;

推荐答案

您动态创建GTT，因此您的INSERT也应该是动态.

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

请注意，PL/SQL甚至在执行每个静态查询之前都会对其进行验证. 这就是为什么即使在编译时也会出现ORA-942 Table or view doesn't exist错误的原因！

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;

然后最后，您不应在运行时创建GTT.为了避免此类问题.无论如何，GTT都会在每个会话中进入 local .

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.

编辑:如Lalit所说，GTT的DDL不接受CREATE OR REPLACE

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

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