The temp table is not drop at the end of session

Question

If my understanding is correct, the temp table should be dropped automatically at the end of session. But after my stored procedure ends, the temp table is still there. Is anybody know how to use the tmp table? Thank you in advance.

Answer 1

A temp table exists for the duration of your session. So if you create a temp table in a stored proc and you are still in the same session the temp table will still be there. I would recommend dropping the table in the stored proc. Other RDBMS like SQL Server allow for a table to exist as a variable which sounds like what you are wanting.

Answer 2

Generally The Temp table gets cleaned up when a standalone procedure ends.

However, ff we are calling the procedure within a loop then it behaves as a single session throughout the iterations.

Hence It is the same as creating the same temp table again and again without dropping it.

It's better practice of having "drop table " whenever we are using Temp tables inside a procedure. Rerun will not be impacted.

example :

FOR v_cnt IN SELECT load_dt AS history_date FROM process_dates ORDER BY history_date ASC                    
    LOOP
       CALL sp_Logic(v_Record.history_date, 'ALL');
    END LOOP;


sp_Logic(v_Record.history_date, 'ALL');

Procedure:

CREATE OR REPLACE PROCEDURE sp_Logic"(DATE, CHARACTER VARYING(ANY))
RETURNS CHARACTER VARYING(ANY) EXECUTE AS CALLER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE 
...
..
..
v_Curr_Step := 'Populate ref_id';
CREATE TEMP TABLE TEMP_REFID_1 AS 
        (SELECT * FROM ref_id) ;
..
..
EXCEPTION 
    WHEN OTHERS 
    THEN 
        RAISE EXCEPTION 'CAUGHT EXCEPTION WHILE % : %', v_Curr_Step, SQLERRM;
END; 
END_PROC;

This code will create issue once the SELECT load_dt AS history_date FROM process_date query returns more than one dates.

I am not sure if the above explanation makes sense. I tried :-)