oracle “ORA-01489:字符串连接的结果太长”,带有LISTAGG [重复]

pftdvrlh  于 2023-04-20  发布在  Oracle
关注(0)|答案(1)|浏览(346)

此问题已在此处有答案

LISTAGG function: "result of string concatenation is too long"(14个答案)
11小时前关闭
我想从sys表ALL_SOURCE中获取所有函数和过程的DDL。下面是代码:

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

但是当脚本大于4000个字符时,我得到了错误:ORA-01489:字符串串联的结果太长
帮帮我
谢谢

bxjv4tth

bxjv4tth1#

显然,这是行不通的。但是-也许你应该重新考虑一下你在做什么-而不是 * 聚合 * 过程的文本,使用旨在产生DDL的工具:DBMS_METADATA.GET_DDL
例如:

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>

您可以修改它,使它(查询)获取所有过程和函数,例如。

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

或者做任何你想做的事

相关问题