Oracle - BULK COLLECT INTO VARRAY used with Bind Variables only collecting column headers

Question

Quick Disclaimer: First thing out of the way, I know the preferred way of handling dynamic SQL in Oracle now is the DBMS_SQL package but unfortunately my application team does not have the grants to execute these procs at the moment and I am hoping to get this quick workaround knocked out before our DBA team gets back to me. Also, this database is on Oracle 12c.

Script Goal: I recently developed a Stored Proc (let's call it Original) that uses values in a "control table" to make a large number of updates to certain columns in a database with many schemas and tables. This script I am struggling with now (let's call it Test) is meant to be a quick loop through those columns affected by Original so as to verify that everything worked expectedly. Ultimately, I want to output the top 5 results of each changed column and hand a spooled file to my testing team for validation.

The control_table used in both scripts has 4 columns and looks like this:

OWNER	TABLE_NAME	COLUMN_NAME	ALGORITHM
Schema1	TableA	ColumnA	Method1
Schema1	TableB	ColumnB	Method1
Schema2	TableC	ColumnC	Method2

An example of one of the tables that gets updated by Original (let's say for TableA above) would be:

OtherCol1	OtherCol2	ColumnA	OtherCol3
Ignored	Ignored	UpdatedData1	Ignored
Ignored	Ignored	UpdatedData2	Ignored
Ignored	Ignored	UpdatedData3	Ignored

Issue with Test script: I have the dynamic SQL - I believe - working as it needs and I have been trying to figure out how best to print the results of the EXECUTE IMMEDIATE command to output. In doing some reading, I found that BULK COLLECT INTO should allow me to store the results of the dynamic queries into a COLLECTION which I can then print with dbms_output. I have attempted to do this with both a TABLE and a VARRAY but in both cases when I print, I am finding that the data stored in my collection is the column header of my dynamic query instead of the query values! The only thing I can think that could be the problem is the combining of BULK COLLECT INTO with the USING command when I run the dynamic statement but I have seen nothing in the documentation to indicate that these two commands are incompatible and my Test procedure below compiles without issue (and even seems to run ok).

Test Script:

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
    l_script VARCHAR2(500);
    l_errm  VARCHAR2(64);
    TYPE results IS VARRAY(5) OF VARCHAR2(250);
    va_cols results;  --Defining here with a VARRAY but I have also tried with a table
BEGIN
    FOR c_col IN(
        SELECT owner, table_name, column_name, algorithm FROM control_list)
    LOOP
        l_errm := NULL;
        va_cols := NULL;
        BEGIN
            dbms_output.put_line('Column '|| c_col.column_name || ' of table ' || c_col.owner || 
                    '.' || c_col.table_name || ' used algorithm ' || c_col.algorithm);
            
            l_script := 'SELECT :1 FROM ' || c_col.owner || '.' || c_col.table_name || 
                ' WHERE :2 IS NOT NULL FETCH FIRST 5 ROWS ONLY';
            dbms_output.put_line('Script sent to Exec Immediate: ' || l_script); --Print l_script for debugging
            
            EXECUTE IMMEDIATE l_script BULK COLLECT INTO va_cols USING c_col.column_name, c_col.column_name;
            dbms_output.put_line(va_cols(1));
            dbms_output.put_line(va_cols(2));
            dbms_output.put_line(va_cols(3));
            dbms_output.put_line(va_cols(4));
            dbms_output.put_line(va_cols(5));

        EXCEPTION         
         WHEN OTHERS THEN   
            l_errm := SUBSTR(SQLERRM, 1, 64);
            dbms_output.put_line(' ERROR: ' || l_errm || '. Skipping row');
            CONTINUE;
        END;
        
    END LOOP;
  END;
/

So my intended dbms_output of the script above is:

Column ColumnA of table Schema1.TableA used algorithm Method1
Script sent to Exec Immediate: SELECT :1 FROM SCHEMA1.TABLEA WHERE :2 IS NOT NULL FETCH FIRST 5 ROWS ONLY    
UpdatedData1
UpdatedData2
UpdatedData3
UpdatedData4
UpdatedData5

Instead, however, bizarrely, what I am getting when I run this is:

Column ColumnA of table Schema1.TableA used algorithm Method1
Script sent to Exec Immediate: SELECT :1 FROM SCHEMA1.TABLEA WHERE :2 IS NOT NULL FETCH FIRST 5 ROWS ONLY     
ColumnA
ColumnA
ColumnA
ColumnA
ColumnA

Has anyone seen this before and know what I am doing wrong? Thanks in advance!!

Answer 1

You can't use bind variables to change what columns you're referencing. You use bind variables to specify particular values at runtime. When you do

l_script := 'SELECT :1 FROM ' || c_col.owner || '.' || c_col.table_name || 
                ' WHERE :2 IS NOT NULL FETCH FIRST 5 ROWS ONLY';
            
EXECUTE IMMEDIATE l_script BULK COLLECT INTO va_cols USING c_col.column_name, c_col.column_name;

you're telling Oracle that you want to select the literal string in the variable c_col.column_name. Not the column in the table by that name. Which is why every row returns that literal value.

You'd need to dynamically assemble the SQL statement with the column names, not try to use them as bind variables. So something like

l_script := 'SELECT ' || c_col.column_name || 
            ' FROM ' || c_col.owner || '.' || c_col.table_name || 
            ' WHERE ' || c_col.column_name || ' IS NOT NULL FETCH FIRST 5 ROWS ONLY';
            
EXECUTE IMMEDIATE l_script BULK COLLECT INTO va_cols;