“ORA-01489: 字符串连接的结果过长” 与 LISTAGG

huangapple go评论118阅读模式
英文:

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

问题

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

英文:

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

答案1

得分: 1

这明显行不通。也许你应该重新考虑你正在做的事情,而不是聚合过程的文本,而是使用设计用于生成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');

或者你可以做你想做的任何事情。

英文:

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.

huangapple
  • 本文由 发表于 2023年4月19日 18:04:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/76053225.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定