xdb 模式注册报错：ORA-31001

郑全

In this Document

Symptoms

Cause

Solution

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

The following error is seen when registering a schema as below:

SQL> BEGIN

DBMS_XMLSCHEMA.registerSchema(

'http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',

XDBURIType('/source/schemas/poSource/xsd/purchaseOrder.xsd').getClob(),

TRUE,

TRUE,

FALSE,

TRUE);

END;

/

BEGIN

*

ERROR at line 1:

ORA-31001: Invalid resource handle or path name

"/source/schemas/poSource/xsd/purchaseOrder.xsd"

ORA-06512: at "SYS.XDBURITYPE", line 4

ORA-06512: at line 2

The folders for containing the schema document have already been created in the XDB repository:

SQL> declare

res boolean;

begin

res := dbms_xdb.createFolder('/source/');

end;

/

SQL> declare

res boolean;

begin

res := dbms_xdb.createFolder('/source/schemas/');

res := dbms_xdb.createFolder('/source/schemas/poSource/');

res := dbms_xdb.createFolder('/source/schemas/poSource/xsd/');

commit;

end;

/

CAUSE

A resource has NOT been created within the XDB repository for the actual schema document purchaseOrder.xsd.

Hence the registerSchema cannot locate the document, and hence the error:

ORA-31001: Invalid resource handle or path name

When referencing the schema document this way, it must exist where specified.

SOLUTION

To implement the solution execute the following steps:

-- create a directory in Oracle to point to the actual current physical

-- location on your o/s file system where you currently have

-- the purchaseOrder.xsd file located

create or replace directory XMLDIR as '<your_directory_location>';

grant read on directory XMLDIR to public with grant option;

-- create a function to enable the xsd file to be called within a

-- createResource statement

create or replace function getDocument(filename varchar2) return clob

authid current_user is

xbfile bfile;

xclob clob;

destination_offset INTEGER := 1;

source_offset INTEGER := 1;

language_context INTEGER := DBMS_LOB.default_lang_ctx;

warning_message INTEGER;

begin

xbfile := bfilename('XMLDIR',filename);

dbms_lob.open(xbfile);

dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);

DBMS_LOB.LOADCLOBFROMFILE(xclob,xbfile,

dbms_lob.getlength(xbfile),

destination_offset, source_offset,

NLS_CHARSET_ID('UTF8'),

language_context, warning_message);

dbms_lob.close(xbfile);

return xclob;

end;

/

-- create the resource in the XDB repository under folder

-- '/source/schemas/poSource/xsd/' for

-- the schema document purchaseOrder.xsd

declare

bret boolean;

begin

bret :=

dbms_xdb.createresource('/source/schemas/poSource/xsd/purchaseOrder.xsd',

getDocument('purchaseOrder.xsd'));

end;

/

commit;

-- Now create the schema

SQL> BEGIN

DBMS_XMLSCHEMA.registerSchema(

'http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',

XDBURIType('/source/schemas/poSource/xsd/purchaseOrder.xsd').getClob(),

TRUE,

TRUE,

FALSE,

TRUE);

END;

/