Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Function for private temp table

foxhoundNov 3 2021

I have a sql script with logic that inserts data to several private temporary tables where amount of data is controlled by values from ora$ptt_props and then later the rows from those private temp tables is duplicated, again controlled by values from ora$ptt_props, to actual tables in the db. The script has several anonymous blocks which execute parts of the job.
The problem I have is to avoid code duplication when accessing values from ora$ptt_props private temp table. Below is an simplified example of the script:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_props
(
col1 NUMBER,
col2 NUMBER,
col3 NUMBER
)
ON COMMIT DROP DEFINITION;

INSERT INTO ora$ptt_props
VALUES
(
10000,
10,
34
);

CREATE OR REPLACE FUNCTION get_col1
RETURN NUMBER
IS col1_val NUMBER;
BEGIN
SELECT col1
INTO col1_val
FROM ora$ptt_props;

RETURN(col1_val);
END;
/

CREATE OR REPLACE FUNCTION get_col2
RETURN NUMBER
IS col2_val NUMBER;
BEGIN
SELECT col2
INTO col2_val
FROM ora$ptt_props;

RETURN(col2_val);
END;
/

CREATE PRIVATE TEMPORARY TABLE ora$ptt_realtab1
ON COMMIT DROP DEFINITION
AS
(
SELECT
realcol1, realcol2, realcol3
FROM realtab1
WHERE 1=2
);

CREATE PRIVATE TEMPORARY TABLE ora$ptt_realtab2
ON COMMIT DROP DEFINITION
AS
(
SELECT
realcol12, realcol22, realcol32
FROM realtab2
WHERE 1=2
);

-- preparing patterns in private temp tables
DECLARE
v_patternamount NUMBER;
BEGIN
v_patternamount := getcol1 / get_col2;

   FOR i in 1..v\_patternamount LOOP  
       INSERT INTO ora$ptt\_realtab1  
           (realcol1, realcol2, realcol3)  
       VALUES  
           ('realval1' || i, 'realcol2, 'realcol3');  

       INSERT INTO ora$ptt\_realtab2  
           (realcol12, realcol22, realcol32)  
       VALUES  
           ('realval2' || i, 'realcol2, 'realcol2');  
   END LOOP;  

END;
/

-- populating real tables in bulk inserts
BEGIN

   FOR i in 1..get\_col2() LOOP  
       INSERT INTO realtab1  
     SELECT  
        realcol1 || i, realcol2, realcol3  
        FROM ora$ptt\_realtab1  

      INSERT INTO realtab2  
     SELECT  
        realcol12 || i, realcol22, realcol32  
        FROM ora$ptt\_realtab2  

   END LOOP;  

END;
/

The above sql code will fail because of functions compilation errors - stored function can't see private temporary table. This is obvious. But how can I implement it to have something like function defined once for the whole sql script that will remain for the time of the transaction like private temporary table does?
I can use SELECT INTO in each anonymous block but this is unnecessary code duplication in my opinion that is error prone.

Comments
Post Details
Added on Nov 3 2021
9 comments
1,989 views