1 2 Previous Next 20 Replies Latest reply: Jul 18, 2008 5:20 PM by mdrake-Oracle Go to original post RSS
      • 15. How do I uninstall XML DB ? How do I re-install XML DB ?
        mdrake-Oracle
        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.
        • 16. How do I load an large XML document into XML DB ?
          mdrake-Oracle
          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
          CREATE OR REPLACE DIRECTORY XMLDIR as 'c:\temp'
          /
          In 10.1.x and later you can then create an XMLTYPE using a BFILE constructor
          XMLTYPE ( BFILENAME ('XMLDIR','filename.xml'),nls_charset_id('AL32UTF8'))
          9.2.x does not support the BFILE constructor so we need a small PL/SQL procedcure that will help...
          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
          --
          We can then call this to create an XMLType using the same parameters as the BFILE based 10.1x constructor

          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'.
          • 17. What is the best way of inserting very large XML documents into XML DB.
            mdrake-Oracle
            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
            • 18. Why do I get ORA-19114 when trying to use XMLTable() or XMLQuery()
              mdrake-Oracle
              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.
              • 20. What are the "XDB Utilities", how do I install them.
                mdrake-Oracle
                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

                http://www.oracle.com/technology/tech/xml/xmldb/index.html

                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 11.1.0.6.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.
                1 2 Previous Next