oracle: display results of dynamic sql based on antoher sql

Question

I would like to display results of dynamic sql based on antoher sql, but get error message. My code:

DECLARE
    sql_qry      VARCHAR2(1000) := NULL;
    TYPE results IS
        TABLE OF all_tab_columns%rowtype;
    results_tbl  results;
BEGIN
    FOR i IN (
        SELECT
            *
        FROM
            all_tab_columns
        WHERE
                owner = 'OWNER_XYZ'
            AND upper(column_name) LIKE '%COLUMN_XYZ%'
        ORDER BY
            table_name,
            column_name
    ) LOOP
        sql_qry := ' SELECT DISTINCT '
                   || i.column_name
                   || ' as column_name '
                   || ' FROM '
                   || i.owner
                   || '.'
                   || i.table_name
                   || ' WHERE SUBSTR('
                   || i.column_name
                   || ',1,1) =  ''Y''';
        EXECUTE IMMEDIATE sql_qry BULK COLLECT
        INTO results_tbl;
           dbms_output.put_line(results_tbl);
    END LOOP;
END;

I get the error message:

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

In fact I need the results of all queries with an union between them like that [1] [1]: https://i.sstatic.net/llxzr.png

Answer 1

Your dynamic query is selecting a single column, but you are bulk collecting into results_tbl, which is of type results, based on all_tab_columns%rowtype - which has lots of columns.

If you define your collection type as a single column of the data type you need, i.e. some length of string:

DECLARE
    sql_qry      VARCHAR2(1000) := NULL;
    TYPE results IS
        TABLE OF varchar2(4000);
    results_tbl  results;
...

You will then bulk collect a single column into that single-column collection. To display the results you need to loop over the collection:

        FOR j IN 1..results_tbl.COUNT loop
           dbms_output.put_line(results_tbl(j));
        END LOOP;

so the whole block becomes:

DECLARE
    sql_qry      VARCHAR2(1000) := NULL;
    TYPE results IS
        TABLE OF varchar2(4000);
    results_tbl  results;
BEGIN
    FOR i IN (
        SELECT
            *
        FROM
            all_tab_columns
        WHERE
                owner = 'OWNER_XYZ'
            AND upper(column_name) LIKE '%COLUMN_XYZ%'
        ORDER BY
            table_name,
            column_name
    ) LOOP
        sql_qry := ' SELECT DISTINCT '
                   || i.column_name
                   || ' as column_name '
                   || ' FROM '
                   || i.owner
                   || '.'
                   || i.table_name
                   || ' WHERE SUBSTR('
                   || i.column_name
                   || ',1,1) =  ''Y''';
        EXECUTE IMMEDIATE sql_qry BULK COLLECT
        INTO results_tbl;
        
        FOR j IN 1..results_tbl.COUNT loop
           dbms_output.put_line(results_tbl(j));
        END LOOP;
    END LOOP;
END;
/

However, you can also do this without PL/SQL, using a variation on an XML trick. You can get the results of the dynamic query as an XML document using something like:

select dbms_xmlgen.getxmltype(
    'select distinct "' || column_name || '" as value'
      || ' from "' || owner || '"."' || table_name || '"'
      || ' where substr("' || column_name || '", 1, 1) = ''Y'''
  )
from all_tab_columns
where owner = 'OWNER_XYZ'
and upper(column_name) like '%COLUMN_XYZ%';

and then extract the value you want from that:

with cte (xml) as (
  select dbms_xmlgen.getxmltype(
      'select distinct "' || column_name || '" as value'
        || ' from "' || owner || '"."' || table_name || '"'
        || ' where substr("' || column_name || '", 1, 1) = ''Y'''
    )
  from all_tab_columns
  where owner = 'OWNER_XYZ'
  and upper(column_name) like '%COLUMN_XYZ%'
)
select x.value
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns value varchar2(4000) path 'VALUE'
) x;

You can also easily include the table each value came from if you want that information (and the owner, and actual column name, etc.).

db<>fiddle showing both approaches.