在与oracle中的另一个模式进行比较后，如何创建缺失函数的DDL

Question

我想从另一个数据库导入函数/过程，只导入当前数据库中不存在的函数/过程。我用这个得到了对象名称：-

select OBJECT_NAME from dba_objects where owner = 'AKHSYS'  and  object_type in ( 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'PACKAGE BODY' ) minus select OBJECT_NAME from dba_objects  where owner = 'LDHSYS'  and object_type in ( 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'PACKAGE BODY' );

但是，如何为当前模式中缺少的所有函数创建ddl脚本呢？

Answer 1

使用DBMS_METADATA。示例：

SELECT owner, object_type, object_name, DBMS_METADATA.get_ddl (object_type, object_name, owner)
  FROM (SELECT owner, OBJECT_NAME, object_type
          FROM dba_objects
         WHERE owner = USER AND object_type IN ('PROCEDURE', 'PACKAGE', 'FUNCTION'))
ORDER BY 1,2,3