提取仓库映射作为创建脚本

Question

有人知道从 Oracle 中提取映射创建脚本的纯 sql 方法吗？以下 SQL 返回正确的结果：

SELECT text FROM dba_source WHERE NAME = 'MAPPING_NAME' AND TYPE = 'PACKAGE';

但为了将其转换为一行，我使用了 http://dotnetsurfers.com/blog/2008/01/16/concatenating-rows-in-a- table-into-a-single-string-using-sql/ 所以现在我有这个：

SELECT SYS_CONNECT_BY_PATH(text, ' ') PackageScript FROM (
  SELECT text, ROW_NUMBER() OVER (order by line) rownumber, COUNT(*) OVER () cnt
  FROM (SELECT text, line FROM dba_source WHERE NAME = 'MAPPING_NAME' AND TYPE = 'PACKAGE' AND substr(text, 0,2) != '--' AND text IS NOT NULL)
) data
WHERE rownumber = cnt
START WITH rownumber = 1
CONNECT BY PRIOR rownumber = rownumber-1;

问题是如果结果超过 4000 个字符，oracle 无法连接字符串。它抛出一个错误：

ORA-01489: result of string concatenation is too long

有更好的方法吗？

Answer 1

下面将提取一个对象的 ddl。

select dbms_metadata.get_ddl('object_type_goes_here','object_name_goes_here','owner_goes_here') 
from dual