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.