This content has been marked as final. Show 20 replies
To uninstall XML DB run the sql script $ORACLE_HOME/rdbms/admin/catnoqm.
Note that running this script will
1. DELETE ALL INFORMATION STORED IN THE ORACLE XML DB REPOSITORY AND XDB DATABASE SCHEMA
2. PERMANENTLY INVALIDATE ANY XMLTYPE TABLES OR COLUMNS THAT ARE ASSOCIATED WITH A REGISTERED XML SCHEMA. IF THE XDB USER IS DROPPED THERE IS NO WAY TO RECOVER THE DATA IN THESE XMLTYPE TABLES OR COLUMNS.
Please do not uninstall XML DB in a production database without explicit instructions from Oracle Support to do so.
To re-install XDB, after running catnoqm.sql, stop and restart the instance and then run catqm as described in the previous post.
The easiest way to do this is to place the file in a directory on the file system of the machine that is running the database and then using a bfile to load access the contents of the file.
The first step is to create a SQL Directory object that points at the operating system folder containing the file
In 10.1.x and later you can then create an XMLTYPE using a BFILE constructor
CREATE OR REPLACE DIRECTORY XMLDIR as 'c:\temp' /
9.2.x does not support the BFILE constructor so we need a small PL/SQL procedcure that will help...
XMLTYPE ( BFILENAME ('XMLDIR','filename.xml'),nls_charset_id('AL32UTF8'))
We can then call this to create an XMLType using the same parameters as the BFILE based 10.1x constructor
create or replace function getXML(file bfile,charset number) return XMLTYPE is XML xmltype; tempCLOB CLOB; tempFile BFILE; dest_offset number := 1; src_offset number := 1; lang_context number := 0; conv_warning number := 0; begin tempFile := file; DBMS_LOB.createTemporary(tempCLOB,true,DBMS_LOB.SESSION); DBMS_LOB.fileopen(tempFile, DBMS_LOB.file_readonly); DBMS_LOB.loadClobfromFile ( tempCLOB, tempFile, DBMS_LOB.getLength(file), dest_offset, src_offset, charset, lang_context, conv_warning ); DBMS_LOB.fileclose(tempFile); XML := xmltype(tempCLOB); dbms_lob.freeTemporary(tempCLOB); return XML; end; / show errors --
To create the SQL Directory you must have 'CREATE ANY DIRECTORY' role. The values passed to the NLS_CHARSET_ID is the Oracle name for the character set eqivilaint to the the encoding of the file in, eg XML 'UTF-8' encoding is oracle 'AL32UTF8'.
One good way of doing this, particularly in the case of a document that is really a large collection of smaller documents, encapulated by a single root element is to insert the collection as a set of smaller documnts. The SaxLoader example shows one good way of doing this...
Sax Loader example
IN 10.2.0.1.0 and 10.2.0.2.0 the following error can occur when attempting to use XMLTable or XMLQuery.
ORA-19114: error during parsing the XQuery expression:
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
This is caused by the fact in these releases the XQuery parser (The code that parses the XQuery expression) is implemented in Java. If the database JVM is not installed the parser is not installed and the XMLTable and XMLQuery operators cannot be used.
In 10.2.0.3.0 the Java based XQuery parser was replaced with a 'C' based parser the requirement to have the database JVM installed in order to parse the XQuery statement is removed.
Note that we are discussing the parser for the XQuery statements here, not the the parser for the XML Documents.
Also, even in 10.2.0.3.0, in cases where the XQuery statement cannot be sucessfully be re-written into an equivilant relational operation, Java will be used to execute the statement. If the JVM is not present statements which cannot be re-written will not be executed.
The XDB Utiltities are a set of PL/SQL packages that provide helpers for common tasks associated with XML DB. Theses packages are not supported by Oracle. The can be downloaded from the XML DB page on OTN
Installing the XDB Utilities will create a locked database acount called XDBPM. This user is granted limited access to some dictionary tables. Installed XDBPM will aslo add an XDBPM_HELPER package to the XDB account and allow the XDBPM user to invoke it. Re-installing the XDB Utilities package will drop and re-create the XDBPM user, as well as drop and re-create the XDB_HELPER package. The XDB Utilities require that XDBPM_HELPER package be installed.
To Install XDB Utilities run the following command
sqlplus / as sysdba @xdbSupport...
The current version of xdb_utilities supports database 220.127.116.11.0 and later. The zip also contains an earlier version of the software desgined for database 10.2.0.0.0. This can be installed by running the script xdbSupport_10200, rather than XDB Support.
Please post any questions related to use of these packages here...
The "Offical" XDB Utilities thread.