Основываясь на этом ответе, я пытался создать временную таблицу, однако я получаю исключение 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 ниже.
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;