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

最新推荐文章于 2021-08-27 11:22:13 发布

原创最新推荐文章于 2021-08-27 11:22:13 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

oracle 基础同时被 2 个专栏收录

93 篇文章

订阅专栏

ora-XXX

29 篇文章

订阅专栏

当尝试使用dbms_metadata.get_ddl获取DDL时，遇到ORA-31605错误，可能是由于XSL stylesheets未安装或数据库字符集更改导致。解决方案包括在sys用户下执行特定版本的修复步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用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 
        for 
        Linux: 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> 
        select 
        dbms_metadata.get_ddl(
        'TABLE'
        ,
        'XFF_IOT'
        ,
        'CHF1'
        ) 
        from 
        dual;  
       
        ERROR: 
       
        ORA-06502: PL/SQL: 
        numeric 
        or 
        value error 
       
        ORA-31605: the following was returned from LpxXSLResetAllVars in 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> 
        exec 
        dbms_metadata_util.load_stylesheets; 
       
        PL/SQL 
        procedure 
        successfully completed.

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

 
        SQL> 
        exec 
        dbms_metadata_util.load_stylesheets(
        '/u01/oracle/9.2.0/db_1/rdbms/xml/xsl'
        ); 
       
        PL/SQL 
        procedure 
        successfully completed. 
       
        SQL> 
        select 
        dbms_metadata.get_ddl(
        'TABLE'
        ,
        'XFF_IOT'
        ,
        'CHF1'
        ) 
        from 
        dual;  
       
        DBMS_METADATA.GET_DDL(
        'TABLE'
        ,
        'XFF_IOT'
        ,
        'CHF1'
        ) 
       
        -------------------------------------------------------------------------------- 
       
        CREATE 
        TABLE 
        "CHF1"
        .
        "XFF_IOT"
       
        (    
        "ID" 
        NUMBER,  
       
        "NAME" 
        VARCHAR2(30), 
       
        CONSTRAINT 
        "CHF_IOT_ID#_PK" 
        PRIMARY 
        KEY 
        (
        "ID"
        ) ENABLE 
       
        ) ORGANIZATION 
        INDEX 
        NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING 
       
        STORAGE(INITIAL 65536 
        NEXT 
        1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
       
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL 
        DEFAULT
        ) 
       
        TABLESPACE 
        "SYSTEM"
       
        PCTTHRESHOLD 50 
       
        DBMS_METADATA.GET_DDL(
        'TABLE'
        ,
        'XFF_IOT'
        ,
        'CHF1'
        ) 
       
        --------------------------------------------------------------------------------

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