I have a table operations_tb_validation where each row contains a dynamic SELECT statement in the operation_script column. Each SELECT returns two columns:
- The first column is a
VARCHAR (e.g., transformation business unit).
- The second column is a
NUMBER (e.g., a count).
I want to write a PL/SQL procedure that:
- Executes each of these SELECT statements dynamically.
- Fetches all the rows from the result set (returned by the SELECT).
- Inserts each row (the values of both columns) into a log table
validation_log_tb, along with metadata like table_name, execution timestamps, and a comment.
- Handles exceptions gracefully and logs the error in the same log table — no rollback should occur if an error is raised.
- Does not assume the structure of the result except that it always has exactly two columns:
VARCHAR, NUMBER.
I’ve tried using both EXECUTE IMMEDIATE and DBMS_SQL, but I get ORA-01002: fetch out of sequence when trying to fetch rows. I believe I may need to use collections or record types to store and iterate over the result set from the dynamic SELECT.
How can I correctly capture the dynamic result set and insert each row into my log table?
Any example or pattern for this kind of dynamic result handling would be very helpful.
Table Structures
1. operations_tb_validation
sql
CopyEdit
CREATE TABLE operations_tb_validation ( id NUMBER PRIMARY KEY, db_name VARCHAR2(100), owner_name VARCHAR2(100), table_name VARCHAR2(100), operation_script CLOB, processed_flag CHAR(1), executed_by VARCHAR2(100), executed_at TIMESTAMP );
2. validation_log_tb
CREATE TABLE validation_log_tb ( log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR2(100), transformation_bu VARCHAR2(100), result_count NUMBER, executed_at TIMESTAMP, executed_by VARCHAR2(100), comment_text VARCHAR2(4000), start_time TIMESTAMP, end_time TIMESTAMP, run_time NUMBER );
-
Sample data from -
Insert into operations_tb_validation(DB_NAME,OWNER_NAME,OPERATION_SCRIPT,PROCESSED_FLAG,EXECUTED_BY,TABLE_NAME) values ('GBSO','GBLAS','SELECT b.transformation_business, count(*) FROM TO_DELIVERY a, itm_igor_prod_bu_tb b WHERE NVL(a.GBL_ITMID, ''ZZZ-ZZZ'') = NVL(b.GBL_ITMID, ''ZZZ-ZZZ'') AND a.CORP_TIME_KEY_AGM <= ''20230299'' GROUP BY b.transformation_business','N',null,'F_LAS_STO_DELIVERY');
Insert into operations_tb_validation (DB_NAME,OWNER_NAME,OPERATION_SCRIPT,PROCESSED_FLAG,EXECUTED_BY,TABLE_NAME) values ('GBSO','GBLAS','SELECT b.transformation_business, count(*) FROM STO_DELIVERY a, f_ls_report_locations b WHERE a.RPTLOC = b.ORG_ID AND a.CORP_TIME_KEY_AGM > ''20230299'' GROUP BY b.transformation_business','N',null,'F_LAS_STO_DELIVERY');
Let me know if you need anyother details
Thanks,