CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Avengers
Avengers

Reputation: 15

TEMP table in stored procedure oracle 11g

Is there any way to create a temp table with the stored procedure and use the same in the ref cursor in the same stored procedure. I wrote something like below, it's not working....

    CREATE OR REPLACE PROCEDURE USP_TEST(
    CUR_QUOTE OUT SYS_REFCURSOR) AS 
    BEGIN
    CREATE GLOBAL TEMPORARY TABLE users1 ON COMMIT PRESERVE ROWS
    AS
      SELECT '[email protected]' FROM DUAL;
      OPEN CUR_QUOTE FOR
      SELECT DISTINCT CREATEDBY
      FROM QUOTE
      WHERE TRUNC(DATEOFENQUIRY)=TRUNC(SYSDATE-1) AND CREATEDBY = users1.EMAIL;
    END;

And delete the temp table at the end. Please suggest with some sample code... Keep coding :)

Upvotes: 0

Views: 476

Answers (1)

William Robertson
William Robertson

Reputation: 15991

If you drop the table then the cursor is invalidated.

From 18c you can use private temporary tables:

create or replace procedure usp_test
    ( cur_quote out sys_refcursor )
as
begin
    execute immediate
        'create private temporary table ora$ptt_demo' ||chr(10)||
        'on commit drop definition as' ||chr(10)||
        'select sysdate -1 as dateofenquiry, ''[email protected]'' as createdby' ||chr(10)||
        'from   dual';

    open cur_quote for
        'select distinct createdby from ora$ptt_demo where trunc(dateofenquiry) = trunc(sysdate - 1)';
end;

Note that the table name must have the prefix defined by the PRIVATE_TEMP_TABLE_PREFIX parameter (default ORA$PTT_), and you must commit before calling the procedure a second time.

Upvotes: 1

Related Questions