Concatenating regexp_replace into listagg: Result too long (SQL Error: ORA-01489)

Question

I have created a pl/sql procedure for a package that is performing a reconciliation between sets of tables which should match.

I am using listagg to concatenate the column names of the current table name in the loop into a string used in a dynamic SQL statement that compares two tables (34 sets, looped for each table name).

The procedure worked as expected, but the results were returned unexpectedly from the minus. After researching, I determined that some fields contained a HEX (00) character received in the flat file that populates the data on only the data from one side of the recon. In order to account for the special characters, I added a regexp_replace concatenated in line with the listagg in the column name select so it outputs the complete listagg results with each column name wrapped in a regexp_replace.

It works. However, some tables have over a hundred columns, and the listagg failes for the results being over 4000 characters.

Is there a better way to go about this entire thing?

Here is the code:

Collects column names into the comma-separated list (comma character is concatenated into the string itself for use as a separator in dynamic SQL select below)

execute immediate
'SELECT ' || q'{listagg('regexp_replace(' || column_name || ', ''[^A-Z0-9 ]'', '''')',  '||'', '' || ')}' || ' within group (order by rownum) "COLUMN_NAME"
FROM user_tab_cols
where table_name =''' || csrpubtable.table_name || ''''

into v_column_names;

These two dynamic SQL statements perform the reconciliation in both directions. These aren't directly related to the error, but definitely to my question of a better overall way to accomplish the task.

--Insert data to RECON_PUB_TABLES where record exists in FILE but not PROD
execute immediate
'INSERT INTO RECON_PUB_TABLES
SELECT ''' || csrpubtable.table_name || ''', ''FILE'' , ' || v_column_names || ', trunc(sysdate) from ' || csrpubtable.table_name || '
minus
SELECT ''' || csrpubtable.table_name || ''', ''FILE'' , ' || v_column_names || ', trunc(sysdate) from ' || csrpubtable.table_name || '@pub_recon2prod where trunc(' || v_lastupdate_column || ') <= trunc(to_date(''' || v_compare_date || ''', ''dd-MON-yy''))';

--Insert data to RECON_PUB_TABLES where record exists in PROD but not FILE
execute immediate
'INSERT INTO RECON_PUB_TABLES
SELECT ''' || csrpubtable.table_name || ''', ''PROD'' , ' || v_column_names || ', trunc(sysdate) from ' || csrpubtable.table_name || '@pub_recon2prod where trunc(' || v_lastupdate_column || ') <= trunc(to_date(''' || v_compare_date || ''', ''dd-MON-yy''))
minus
SELECT ''' || csrpubtable.table_name || ''', ''PROD'' , ' || v_column_names || ', trunc(sysdate) from ' || csrpubtable.table_name ;

Answer 1

varchar2 is limited to 32k within plsql if 32 is enough you can try something like this

create or replace procedure conc_col_names(tableName IN varchar2)  as 
  collist varchar2(32767); 
begin
  for xx in (select * from user_tab_columns where table_name = tableName order by column_name asc) loop
    if ( length(collist) > 0) then 
      collist := collist||','; 
    end if; 
    collist := collist||'regexp_replace('||xx.column_name||',''[^A-Z0-9 ]'')';
  end loop;

  /* add the rest code for comparing rows in the two table here  */

end; 
/