How to execute dynamic SQL queries and show combined results in Oracle

Data and Databases
1

I’m trying to run dynamic SQL statements in Oracle and display the combined output, but I keep getting an error. Here’s what I’m working with:

DECLARE
    dynamic_query VARCHAR2(2000);
    TYPE record_collection IS TABLE OF user_tab_columns%rowtype;
    query_results record_collection;
BEGIN
    FOR rec IN (
        SELECT *
        FROM user_tab_columns
        WHERE owner = 'SCHEMA_ABC'
          AND upper(column_name) LIKE '%FIELD_ABC%'
        ORDER BY table_name, column_name
    ) LOOP
        dynamic_query := 'SELECT DISTINCT '
                      || rec.column_name
                      || ' as field_name '
                      || 'FROM '
                      || rec.owner
                      || '.'
                      || rec.table_name
                      || ' WHERE SUBSTR('
                      || rec.column_name
                      || ',1,1) = ''N''';
        
        EXECUTE IMMEDIATE dynamic_query BULK COLLECT INTO query_results;
        dbms_output.put_line(query_results);
    END LOOP;
END;

The error I’m getting is:

PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

What I really want is to combine all the query results together like a UNION operation. Any suggestions on how to fix this?

2

Your problem is a data type mismatch. You’re trying to collect a single VARCHAR2 column into a record that’s based on user_tab_columns%rowtype - but that has multiple columns, not just one. Change your collection to TYPE record_collection IS TABLE OF VARCHAR2(4000); instead. For the union part, don’t execute multiple queries. Build one big concatenated string outside your loop - just append each SELECT with UNION ALL between them, then run it once at the end. Way more efficient and you’ll get the combined results you want. Also double-check your schema references. You’re selecting from user_tab_columns but using rec.owner in the dynamic query, which might cause issues.

3

you cant print a collection directly like that. try using a ref cursor instead - it’ll make combining results way easier. what exactly do you want in the final output? just the distinct values or something more specific?

4

u shudnt bulk collect into user_tab_columns%rowtype if ur query only gives 1 column. just use a varchar2 var to loop and print each result. for the union, make a big query str that concatenates each select using UNION ALL.