英文:
"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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论