"ORA-01489: the result of string concatenation is too long" with LISTAGG

Question

I want to get the DDL of all functions and procedures from the sys table ALL_SOURCE. here is the code:

  SELECT OWNER,
         NAME,
         TYPE,
         LISTAGG (TEXT, '') WITHIN GROUP (ORDER BY LINE)     TEXT
    FROM ALL_SOURCE WHERE OWNER = 'ITMS'
GROUP BY OWNER,
         NAME,
         TYPE

But when the script is larger than 4000 characters, I got the error: ORA-01489: the result of string concatenation is too long

Help me with that

Thanks

Answer 1

That won't work, obviously. But - perhaps you should reconsider what you're doing and - instead of aggregating procedure's text, use tool which is designed to produce DDL: DBMS_METADATA.GET_DDL

For example:

SQL> select dbms_metadata.get_ddl('PROCEDURE', 'P_EMPTY') ddl from dual;

DDL
--------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "SCOTT"."P_EMPTY" (par_table_name IN VARCHAR2)
IS
   l_str  VARCHAR2 (200);
BEGIN
   FOR cur_r IN (SELECT column_name
                   FROM user_tab_columns
                  WHERE table_name = DBMS_ASSERT.sql_object_name (par_table_name
))
   LOOP
      l_str :=
            'update '
         || par_table_name
         || ' set '
         || cur_r.column_name
         || q'[ = 'EMPTY' where ]'
         || cur_r.column_name
         || ' is null';

dbms_output.put_line(l_str);
      EXECUTE IMMEDIATE l_str;
   END LOOP;
END;


SQL>

You can modify it so that it (query) fetches all procedures and functions, e.g.

SELECT DBMS_METADATA.get_ddl ('PROCEDURE', object_name) ddl
  FROM user_objects
 WHERE object_type IN ('FUNCTION', 'PROCEDURE');

or do whatever you want.