List aggregate column values when column contains customer-CodePudding

I am running a query on ALL_TAB_COLUMNS to select for column_names where it contains the name customer, then enter these tables and LISTAGG the values from these columns.

However, I get an exceptionally long error that I cannot understand:

PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 22:
PLS-00364: loop index variable 'TB' use is invalid
ORA-06550: line 15, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"

Here is what I have tried:

DECLARE
vcol VARCHAR2(128);
vtable VARCHAR(128);
BEGIN
FOR VAL IN (SELECT COLUMN_NAME, TABLE_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%Customer%' AND DATA_TYPE in ( 'CHAR' ,  'VARCHAR2' ))
LOOP
vcol := VAL.COLUMN_NAME;
vtable := VAL.TABLE_NAME;
FOR TB IN (
WITH A AS (
  SELECT DISTINCT vcol FROM vtable
  ) SELECT LISTAGG(vcol, ',') as cols FROM A
)
LOOP
dbms_output.put_line(TB.cols);
END LOOP;
END LOOP;
END;

CodePudding user response：

You can't refer to table and column names using run-time variables, unless you use dynamic SQL. You could construct each statement as a string, then open the cursor dynamically using that query string:

DECLARE
  vstmt VARCHAR2(4000);
  vresult VARCHAR2(4000);
  vcursor SYS_REFCURSOR;
BEGIN
  FOR VAL IN (SELECT COLUMN_NAME, TABLE_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%Customer%')
  LOOP
    vstmt := 'WITH A AS (
  SELECT DISTINCT "' || VAL.COLUMN_NAME || '" FROM "' || VAL.TABLE_NAME || '"
  ) SELECT LISTAGG("' || VAL.COLUMN_NAME || '", '','') FROM A';

    OPEN vcursor FOR vstmt;
    LOOP
      FETCH vcursor INTO vresult;
      EXIT WHEN vcursor%NOTFOUND;
      dbms_output.put_line(vresult);
    END LOOP;
  END LOOP;
END;
/

Or as the lack of a WITHIN GROUP clause on your LISTAGG implies you're on 19c or later, you can simplify the dynamic statement to:

    vstmt := 'SELECT LISTAGG(DISTINCT "' || VAL.COLUMN_NAME || '", '','') FROM "' || VAL.TABLE_NAME || '"';

You can also do the same thing without PL/SQL, using an XML trick do handle the dynamic statement:

select table_name, column_name,
  xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select listagg(distinct "' || column_name || '", '','') as c '
      || 'from "' || table_name || '"'))
  returning content) as value_list
from all_tab_columns
where column_name like '%Customer%';

Your filter where column_name like '%Customer%' implies you have quoted identifiers. If that's not the case then the column name will be in uppercase by default; but you could search for any case with where upper(column_name) like '%CUSTOMER%'.

And as you're looking up the tables and columns from all_tables, not user_tables, you should really be specifying the owner as well, whether you stick with PL/SQL or use the XML approach:

select owner, table_name, column_name,
  xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select listagg(distinct "' || column_name || '", '','') as c '
      || 'from "' || owner || '"."' || table_name || '"'))
  returning content) as value_list
from all_tab_columns
where upper(column_name) like '%CUSTOMER%';

fiddle including some dummy tables.