使用dbms_metadata.get_ddl出现ORA-31605错误

使用dbms_metadata.get_ddl出现ORA-31605错误

SQL> select* from v$version;

  

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

PL/SQL Release 9.2.0.4.0 - Production

CORE    9.2.0.3.0       Production

TNS forLinux: Version 9.2.0.4.0 - Production

NLSRTL Version 9.2.0.4.0 - Production

  

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')"www.xifenfei.com" from dual;

  

www.xifenfei.com

-------------------

2012-05-26 23:10:22

  

SQL> selectdbms_metadata.get_ddl('TABLE','XFF_IOT','CHF1')from dual;

ERROR:

ORA-06502: PL/SQL:numeric orvalue error

ORA-31605: the following was returnedfrom LpxXSLResetAllVarsin routine kuxslResetParams:

LPX-1:NULL pointer

ORA-06512: at "SYS.UTL_XML", line 0

ORA-06512: at "SYS.DBMS_METADATA_INT", line 3320

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

  

no rows selected

错误原因
dbms_metadata.get_ddl需要调用Oracle dictionary table “sys.metastylesheet.”中的XSL stylesheets,但是由于某种原因,使得调用失败,出现上述错误.因为该错误可能有:
1.XSL stylesheets没有安装；
2.使用alter database 修改数据库字符集(本库是因为昨天修改字符集导致)；

解决办法(sys用户执行)
1.在10g及其以上版本中(不带参数)

SQL> execdbms_metadata_util.load_stylesheets;

  

PL/SQL proceduresuccessfully completed.

2.在9i版本中(带dir参数)

SQL> execdbms_metadata_util.load_stylesheets('/u01/oracle/9.2.0/db_1/rdbms/xml/xsl');

  

PL/SQL proceduresuccessfully completed.

  

SQL> selectdbms_metadata.get_ddl('TABLE','XFF_IOT','CHF1')from dual;

  

DBMS_METADATA.GET_DDL('TABLE','XFF_IOT','CHF1')

--------------------------------------------------------------------------------

  

  CREATETABLE "CHF1"."XFF_IOT"

   (   "ID" NUMBER,

        "NAME"VARCHAR2(30),

         CONSTRAINT"CHF_IOT_ID#_PK" PRIMARYKEY ("ID") ENABLE

   ) ORGANIZATIONINDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

  STORAGE(INITIAL 65536NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOLDEFAULT)

  TABLESPACE"SYSTEM"

 PCTTHRESHOLD 50

  

DBMS_METADATA.GET_DDL('TABLE','XFF_IOT','CHF1')

--------------------------------------------------------------------------------

参考：http://www.xifenfei.com/3239.html