DBMS_METADATE.GET_DDL使用(二)

SPOOL spool/procedure.sql

FROM user_objects u WHERE object_type='PROCEDURE';

SPOOL OFF;

获取某个SCHEMA的建全部函数的语法：

SET PAGESIZE 1000

SET LINESIZE 120

SET NEWPAGE 0

SET ECHO OFF

SPOOL spool/function.sql

SELECT DBMS_METADATA.GET_DDL('FUNCTION',u.object_name)

FROM user_objects u WHERE object_type='FUNCTION';

SPOOL OFF;

执行DBMS_METADATA.GET_DDL报ORA-39212的解决方法

以sys登陆，执行DBMS_METADATA.get_ddl得到某个表空间的元数据报错
select DBMS_METADATA.get_ddl('TABLESPACE','PDA') from dual;
ERROR:
ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7428
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7477
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9495
ORA-06512: at "SYS.DBMS_METADATA", line 1920
ORA-06512: at "SYS.DBMS_METADATA", line 2793

.........

解决方法：
以sysdba权限的用户登陆执行以下语句：

SQL> exec dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

再次执行成功：

SQL> selectDBMS_METADATA.get_ddl('TABLESPACE','PDA') from dual;