ORA-01489 : result of string concatenation to long

user_18983920 0
Viewed 62

I am creating a dynamic query from a list of tables which are their in my table called : get_table_names

My Query :

SELECT   listagg('SELECT '
                  || ''''
                  || tbl_name
                  || ''''
                  || ' AS TBL , COUNT(*) as CNT FROM '
                  || 'TGT_MB.'
                  || tbl_name
                  || ' union all'
                  || CHAR(10) ) WITH GROUP ( ORDER BY tbl_name) AS sql_str
FROM     get_table_names;

My get_table_names as lots of table , at least 70 table names.

The query works fine for 10 tables but more then that it throws the error like below

ORA-01489 : result of string concatenation to long

Their is some option called EXTEND that option I cannot touch as I have low level privileges. cannot make changes to it until DBA.

Any other work around would be much appreciated like using some XMLAGG or CLOB , BLOB

listagg clob blob oracle sql
1 Answers
Can I respectively suggest that when posting a code snippet, make it sure it runs so that people can help you more easily.  eg your code above took the following debugging before we could even start to see what you want to do

    SQL> SELECT   listagg('SELECT '
      2                    || ''''
      3                    || table_name
      4                    || ''''
      5                    || ' AS TBL , COUNT(*) as CNT FROM '
      6                    || 'TGT_MB.'
      7                    || table_name
      8                    || ' union all'
      9                    || CHAR(10) ) WITH GROUP ( ORDER BY table_name) AS sql_str
     10  FROM     dba_tables
     11  where owner = 'SCOTT';
                      || CHAR(10) ) WITH GROUP ( ORDER BY table_name) AS sql_str
                         *
    ERROR at line 9:
    ORA-00936: missing expression
    
    SQL> SELECT   listagg('SELECT '
      2                    || ''''
      3                    || table_name
      4                    || ''''
      5                    || ' AS TBL , COUNT(*) as CNT FROM '
      6                    || 'TGT_MB.'
      7                    || table_name
      8                    || ' union all'
      9                    || chr(10) ) WITH GROUP ( ORDER BY table_name) AS sql_str
     10  FROM     dba_tables
     11  where owner = 'SCOTT';
                      || chr(10) ) WITH GROUP ( ORDER BY table_name) AS sql_str
                                   *
    ERROR at line 9:
    ORA-00923: FROM keyword not found where expected

Once those fixes are done, we end up here
    
    SQL> SELECT   listagg('SELECT '
      2                    || ''''
      3                    || table_name
      4                    || ''''
      5                    || ' AS TBL , COUNT(*) as CNT FROM '
      6                    || 'TGT_MB.'
      7                    || table_name
      8                    || ' union all'
      9                    || chr(10),'' ) WITHIN GROUP ( ORDER BY table_name) AS sql_str
     10  FROM     dba_tables
     11  where owner = 'SCOTT';
    
    SQL_STR
    -------------------------------------------------------------------------------------
    SELECT 'BONUS' AS TBL , COUNT(*) as CNT FROM TGT_MB.BONUS union all
    SELECT 'DEPT' AS TBL , COUNT(*) as CNT FROM TGT_MB.DEPT union all
    SELECT 'EMP' AS TBL , COUNT(*) as CNT FROM TGT_MB.EMP union all
    SELECT 'EMP2' AS TBL , COUNT(*) as CNT FROM TGT_MB.EMP2 union all
    SELECT 'SALGRADE' AS TBL , COUNT(*) as CNT FROM TGT_MB.SALGRADE union all


which ultimately blows up when we get too many tables

SQL> SELECT   listagg('SELECT '
  2                    || ''''
  3                    || table_name
  4                    || ''''
  5                    || ' AS TBL , COUNT(*) as CNT FROM '
  6                    || 'TGT_MB.'
  7                    || table_name
  8                    || ' union all'
  9                    || chr(10),'' ) WITHIN GROUP ( ORDER BY table_name) AS sql_str
 10  FROM     dba_tables;
FROM     dba_tables
         *
ERROR at line 10:
ORA-01489: result of string concatenation is too long

Now even if we use a tool (eg

https://github.com/connormcd/listagg_clob) to get the results as a clob, then I imagine you are just going to fire that SQL at the database to get a count of all tables. If that is the case, then why do you need to LISTAGG at all, just build a script to do it, ie

SELECT   'SELECT '
                  || ''''
                  || table_name
                  || ''''
                  || ' AS TBL , COUNT(*) as CNT FROM '
                  || 'TGT_MB.'
                  || table_name
                  || ' union all'
FROM     dba_tables

Spool that to a file, trim the last union all and run it as a script.

Or even better...think about what benefit the output gives you. Why does anyone need the exact row count? Querying NUM_ROWS from USER_TABLES is probably sufficient.

user_73672930
Related