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!!

Only question, why don't you use a *dynamic cursor*? This [documentation link](https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057) provides an example exact for your use case. Well OK, you'll need an additional inner loop over the column names... — Marmite Bomber, Mar 16 '21 at 19:21
@MarmiteBomber Because I'm not a great PLSQL developer :-) I do appreciate you calling that functionality out though. I'll take a look at that for any future tasks requiring dynamic SQL. Thanks! — , Mar 16 '21 at 19:39
Most developers would say that native dynamic SQL is the preferred way of dealing with dynamic SQL, and that we should avoid `DBMS_SQL` unless necessary. However, the package is still important and should be granted to public by default. If you don't have access to that package there's a chance that someone incorrectly applied a security rule - see my answer [here](https://stackoverflow.com/a/46259410/409172) for an explanation of how these problems often happen. — Jon Heller, Mar 17 '21 at 00:44
@JonHeller Ah. Good call. I do see that same recommendation now in the [DBMS_SQL](https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i1028953) documentation: `Native Dynamic SQL is an alternative to DBMS_SQL that lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic SQL is easier to use and performs better than DBMS_SQL. However, Native Dynamic SQL itself has certain limitations: There is no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs)`. Thanks for calling that out! — , Mar 18 '21 at 01:20

score 2 · Accepted Answer · answered Mar 16 '21 at 19:19

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;

Thank you Justin Cave! That was exactly my mistake. And I'm embarrassed as I should have known better. I structured the query schema and table with that in mind but I think slotting objects as data got me turned around. Appreciate you uncovering my error! — , Mar 16 '21 at 19:36

score 1 · Answer 2 · answered Mar 16 '21 at 19:43

This is approximately what you want. I outer cursor over tables and column to inspect that generate the dynamic SQL.

Inner loop reading the column values from the previous query

DECLARE
  TYPE CurTyp  IS REF CURSOR;
  v_cursor        CurTyp;
  v_value         VARCHAR2(200);
  v_stmt_str      VARCHAR2(200);
BEGIN
    FOR c  IN (
        SELECT   table_name, column_name FROM control_list)
    LOOP
       dbms_output.put_line('tab: '||c.table_name);
       v_stmt_str := 'SELECT '||c.column_name||' FROM '|| c.table_name;
 
       OPEN v_cursor FOR v_stmt_str;

  
       LOOP
         FETCH v_cursor  INTO v_value;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('col: '||c.column_name||' val: '||v_value);
      END LOOP;
    END LOOP;
  CLOSE v_cursor;
END;
/

Ok. I see what you did. That's slick. Thanks for idea. I'll definitely use these dynamic cursors in the future. — , Mar 16 '21 at 20:00

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

2 Answers2