Execute immediate bulk collect into

Recently I had to create a stored procedure that referenced a table that was dropped nightly. Because of the table drop, the procedure would instantly become invalid.

I found that bulk operations DO work with execute immediate!! Procedure is as follows:

CREATE OR REPLACE PACKAGE BODY HR_CUSTOM_PROCS_FUNCS IS

PROCEDURE PROC_REFRESH_HR_SECURITY_TREE IS

TYPE NEW_HR_SECURITY_TABLE_DATA IS TABLE OF RDSPRD.HR_SECURITY_TREE%ROWTYPE;

NEW_HR_SECURITY_TABLE_DATA_T NEW_HR_SECURITY_TABLE_DATA;

BEGIN

EXECUTE IMMEDIATE 'SELECT SUBSTR(A.PATH, 1, 6) DEPT,

SUBSTR(A.PATH, 8, 6) ROOT,

SUBSTR(A.PATH, 15, 6) LEVEL1,

SUBSTR(A.PATH, 22, 6) LEVEL2,

SUBSTR(A.PATH, 29, 6) LEVEL3,

SUBSTR(A.PATH, 36, 6) LEVEL4

FROM (SELECT TREE_NODE ||

RPAD(SYS_CONNECT_BY_PATH(TREE_NODE, '',''), 35, '',NULL '') "PATH"

FROM (SELECT TREE_NODE,

TREE_NODE_NUM,

PARENT_NODE_NUM,

EFFDT,

TREE_NAME

FROM RDSSTG_HRSA.PSTREENODE

WHERE TREE_NAME =

''DEPT_SECURITY''

AND EFFDT =

(SELECT MAX(B.EFFDT)

FROM RDSSTG_HRSA.PSTREENODE B

WHERE B.TREE_NAME =

''DEPT_SECURITY''))

START WITH TREE_NODE_NUM = 1

CONNECT BY PRIOR

TREE_NODE_NUM =

PARENT_NODE_NUM

ORDER BY 1) A'

BULK COLLECT

INTO NEW_HR_SECURITY_TABLE_DATA_T;

IF NEW_HR_SECURITY_TABLE_DATA_T.COUNT > 0 THEN

EXECUTE IMMEDIATE 'TRUNCATE TABLE RDSPRD.HR_SECURITY_TREE';

FORALL I IN NEW_HR_SECURITY_TABLE_DATA_T.FIRST .. NEW_HR_SECURITY_TABLE_DATA_T.LAST

INSERT INTO RDSPRD.HR_SECURITY_TREE

VALUES NEW_HR_SECURITY_TABLE_DATA_T

(I);

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

BEGIN

RDS_MAIL.mail('sbealer@csuchico.edu', 'sbealer@csuchico.edu', 'PROC_REFRESH_HR_SECURITY_TREE Failure', 'Error message was: '||SQLERRM);

END;

END PROC_REFRESH_HR_SECURITY_TREE;

END HR_CUSTOM_PROCS_FUNCS;