From the below query, I am expecting to get the result set into an associative array and run through a loop. This query will have dynamic columns, dynamic tables joined and the datatypes of the columns are unknown.
CREATE OR REPLACE PROCEDURE RULE_TEST AS
qry_stmt CLOB;
type qry_stmt_result_type is table of varchar2(3000) index by binary_integer;
qry_stmt_result qry_stmt_result_type;
BEGIN
qry_stmt:='SELECT col_1, col_2, col_3, col_N FROM table_x
JOIN on table_x.col_1=table_y.col_5
JOIN on table_y.col_2=table_z.col_3
WHERE table_z.col_2 = 1';
EXECUTE IMMEDIATE qry_stmt bulk collect into qry_stmt_result;
FOR i IN qry_stmt_result.first..qry_stmt_result.last LOOP
DBMS_OUTPUT.put_line(qry_stmt_result(i));
DBMS_OUTPUT.put_line(qry_stmt_result(i).col_1);
END LOOP;
END RULE_TEST;
dbms_sqlpackage to handle an arbitrary SQL statement (realizing that you need to do a lot of work to ensure you're safe from SQL injection attacks). But you're not going to be able realistically to fetch the data into a single collection. You could parse, describe the columns, and do array binds to fetch the data. You could start with this example stackoverflow.com/questions/31509018/…