Creating temporary tables in Oracle stored procedures

Creating temporary tables in Oracle stored procedures

DDL statements cannot be used directly in a stored procedure, so only dynamic SQL statements can be used to perform

--on Commit Delete rows indicates that the staging table is a transaction specification, and Oracle truncates the table after each commit (delete all rows)
--on COMMIT PRESERVE ROWS indicates that the staging table is session-specific and Oracle truncates the table when the session is interrupted.

CREATE OR REPLACE PROCEDURE temptest
(P_searchdate in DATE)
Is
V_count INT;
STR VARCHAR2 (300);
BEGIN
V_count: = 0;
str:= ' drop table sett_dailytest ';
Execute immediate str;
str:= ' CREATE GLOBAL temporary TABLE sett_dailytest (
Naccountid number is not NULL,
Nsubaccountid number NOT NULL)
On COMMIT PRESERVE ROWS ';
Execute immediate str; ----Use dynamic SQL statements to perform
str:= ' INSERT into Sett_dailytest (select Naccountid,nsubaccountid from sett_dailyaccountbalance);
Execute immediate str;
END temptest;

Stored procedure for creating a temporary table above

Here are some things to do to write data to a temporary table.

CREATE OR REPLACE PROCEDURE Pr_dailycheck
(
P_date in Date,
P_office in INTEGER,
P_currency in INTEGER,
P_check in INTEGER,
P_countnum out INTEGER)
Is
V_count INT;
BEGIN
V_count: = 0;
IF P_date is NULL then
Dbms_output.put_line (' date cannot be null ');
ELSE
IF P_check = 1 Then
INSERT INTO Sett_dailytest (select Naccountid,nsubaccountid from Sett_dailyaccountbalance
where dtdate = p_date);
Select
Count (Sd.naccountid) into V_count
From Sett_subaccount ss,sett_account sa,sett_dailytest SD
where Sd.naccountid = sa.id and Sd.nsubaccountid = ss.id and sa.id = Ss.naccountid
and Sa.nofficeid = P_office and Sa.ncurrencyid = p_currency
and RowNum < 2;
COMMIT;
P_countnum: = V_count;
Dbms_output.put_line (P_countnum);
END IF;
IF P_check = 2 Then
INSERT INTO Sett_dailytest (select Naccountid,nsubaccountid from Sett_dailyaccountbalance
where dtdate = p_date);
Select
Count (Sd.naccountid) into V_count
From Sett_cfsubaccount ss,sett_account sa,sett_dailytest SD
where Sd.naccountid = sa.id and Sd.nsubaccountid = ss.id and sa.id = Ss.naccountid
and Sa.nofficeid = P_office and Sa.ncurrencyid = p_currency
and RowNum < 2;
COMMIT;
P_countnum: = V_count;
Dbms_output.put_line (P_countnum);
END IF;
END IF;
END Pr_dailycheck;