DBMS_METADATA.GET_DDL 错误解决
Oracle 9i 开始，提供了 DBMS_METADATA 包，可以方便的获得对象的 DDL 语句。
FUNCTION GET_DDL RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME VARCHAR2 IN
SCHEMA VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT
今天，使用 GET_DDL 时，出现了问题：
ERROR:
ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'n'
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3296
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1
自从上次把 Oracle 升级到 9.2.0.6 感觉数字字典一直有问题，google 了一下，发现 Fenng 的一篇文章，上面提到了是个 BUG，不过再仔细一看，发现错误并不相同。于是继续 google，终于找到了解决方法：Reload the XML API。
步骤：
2. run:
(from $ORACLE_HOME/rdbms/admin):
catnomet.sql
rmxml.sql
to remove the xml subsystem and after that:
catxml
utlcxml.sql
prvtcxml.plb
catmet.sql
to reload it.
问题解决：
DBMS_METADATA.GET_DDL('TABLE',
-----------------------------------------------------------------------
CREATE TABLE "SKY"."A"
( "C" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EXAMPLE"
另外，从 Fenng 那篇文章还知道了，屏蔽不需要的 DDL 子句：
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'
STORAGE',false);
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl('TABLE','A') from dual;
DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
CREATE TABLE "SKY"."A"
( "C" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "EXAMPLE"
参考：
http://www.dbanotes.net/Oracle/DBMS_METADATA_DDL.htm
http://www.mydatabasesupport.com/forums/showthread.php?t=235
Posted by Sky at 04:26 PM | Permalink | comments(0) | Edit | Database