13 Replies Latest reply on May 5, 2006 8:44 PM by mdrake-Oracle

    How to export or backup an XML DB repository?

    312365
      <p>All,</p>

      <p>I am alarmed by these statements in the Oracle Database 10gR2 documentation regarding database exports/imports:
      Oracle® XML DB Developer's Guide 10g Release 2 (10.2), Chapter 30

      <p>--8<--</p>
      <p>Resources and Foldering Do Not Fully Support IMPORT/EXPORT</p>

      <p>Oracle XML DB supports a foldering mechanism that uses path names and URIs to refer to data (repository resources), rather than table names, column names, and so on. This foldering mechanism is not entirely supported using IMPORT/EXPORT.</p>

      <p>However, for resources based on a registered XML schema, the XMLType tables storing the data can be imported and exported. During export, only the XML data is exported; the relationship in the Oracle XML DB foldering hierarchy is lost.</p>

      <p>Repository Metadata is Not Exported During a Full Database Export</p>

      <p>Oracle XML DB stores the metadata (and data unrelated to XML Schema) for Oracle XML DB Repository in the XDB database-user schema. Because Oracle Database does not support the export of the repository structure, these metadata tables and structures are not exported during a full database export.</p>

      <p>The entire XDB user schema is skipped during a full database export and any database objects owned by XDB are not exported.</p>
      <p>--8<--</p>

      <p>We have a large application that is using the resource view / repository almost exclusively. Although we alwsy use registered schemas, our data access is exclusively through the resource_view so it is imperitive that we are able to backup the database and preserve all data and metadata in the resource_view.</p>

      <p>What strategies are there for handling this situation?
      <ol>
      <li>Export the datafiles manually using ftp/webdav?</li>
      <li>Backup the disk files?</li>
      <li>Use the new Oracle Data Pump Export tool?</li>
      </ol>
      </p>

      <p>Any help / insight into this problem would be deeply appreciated. It surely can't be the case that Oracle does not have a strategy for handling this situation.</p>

      <p>Thanks,
      Marcus</p>
        • 1. Re: How to export or backup an XML DB repository?
          312365
          <p>Additional comments from the Oracle® Database Utilities 10g Release 2 (10.2) guide include the following:</p>
          <p>---8<---</p>
          <p>Oracle recommends that you use the new Data Pump Export and Import utilities because they support all Oracle Database 10g features, except for XML schemas and XML schema-based tables. Original Export and Import support the full set of Oracle database release 9.2 features.</p>
          <p>---8<---</p>
          <p>This appears to rule out the Data Pump Export strategy.</p>
          • 2. Re: How to export or backup an XML DB repository?
            mdrake-Oracle
            RMAN backup of the database ?

            I am also working on a Java based utilitiy to assist in this area. To date it handles non schema based XML Content and Non Schema based data. I've not had a chance to extend it to schema based data or thought about how it would handle extended metadata. I'm still thinking about how to handle Schema based XML. There are two approaches, 1 use conventional import/export to be the XML Schema based data between the repositories and have the Java Utilities simple move the metadata (probably more efficienct but has the problem that you have to make sure there are no updates going on until the export and the java utility have completed. (2) Move the contents of the schema based XML as part of the Java Utilitiy and bypass import/export entirely. (Probably slower). The utilitiy generates ZIP files so I also suspect I may hit a 4GB limit which will necessate generating a set of ZIP files.

            I can post the code I have to date if anyone's interested in contribuiting to this effort...
            • 3. Re: How to export or backup an XML DB repository?
              Marco Gralike
              I think RMAN is the only way to go (for now), but then again export / import methods are NOT real database backup methods anyway ;-). Is database flashback an option...?
              • 4. Re: How to export or backup an XML DB repository?
                312365
                Thanks for all the input. RMAN is indeed the way forward on this.
                • 5. Re: How to export or backup an XML DB repository?
                  149271
                  Hi , that java software sound interesting , I'm not sure if you can post the files here .
                  If not , please send them to my email at agonen_il@yahoo.com

                  thanks.
                  • 6. Re: How to export or backup an XML DB repository?
                    mdrake-Oracle
                    3 Java Classes and a PL/SQL Package...

                    Exporter.java
                    Importer.java
                    Export/Import.java

                    and

                    xdbImportUtilities.java

                    Each posted seperately below
                    • 7. Re: How to export or backup an XML DB repository?
                      mdrake-Oracle
                      package com.oracle.st.xmldb.pm.export;
                      
                      
                      import java.io.ByteArrayInputStream;
                      import java.io.IOException;
                      import java.io.InputStream;
                      import java.io.OutputStream;
                      import java.sql.SQLException;
                      
                      import java.util.Enumeration;
                      import java.util.Hashtable;
                      import java.util.zip.ZipEntry;
                      import java.util.Vector;
                      import java.util.zip.ZipException;
                      import java.util.zip.ZipOutputStream;
                      import oracle.jdbc.OracleCallableStatement;
                      import oracle.jdbc.OracleConnection;
                      import oracle.jdbc.OraclePreparedStatement;
                      import oracle.jdbc.OracleResultSet;
                      import oracle.sql.BLOB;
                      import oracle.sql.CLOB;
                      
                      
                      import oracle.xml.parser.v2.DOMParser;
                      import oracle.xml.parser.v2.XMLDocument;
                      import oracle.xml.parser.v2.XMLElement;
                      import org.w3c.dom.Element;
                      import org.w3c.dom.NodeList;
                      
                      import org.xml.sax.SAXException;
                      
                      public class Exporter
                      {
                        public static boolean DEBUG = true;
                      
                        public static String RESOURCE_NAMESPACE         = "http://xmlns.oracle.com/xdb/XDBResource.xsd";
                        public static String BINARY_CONTENT             = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary";
                        public static String TEXT_CONTENT               = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#text";
                        public static String VERSION_ID_ELEMENT         = "VCRUID";
                      
                        public static String LOOKUP_ROOT =
                        "select p.resid " +
                        "      ,p.path " + 
                        "      ,extractValue(p.res,'/Resource/@Container') " +
                        "      ,(select any_path from resource_view a where make_ref(xdb.xdb$ACL,extractValue(p.res,'/Resource/ACLOID')) = extractValue(a.res,'/Resource/XMLRef')) " + 
                        "      ,p.res.getClobVal()" +
                        "  from path_view p " +
                        " where equals_path(p.res,1,:1) = 1";
                      
                        public static String LOOKUP_RESOURCE =
                        "select p.resid " +
                        "      ,p.path " + 
                        "      ,extractValue(p.res,'/Resource/@Container') " +
                        "      ,(select any_path from resource_view a where make_ref(xdb.xdb$ACL,extractValue(p.res,'/Resource/ACLOID')) = extractValue(a.res,'/Resource/XMLRef')) " + 
                        "      ,p.res.getClobVal() " +
                        "  from path_view p " +
                        " where equals_path(p.res,:1) = 1";
                      
                        public static String LIST_FOLDER_CONTENTS =
                        "select p.resid" +
                        "      ,p.path" +
                        "      ,extractValue(p.res,'/Resource/@Container')" +
                        "      ,(select any_path from resource_view a where make_ref(xdb.xdb$ACL,extractValue(p.res,'/Resource/ACLOID')) = extractValue(a.res,'/Resource/XMLRef')) "+
                        "      ,p.res.getClobVal()" + 
                        "  from path_view p" +
                        " where under_path(p.res,1,:1) = 1";
                        
                        public static String LOOKUP_ACL =
                        "select p.resid " +
                        "      ,p.path " + 
                        "      ,'false'  " +
                        "      ,(select any_path from resource_view a where make_ref(xdb.xdb$ACL,extractValue(p.res,'/Resource/ACLOID')) = extractValue(a.res,'/Resource/XMLRef')) "+
                        "      ,p.res.getClobVal()" +
                        "  from path_view p " +
                        " where equals_path(p.res,:1) = 1";
                      
                        static String GET_VERSION_HISTORY_SQL =
                        "select RESOID, r.RES.getClobVal(), extractValue(Res,'/Resource/@VersionID') VERSION " +
                        "  from ( "+
                        "         select RESID.COLUMN_VALUE RESOID, " +
                        "                DBMS_XDB_VERSION.GETRESOURCEBYRESID(RESID.COLUMN_VALUE) RES " +
                        "           from table(xdb_utilities.getVersionsByResid(hexToRaw(:1))) RESID  " +
                        "       ) r " +
                        // " where extractValue(Res,'/Resource/@VersionID') != 0 " +
                        " order by version";
                      
                        public static String LOOKUP_CONTENT = 
                        "select resid, xdburitype(dbms_xdb.createOidPath(resid)).getBlob() " +
                        "  from resource_view " +
                        " where under_path(res,:1) = 1 " +
                        "   and extractValue(res,'/Resource/XMLLob') is not null"; 
                      
                        public static String FETCH_CONTENT = 
                        "select xdburitype(dbms_xdb.createOidPath(hexToRaw(:1))).getBlob() " +
                        "  from dual";
                      
                        public static String LOOKUP_PATHS = 
                        "select path from path_view where resid = hexToRaw(:1)";
                      
                        public static String SET_PRINT_MODE = 
                        "begin dbms_xdbutil_int.setPrintMode(:1); end;";      
                        
                        private String targetFolder;
                      
                        private ZipOutputStream zos; 
                        
                        private Hashtable aclList;
                        private Hashtable versionedResourceList;
                        
                        private OracleConnection databaseConnection;
                      
                        private OraclePreparedStatement listFolderContents;
                        private OraclePreparedStatement lookupACL;
                        private OraclePreparedStatement lookupVersions;
                        private OraclePreparedStatement lookupPaths;
                        private OraclePreparedStatement fetchContent;
                        
                        public Exporter(OracleConnection connection)
                        throws SAXException, IOException, SQLException
                        {        
                          this.databaseConnection = connection;
                          this.databaseConnection.setAutoCommit(false);
                        }
                      
                        private void setPrintMode(int mode)
                        throws SQLException
                        {
                          OracleCallableStatement setPrintMode =  (OracleCallableStatement) databaseConnection.prepareCall(SET_PRINT_MODE);
                          setPrintMode.setInt(1,mode);
                          setPrintMode.executeQuery();
                          setPrintMode.close();
                        }
                      
                        public void exportFolderTree(String path,OutputStream os)
                        throws SQLException, IOException, SAXException
                        {
                          this.listFolderContents =  (OraclePreparedStatement) this.databaseConnection.prepareStatement(this.LIST_FOLDER_CONTENTS);
                          this.lookupACL = (OraclePreparedStatement) this.databaseConnection.prepareStatement(this.LOOKUP_ACL);
                          this.lookupVersions = (OraclePreparedStatement) this.databaseConnection.prepareStatement(this.GET_VERSION_HISTORY_SQL);
                          this.lookupPaths = (OraclePreparedStatement) this.databaseConnection.prepareStatement(this.LOOKUP_PATHS);
                          this.fetchContent = (OraclePreparedStatement) this.databaseConnection.prepareStatement(this.FETCH_CONTENT);   
                      
                          this.setPrintMode(1);
                      
                          this.aclList = new Hashtable();
                          this.versionedResourceList = new Hashtable();
                      
                          aclList.put("/sys/acls/all_all_acl.xml","");
                          aclList.put("/sys/acls/ro_all_acl.xml","");
                          aclList.put("/sys/acls/bootstrap_acl.xml","");
                          aclList.put("/sys/acls/all_owner_acl.xml","");
                      
                          this.targetFolder = path;
                          this.zos = new ZipOutputStream(os);
                      
                          zipFolder(targetFolder);
                         
                          this.setPrintMode(0);
                          zipContent();
                      
                          this.zos.flush();
                          this.zos.close();
                          this.listFolderContents.close();
                          this.lookupACL.close();
                          this.lookupVersions.close();
                          this.lookupPaths.close();
                          this.fetchContent.close();
                        }
                        
                        private void zipFolder(String path) 
                        throws IOException, SQLException, SAXException
                        {
                            this.listFolderContents.setString(1,path);
                            OracleResultSet resultSet = (OracleResultSet) this.listFolderContents.executeQuery();
                            
                            // First Zip all the Files and build a list of all Folders that are immediate Children of the current Folder
                            // This approach reduces the number of open cursors required to complete the operation since we can close 
                            // the resultSet for the current folder before processing it's subfolders.
                            
                            Hashtable folderList = zipResultSet(resultSet);
                             
                            // Next Zip the contents of any subFolders that were identified 
                            
                            Enumeration subFolders = folderList.keys();
                            while (subFolders.hasMoreElements())
                            {
                               String subFolder = (String) subFolders.nextElement();
                               zipFolder(subFolder, (XMLDocument) folderList.get(subFolder));
                            }
                              
                        }
                        
                        private void zipFolder(String path, XMLDocument resource)
                        throws IOException, SQLException, SAXException
                        {
                        
                          // Zip the contents of the Specified Folder and all it's subfolders;
                          String resourceID = resource.getFirstChild().getNodeValue();
                      
                          ZipEntry zipEntry = new ZipEntry(getRelativePath(path + "/"));
                          System.out.println("Folder = " + zipEntry.getName() + ". ResourceID = " + resourceID);
                          this.zos.putNextEntry(zipEntry);
                          byte[] buffer = resourceID.getBytes();
                          zos.write(buffer,0,buffer.length);
                          zos.flush();
                          zos.closeEntry();
                          
                          zipFolder(path);
                          
                          zipResourceEntry(getRelativePath(path),resource);
                        }
                      
                        private void zipACL(String path)
                        throws SAXException, SQLException, IOException  
                        {
                          this.aclList.put(path,"");
                          this.lookupACL.setString(1,path);
                          this.setPrintMode(0);
                          OracleResultSet resultSet = (OracleResultSet) this.lookupACL.executeQuery();
                          this.setPrintMode(1);
                          this.zipResultSet(resultSet);
                        }
                        
                        private Hashtable zipResultSet(OracleResultSet resultSet)
                        throws SAXException, SQLException, IOException
                        {
                          // Zip each resource found in the folder.
                          Hashtable folderList = new Hashtable();
                          while (resultSet.next())
                          {
                            zipResource(resultSet,folderList);
                          }
                          resultSet.close();    
                          return folderList;
                        }
                      
                        private Hashtable zipResource(OracleResultSet resultSet, Hashtable folderList)
                        throws SAXException, SQLException, IOException
                        {
                          String resourceID = resultSet.getRAW(1).stringValue();
                          String path = resultSet.getString(2);
                          XMLDocument resource = getResource(resultSet);
                      
                          XMLElement root = (XMLElement) resource.getDocumentElement();
                          NodeList nl = root.getChildrenByTagName(this.VERSION_ID_ELEMENT,this.RESOURCE_NAMESPACE);
                          boolean isVersioned = nl.getLength() > 0;
                      
                          boolean isFolder = resultSet.getString(3).equalsIgnoreCase("TRUE");
                          if (isFolder)
                          {
                             folderList.put(path,resource);
                          }
                          else
                          {
                            if (isVersioned)
                            {
                              // Create a folder which will contain one entry for each Version of the Resource
                              zipVersions(path,resourceID,resource,zos);
                            }
                            else
                            {
                              System.out.print("File = ");
                              zipResourceEntry(getRelativePath(path),resource);
                            }
                          }
                          return folderList;
                        }
                      
                        private XMLDocument getResource(OracleResultSet resultSet)
                        throws SQLException, SAXException,IOException 
                        {
                           String resourceID = resultSet.getString(1);
                           String aclPath = resultSet.getString(4);
                           if (!this.aclList.containsKey(aclPath))
                           {
                             this.zipACL(aclPath);
                           }
                           
                           CLOB clob = resultSet.getCLOB(5);
                           DOMParser parser = new DOMParser();
                           parser.parse(clob.getCharacterStream(0));
                           clob.freeTemporary();
                           XMLDocument resource = parser.getDocument();
                           XMLElement root = (XMLElement) resource.getDocumentElement();
                      
                           NodeList nl = root.getChildrenByTagName(this.VERSION_ID_ELEMENT,this.RESOURCE_NAMESPACE);
                           boolean isVersioned = nl.getLength() > 0;
                      
                           root.getParentNode().insertBefore(resource.createProcessingInstruction("Resid",resourceID),root);
                           root.getParentNode().insertBefore(resource.createProcessingInstruction("AclPath",aclPath),root);
                           Element element = (Element) root.getChildrenByTagName("RefCount").item(0);
                           root.removeChild(element);
                           element = (Element) root.getChildrenByTagName("ACL").item(0);
                           root.removeChild(element);
                           return resource;
                        }
                        private void zipResourceEntry(String path, XMLDocument resource) 
                        throws IOException 
                        {
                          // Create a ZipEntry for the Resource document associated with the File or Folder    
                          ZipEntry zipEntry = new ZipEntry(path);
                          System.out.println(zipEntry.getName());
                          this.zos.putNextEntry(zipEntry);
                          resource.print(this.zos);
                          resource.freeNode();
                          zos.flush();
                          zos.closeEntry();
                        }
                        
                        private void zipVersions(String path, String resourceID, XMLDocument resource, ZipOutputStream zos) 
                        throws IOException, SQLException, SAXException
                        {
                        
                          // Check if the VersionSeries has already been processed
                          
                          if (versionedResourceList.containsKey(resourceID)) 
                          {
                            // Create an entry that will create a Link to the Versioned Document
                            System.out.print("Version Link = ");
                            zipResourceEntry(getRelativePath(path),resource);
                          }
                          else 
                          {
                            // Create a Zip Archive folder object that will contain each of the Versioned Resources 
                            ZipEntry zipEntry = new ZipEntry(getRelativePath(path + "/"));
                            System.out.println("VersionSeries = " + zipEntry.getName());
                            this.zos.putNextEntry(zipEntry);
                            zos.closeEntry();
                      
                            // Write the versioned documents into the Zip Archive. Order is Oldest ==> Current
                      
                            lookupVersions.setString(1,resourceID);
                            OracleResultSet resultSet = (OracleResultSet) lookupVersions.executeQuery();
                            while (resultSet.next())
                            { 
                              String resid = resultSet.getRAW(1).stringValue();
                              if (!resid.equalsIgnoreCase(resourceID))
                              { 
                                CLOB clob = resultSet.getCLOB(2);
                                DOMParser parser = new DOMParser();
                                parser.parse(clob.getCharacterStream(0));
                                clob.freeTemporary();
                      
                                XMLDocument previousVersion = parser.getDocument();
                                XMLElement root = (XMLElement) previousVersion.getDocumentElement();
                                root.getParentNode().insertBefore(previousVersion.createProcessingInstruction("Resid",resid),root);
                                // root.getParentNode().insertBefore(resource.createProcessingInstruction("AclPath",aclPath),root);
                                Element element = (Element) root.getChildrenByTagName("RefCount").item(0);
                                root.removeChild(element);
                                NodeList nl =  root.getChildrenByTagName("Lock");
                                if (nl.getLength() > 0)  
                                {
                                    element = (XMLElement) nl.item(0);
                                    root.removeChild(element);
                                }
                                // 
                                element = (Element) root.getChildrenByTagName("ACL").item(0);
                                root.removeChild(element);
                      
                                System.out.print("Previous = ");
                                zipResourceEntry(getRelativePath(path) + '/' + resid,previousVersion);
                      
                                zipEntry = new ZipEntry("/sys/oid" + resid);
                                fetchContent.setString(1,resid);
                                OracleResultSet content = (OracleResultSet) fetchContent.executeQuery();
                                while (content.next()) 
                                {
                                  BLOB versionedContent = content.getBLOB(1);
                                  zipBLOB(resid,versionedContent,zos);
                                }
                                content.close();
                              }
                            }
                      
                            resultSet.close();
                       
                            // Write the resource for current version to the Archive.. 
                            // Check that the current resource has not already been processed.
                            // This situation occurs when the document has been checked-out but not yet updated.
                         
                             zipEntry = new ZipEntry(getRelativePath(path) + '/' + resourceID);
                             System.out.println("Current = " + zipEntry.getName());
                             this.zos.putNextEntry(zipEntry);
                             resource.print(this.zos);
                             resource.freeNode();
                             zos.flush();
                             zos.closeEntry();
                             
                             this.versionedResourceList.put(resourceID,resourceID);
                          }
                        }
                        
                        private void zipContent()
                        throws IOException, SQLException
                        {
                          // Process Content of all Current Versions of the selected Resources
                          
                          System.out.println("Processing Non Versioned Content");  
                          OraclePreparedStatement lookupContent = (OraclePreparedStatement) this.databaseConnection.prepareStatement(this.LOOKUP_CONTENT);
                          lookupContent.setString(1,this.targetFolder);
                          OracleResultSet resultSet = (OracleResultSet) lookupContent.executeQuery();
                          while (resultSet.next())
                          {
                            String resid = resultSet.getRAW(1).stringValue();
                            BLOB content = resultSet.getBLOB(2);
                            zipBLOB(resid,content,zos);          
                          }
                          resultSet.close();
                          lookupContent.close();    
                        }
                      
                        private String getRelativePath(String path)
                        {
                          if (path.startsWith(this.targetFolder))
                          {
                            path = path.substring(this.targetFolder.lastIndexOf("/")+1);
                          }
                          return path;
                        }
                      
                        public int zipBLOB(String resid, BLOB content, ZipOutputStream zos) 
                        throws IOException, SQLException 
                        {
                          try
                          {
                            int bytesWritten = 0;
                            System.out.print("Process Resource : "  + resid);
                            if (content != null)
                            {
                              ZipEntry zipEntry = new ZipEntry("/sys/oid/" + resid);
                              zos.putNextEntry(zipEntry);
                      
                              InputStream is = content.getBinaryStream();
                              byte[] buffer = new byte[BLOB.MAX_CHUNK_SIZE];
                              int n;
                              while (-1 != (n = is.read(buffer,0,buffer.length)))
                              {
                                zos.write(buffer,0,n);
                                bytesWritten = bytesWritten + n;
                              }
                              zos.flush();
                              zos.closeEntry();   
                              content.freeTemporary();
                            }
                            System.out.println(". Bytes written : "  + bytesWritten);
                            return bytesWritten;
                          }
                          catch (ZipException ze) 
                          {
                              content.freeTemporary();
                              logZipException(resid,ze); 
                          }
                          return -1;
                        }
                        
                        public void logZipException(String resid, ZipException ze)
                        throws SQLException
                        {
                          System.out.println();
                          System.out.println("Zip Error occured while processing resource " + resid);
                          lookupPaths.setString(1,resid);
                          OracleResultSet resultSet = (OracleResultSet) lookupPaths.executeQuery();
                          while(resultSet.next()) {
                              String path = resultSet.getString(1);
                              System.out.println("  " + path );
                          }
                          ze.printStackTrace();
                        }
                      }
                      • 8. Re: How to export or backup an XML DB repository?
                        mdrake-Oracle
                        package com.oracle.st.xmldb.pm.export;
                        
                        import com.oracle.st.xmldb.pm.common.baseApp.BaseApplication;
                        
                        import java.io.File;
                        import java.io.FileInputStream;
                        import java.io.FileOutputStream;
                        import java.io.IOException;
                        import java.sql.SQLException;
                        
                        import oracle.xml.parser.v2.XMLDocument;
                        
                        import org.xml.sax.SAXException;
                        public class ExportImport extends BaseApplication
                        {
                        
                          public static final String MODE = "Mode";
                          public static final String ZIPFILE = "ZipFileName";
                          public static final String TARGET_FOLDER = "TargetFolder";
                          public static final String EXPORT_MODE = "EXPORT";
                          
                          public void doSomething( String [] args ) throws Exception
                          {
                            try {
                              String operation = this.getSetting(this.MODE,"export");
                              String filename = this.getSetting(this.ZIPFILE,"repository.zip");
                              String target = this.getSetting(this.TARGET_FOLDER,"/");
                              String onDuplicate = this.getSetting(this.TARGET_FOLDER,"/");
                              
                              if (operation.equalsIgnoreCase(this.EXPORT_MODE) )
                              {
                                Exporter exporter = new Exporter(this.getConnection()); 
                                File file = new File(filename);
                                if (file.exists()) {
                                    file.delete();
                                }
                                FileOutputStream fos = new FileOutputStream(filename);
                                exporter.exportFolderTree(target,fos);
                              }
                              else
                              {
                                Importer importer = new Importer(this.getConnection()); 
                                FileInputStream fis = new FileInputStream(filename);
                                importer.importFolderTree(target,fis,onDuplicate);
                              }
                            }
                            catch (IOException ioe)
                            {
                              ioe.printStackTrace();
                            }
                            catch (SAXException saxe)
                            {
                              saxe.printStackTrace();
                            }
                            catch (SQLException sqle)
                            {
                              sqle.printStackTrace();
                            }
                          }  
                        
                            public static void main (String[] args)
                            {
                              try 
                              {
                               ExportImport export = new ExportImport();
                                export.initializeConnection();
                                export.doSomething(args);
                              }
                              catch (Exception e)
                              {
                                e.printStackTrace();
                              }
                            }
                        }
                        • 9. Re: How to export or backup an XML DB repository?
                          mdrake-Oracle
                          package com.oracle.st.xmldb.pm.export;
                          import java.io.IOException;
                          import java.io.InputStream;
                          import java.io.InputStreamReader;
                          import java.io.OutputStream;
                          import java.io.OutputStreamWriter;
                          import java.io.Reader;
                          import java.io.Writer;
                          import java.sql.SQLException;
                          import java.util.zip.ZipEntry;
                          import java.util.zip.ZipInputStream;
                          import oracle.jdbc.OracleCallableStatement;
                          import oracle.jdbc.OracleConnection;
                          import oracle.jdbc.OraclePreparedStatement;
                          import oracle.jdbc.OracleTypes;
                          import oracle.sql.BLOB;
                          import oracle.sql.CLOB;
                          import org.xml.sax.SAXException;
                          public class Importer
                          {
                            public static boolean DEBUG = true;
                          
                            public static String GET_LOB_LOCATOR = 
                            "begin " + 
                            "  :1 := xdb_import_utilities.getLOBLocator(resourceID => :2 );" +
                            "end;";
                          
                            public static String CREATE_EMPTY_RESOURCE = 
                            "begin " + 
                            "  xdb_import_utilities.importResource" +
                            "                       (" +
                            "                         folderPath   => :1, " +
                            "                         linkName       => :2, " +
                            "                         xmlResource    => :3  " +
                            "                        ); " +
                            "end;";
                          
                            public static String CREATE_FOLDER = 
                            "begin" +
                            "  xdb_import_utilities.importFolder(:1,:2,:3);" +
                            "end;";
                            
                          
                            public static String CLEAN_UPLOAD_FOLDER = 
                            "begin " + 
                            "  xdb_import_utilities.cleanUploadFolder; "+ 
                            "end;";
                          
                            private String importRootFolder;
                            private ZipInputStream zis;
                          
                            private OracleConnection databaseConnection;
                            
                            public Importer(OracleConnection conn)
                            throws SAXException, IOException, SQLException
                            {        
                              this.databaseConnection = conn;
                              this.databaseConnection.setAutoCommit(false);
                            }
                          
                            public void importFolderTree(String path, InputStream is, String onDuplicate)
                            throws IOException, SQLException
                            {
                              this.importRootFolder = path;
                              this.zis = new ZipInputStream(is);
                              unzipArchive(onDuplicate);
                              this.zis.close();   
                            }
                            private void unzipArchive(String onDuplicate)
                            throws SQLException, IOException
                            {
                               OracleCallableStatement createFolder = (OracleCallableStatement) this.databaseConnection.prepareCall(this.CREATE_FOLDER);
                               OracleCallableStatement createResource = (OracleCallableStatement) this.databaseConnection.prepareCall(this.CREATE_EMPTY_RESOURCE);
                               OracleCallableStatement getLOBLocator = (OracleCallableStatement) this.databaseConnection.prepareCall(this.GET_LOB_LOCATOR);
                          
                               CLOB clob = null;
                               clob = CLOB.createTemporary(this.databaseConnection,true,CLOB.DURATION_SESSION);
                          
                               ZipEntry zipEntry = null;
                               String targetResourcePath;     
                               
                               while (null != (zipEntry = this.zis.getNextEntry()))
                               {
                                  String zipEntryName =  zipEntry.getName();  
                                  if (zipEntryName.startsWith("/")) 
                                  {
                                      targetResourcePath = zipEntryName;    
                                  }
                                  else
                                  {
                                    targetResourcePath = this.importRootFolder + "/" + zipEntry.getName();  
                                  }
                                  if (zipEntry.isDirectory())
                                  {
                                    System.out.println("Processing Folder " + zipEntry.getName());
                                    targetResourcePath = targetResourcePath.substring(0,targetResourcePath.length() - 1);
                                    // Folders are created based on the ZIP Entry. The Resouce definition from the source repository is then
                                    // applied after all files and subfolders have been added to the folder. This preserves the last 
                                    // modifed and last modifier values from the original repository
                                    byte[] buffer = new byte[256];
                                    int n = zis.read(buffer);
                                    if (n > 0) 
                                    {
                                      String resourceID = new String(buffer,0,n);
                                      String targetFolder = targetResourcePath.substring(0,targetResourcePath.lastIndexOf("/"));
                                      String linkName = targetResourcePath.substring(targetResourcePath.lastIndexOf("/") + 1);
                                      createFolder.setString(1,targetFolder);
                                      createFolder.setString(2,linkName);
                                      createFolder.setString(3,resourceID);
                                      createFolder.execute();
                                      this.databaseConnection.commit();
                                      zis.closeEntry();
                                    }
                                  }
                                  else
                                  {
                                    if (zipEntry.getName().startsWith("/sys/oid"))
                                    {
                                       System.out.println("Processing Content " + zipEntry.getName());
                                      // This is content. Get the LOB Locator from the resource and add the content.
                                      String resid = zipEntry.getName().substring(zipEntry.getName().lastIndexOf("/")+1);
                                      getLOBLocator.registerOutParameter(1,OracleTypes.BLOB);
                                      getLOBLocator.setString(2,resid);
                                      getLOBLocator.execute();
                                      BLOB content = getLOBLocator.getBLOB(1);
                                      unzipContent(content);
                                    }
                                    else
                                    {
                                      // Create a new resource 
                                      System.out.println("Processing Resource " + zipEntry.getName());
                                      Reader reader = new InputStreamReader(zis);
                                      Writer writer = clob.setCharacterStream(1);
                                      int n;
                                      char[] buffer = new char[CLOB.MAX_CHUNK_SIZE];
                                      while (-1 != (n = reader.read(buffer))) 
                                      {
                                        writer.write(buffer,0,n); 
                                      }
                                      writer.flush();              
                                      String targetFolder = targetResourcePath.substring(0,targetResourcePath.lastIndexOf("/"));
                                      String linkName = targetResourcePath.substring(targetResourcePath.lastIndexOf("/") + 1);
                                      try 
                                      {
                                         createResource.setString(1,targetFolder);
                                         createResource.setString(2,linkName);
                                         createResource.setCLOB(3,clob);
                                         createResource.execute();
                                         clob.truncate(0);
                                      }
                                      catch (SQLException sqle) 
                                      {
                                          sqle.printStackTrace();
                                          reader = clob.getCharacterStream();
                                          writer = new OutputStreamWriter(System.out);
                                          buffer = new char[CLOB.MAX_CHUNK_SIZE];
                                          while (-1 != (n = reader.read(buffer))) 
                                          {
                                            writer.write(buffer,0,n); 
                                          }
                                          writer.flush();                              
                                          throw sqle;
                                      }
                                    }
                                  }
                                  zis.closeEntry();
                                  this.databaseConnection.commit();
                               }
                               clob.freeTemporary();
                               createResource.close();
                               getLOBLocator.close();
                               createFolder.close();
                               
                               OracleCallableStatement cleanUploadFolder = (OracleCallableStatement) this.databaseConnection.prepareCall(this.CLEAN_UPLOAD_FOLDER);
                               cleanUploadFolder.execute();
                               this.databaseConnection.commit();
                               
                            }
                            private void unzipContent(BLOB content)
                            throws SQLException, IOException
                            {
                              OutputStream os = content.setBinaryStream(0);
                              byte[] buffer = new byte[BLOB.MAX_CHUNK_SIZE];
                              int n;     
                              while (-1 != (n = this.zis.read(buffer,0,buffer.length)))
                              {
                                os.write(buffer,0,n);
                              }         
                              os.flush();
                              os.close();
                            }
                          }
                          • 10. Re: How to export or backup an XML DB repository?
                            mdrake-Oracle
                            set echo on
                            spool xdbImport.log
                            --
                            -- XDB_IMPORT_UTILITIES should be created under XDBPM
                            --
                            alter session set current_schema = XDBPM
                            /
                            create or replace package xdb_import_utilities_10200
                            authid CURRENT_USER
                            as
                              BINARY_CONTENT     constant VARCHAR2(128) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary';
                              TEXT_CONTENT       constant VARCHAR2(128) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#text';
                              RESOURCE_SLH       constant VARCHAR2(128) := xdb_namespaces.RESOURCE_NAMESPACE;
                               
                              procedure printResourceSchemaInfo;
                              procedure cleanUploadFolder;
                              procedure deleteUploadFolder;
                              
                              function getLOBLocator(resourceID varchar2) return BLOB;
                              procedure importResource(folderPath varchar2, linkName varchar2, xmlResource XMLType, contentType varchar2);
                              procedure importResource(folderPath varchar2, linkName varchar2, xmlResource CLOB);
                              procedure importFolder(folderPath varchar2, linkName varchar2, resourceID varchar2);
                                                       
                              -- procedure setXMLContent(resid raw, schema_oid raw, element_number number);
                              -- procedure setXMLContent(resid raw, schema_url varchar, element_name varchar);
                            --
                            end;
                            /
                            show errors
                            --
                            create or replace package body xdb_import_utilities_10200
                            as
                            --
                              invalid_user exception;
                              PRAGMA EXCEPTION_INIT( invalid_user , -31071 );
                            
                              read_only_property exception;
                              PRAGMA EXCEPTION_INIT( read_only_property , -31065 );
                            
                              missing_resource_namespace exception;
                              PRAGMA EXCEPTION_INIT( missing_resource_namespace , -30937 );
                              
                              schemaOID RAW(16);
                              textElementID number(4);
                              binaryElementID number(4);
                              resourceUploadFolder varchar2(256) := '/tmp-' || to_char(systimestamp,'YYYY-MM-DD"T"HH24MISS');
                            --
                            procedure deleteResourceUploadFolder
                            is
                            begin
                              if (dbms_xdb.existsResource(resourceUploadFolder)) then
                                dbms_xdb.deleteResource(resourceUploadFolder,4);
                              end if; 
                              commit;
                            end;
                            --
                            procedure createResourceUploadFolder
                            is
                              res boolean;
                            begin
                              deleteResourceUploadFolder;
                              xdbpm_helper.createFolder(resourceUploadFolder,USER,'/sys/acls/all_owner_acl.xml');
                              commit;
                            end;
                            --
                            procedure setResourceUploadFolder(path varchar2)
                            is
                            begin
                              deleteResourceUploadFolder();
                              resourceUploadFolder := path;
                              createResourceUploadFolder();
                            end;
                            --
                            procedure getResourceSchemaInfo
                            as
                            begin
                              select object_id,
                                   extractValue
                                   (
                                     object_value,
                                     '/xs:schema/xs:element[@name="binary"]/@xdb:propNumber',
                                     'xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"'
                                   ),
                                   extractValue
                                   (
                                     object_value,
                                     '/xs:schema/xs:element[@name="text"]/@xdb:propNumber',
                                     'xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"'
                                   )
                                into schemaOID, binaryElementID, textElementID
                                from xdb.xdb$schema
                               where extractValue
                                   (
                                     object_value,
                                     '/xs:schema/@xdb:schemaURL',
                                     'xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"'
                                   )  = 'http://xmlns.oracle.com/xdb/XDBSchema.xsd';
                               printResourceSchemaInfo();
                            end;
                            --
                            procedure printResourceSchemaInfo
                            as
                            begin
                              dbms_output.put_line('Schema ID = ' || schemaOID);
                              dbms_output.put_line('Binary ID = ' || binaryElementID);
                              dbms_output.put_line('Text   ID = ' || textElementID);
                            end;
                            --
                            procedure patchModificationStatus(xmlResource xmltype)
                            is
                            begin
                              null;
                            end;
                            --
                            procedure setBinaryContent(resid raw)
                            is
                            begin
                              -- dbms_output.put_line('Creating Binary Content');
                              update xdb.xdb$resource r
                                 set r.xmldata.xmllob = empty_blob(),
                                     r.xmldata.schoid = schemaOID,
                                     r.xmldata.elnum  = binaryElementID
                               where object_id = resid
                                 and r.xmldata.xmllob is null;
                            end;
                            --
                            procedure setTextContent(resid raw)
                            is
                            begin
                              -- dbms_output.put_line('Creating Text Content');
                              update xdb.xdb$resource r
                                 set r.xmldata.xmllob = empty_blob(),
                                     r.xmldata.schoid = schemaOID,
                                     r.xmldata.elnum  = textElementID
                               where object_id = resid
                                 and r.xmldata.xmllob is null;
                            end;
                            --
                            procedure setXMLContent(resid raw)
                            is
                            begin
                              -- dbms_output.put_line('Creating NSB XML Content');
                              update xdb.xdb$resource r
                                 set r.xmldata.xmllob = empty_blob()
                               where object_id = resid
                                 and r.xmldata.xmllob is null;
                            end;
                            --
                            procedure createPrinciples(xmlResource xmlType )
                            as
                              tmp      xmltype;
                              owner    varchar2(32);
                              modifier varchar2(32);
                              creator  varchar2(32);
                              result   number;
                            begin  
                              tmp := xmlResource.createNonSchemaBasedXML();
                              select extractValue(tmp,'/r:Resource/r:Owner',xdb_namespaces.RESOURCE_PREFIX_R),
                                     extractValue(tmp,'/r:Resource/r:LastModifier',xdb_namespaces.RESOURCE_PREFIX_R),
                                     extractValue(tmp,'/r:Resource/r:Creator',xdb_namespaces.RESOURCE_PREFIX_R) 
                                into owner, modifier, creator
                                from dual;
                                -- dbms_output.put_line('Owner := ' || owner || '. LastModifier = ' || modifier || '. Creator = ' || creator);
                              begin
                                select 1 
                                  into result
                                  from all_users
                                 where username = owner;
                              exception
                                when no_data_found then 
                                  execute immediate 'create user "' || owner || '" identified by password account lock';
                              end;
                                    
                              begin
                                select 1 
                                  into result
                                  from all_users
                                 where username = modifier;
                              exception
                                when no_data_found then
                                  execute immediate 'create user "' || modifier || '" identified by password account lock';
                              end;
                            
                              begin
                                select 1 
                                  into result 
                                  from all_users
                                 where username = creator;
                              exception
                                when no_data_found then 
                                  execute immediate 'create user "' || creator || '" identified by password account lock';
                              end;
                            
                            end;
                            --
                            procedure addContent(resourceID raw, contentType varchar2)
                            as
                            begin
                            
                               -- Create empty content LOB based on document type.
                              
                              if (contentType =  BINARY_CONTENT) then
                                setBinaryContent(resourceID); 
                              end if;
                               
                              if (contentType =  TEXT_CONTENT) then
                                setTextContent(resourceID); 
                              end if;
                               
                              if (contentType is null) then
                                setXMLContent(resourceID);
                              end if;
                            end;
                            --
                            procedure importResource(targetPath varchar2, xmlResource xmltype, contentType varchar2)
                            as
                              resourceID    raw(16);
                            begin
                              
                              insert into RESOURCE_VIEW (ANY_PATH, RES) values (targetPath, xmlResource);     
                              
                              select resid
                                into resourceID
                                from RESOURCE_VIEW
                               where equals_path(res,targetPath) = 1;
                                                   
                               addContent(resourceID,contentType); 
                              
                            end;
                            --
                            procedure updateResource(folderPath varchar2, xmlResource xmltype)
                            as
                              result      boolean;
                              resourceID  raw(16);
                            begin
                              dbms_output.put_line('updateResource() : folderPath = ' || folderPath);
                              update resource_view
                                 set res = xmlResource
                               where equals_path(res,folderPath) = 1;
                            end;
                            --
                            procedure linkVersionedResource(linkTarget varchar2, targetPath varchar2)
                            as
                              resourcePath varchar2(512);
                              targetFolder varchar2(512);
                              linkName     varchar2(512);
                            begin
                            
                              -- Strip RESOURCE ID from targetPath.
                              resourcePath := substr(targetPath,1,instr(targetPath,'/',-1)-1);
                              
                              -- LinkName is the last componant of the resourcePath;
                              linkName := substr(targetFolder,instr(targetFolder,'/',-1)+1);
                              
                              -- Strip the LinkName from the targetFolder
                              targetFolder :=  substr(resourcePath,1,instr(resourcePath,'/',-1)-1);
                            
                              if (not dbms_xdb.existsResource(resourcePath)) then 
                                dbms_xdb.link(linkTarget,targetFolder,linkName);    
                              else
                                -- Hardcoded 'skip' mode. Add code to handle other options (error, update, version)
                                null;
                              end if;
                            
                            end;
                            --
                            procedure importVersionedResource(versionPath varchar2, versionedResource XMLType,resourceID varchar2, contentType varchar2)
                            as
                              targetPath varchar2(256) := substr(versionPath,1,instr(versionPath,'/',-1)-1);
                              xmlResource XMLType := versionedResource;
                              newResourceID  raw(16);
                            begin
                              select deleteXML(xmlResource,'/r:Resource/r:VCRUID',xdb_namespaces.RESOURCE_PREFIX_R) into xmlResource from dual;
                              select deleteXML(xmlResource,'/r:Resource/r:Parents',xdb_namespaces.RESOURCE_PREFIX_R) into xmlResource from dual;
                              select deleteXML(xmlResource,'/r:Resource/@VersionID',xdb_namespaces.RESOURCE_PREFIX_R)  into xmlResource from dual;    
                            
                              if (not dbms_xdb.existsResource(targetPath)) then
                                -- Create Original Version
                                importResource(targetPath, xmlResource, contentType);
                                newResourceID  := dbms_xdb_version.makeVersioned(targetPath);
                              else
                                dbms_xdb_version.checkout(targetPath);
                                update RESOURCE_VIEW
                                   set res = xmlResource
                                 where equals_path(res,targetPath) = 1;
                                newResourceID  := dbms_xdb_version.checkin(targetPath);
                                addContent(newResourceID, contentType);
                              end if;
                            
                              dbms_xdb.link(targetPath,resourceUploadFolder,resourceID); 
                              
                            end;
                            --
                            procedure importFolder(folderPath varchar2, linkName varchar2, resourceID varchar2)
                            is
                              res boolean;
                              targetPath varchar2(512);
                              resourceUploadLink varchar2(512);
                            begin
                              resourceUploadLink := resourceUploadFolder || '/' || resourceId;
                              targetPath := folderPath || '/' || linkName;
                              if (dbms_xdb.existsResource(resourceUploadLink)) then 
                                if (not dbms_xdb.existsResource(targetPath)) then 
                                  dbms_xdb.link(resourceUploadLink,folderPath,linkName);
                                end if;
                              else
                                if (not dbms_xdb.existsResource(targetPath)) then 
                                  res := dbms_xdb.createFolder(targetPath);
                                  dbms_xdb.link(targetPath,resourceUploadFolder,resourceID);
                                else
                                  -- Hardcoded 'skip' mode. Add code to handle other options (error, update, version)
                                  null;
                                end if;
                              end if;
                            end;
                            --
                            procedure importResource(folderPath varchar2, linkName varchar2, xmlResource CLOB)
                            is
                             resourceXML xmltype;
                             contentType varchar2(512);
                            begin
                              
                              -- Fix namespace before converting to Schema Based XMLType.
                               
                              resourceXML := xmltype(xmlResource);
                            
                              if (resourceXML.getNamespace() is null) then
                                select insertChildXML(resourceXML,'/Resource','@xmlns',xdb_namespaces.RESOURCE_NAMESPACE) into resourceXML from dual;
                              end if;
                            
                              if (resourceXML.existsNode('/r:Resource/r:SchemaElement',xdb_namespaces.RESOURCE_PREFIX_R) = 1 ) then
                                select extractValue(resourceXML,'/r:Resource/r:SchemaElement',xdb_namespaces.RESOURCE_PREFIX_R) into contentType from dual;
                              end if;
                              -- dbms_output.put_line('importResource() : Content Type = ' || contentType);
                            
                              resourceXML := resourceXML.createSchemaBasedXML(RESOURCE_SLH);
                              importResource(folderPath, linkName, resourceXML, contentType);
                            end;
                            --
                            procedure importResource(folderPath varchar2, linkName varchar2, xmlResource XMLType, contentType varchar2)
                            is
                              resourceID            varchar2(64);
                              resourceLink          varchar2(256);
                              resourceXML           xmlType := xmlResource;
                            
                              resourceName          varchar2(512);
                              linkFolder            varchar2(512);
                            
                              aclPath               varchar2(256);
                            
                              dom                   dbms_xmldom.DOMDocument;
                              nl                    dbms_xmldom.DOMNodeList;
                              node                  dbms_xmldom.DOMNode;
                              pi                    dbms_xmldom.DOMProcessingInstruction;
                                  
                              result                boolean;
                              
                              targetPath            varchar2(256);
                              newResourceId         raw(16);
                              
                              owner                 varchar2(32);
                            begin
                            
                              -- Cannot Validate a Resource against XDBResource.xsd since XDBResource.xsd is not a valid XML Schema - Bootstrap problems.
                              --  
                              -- if (resourceXML.isSchemaValid() = 0) then
                              --   resourceXML.schemaValidate();
                              -- end if;
                              --
                            
                              begin  
                                 -- Validate Creator, Owner and LastModifier 
                                 if (resourceXML.existsNode('/r:Resource/r:Owner',xdb_namespaces.RESOURCE_PREFIX_R) = 1) then
                                   null; 
                                 end if;
                              exception
                                when invalid_user then
                                  createPrinciples(resourceXML);
                                when read_only_property then
                                  select deleteXML(resourceXML,'/r:Resource/r:Lock',xdb_namespaces.RESOURCE_PREFIX_R) into resourceXML from dual;
                              end;
                              
                              targetPath   := folderPath || '/' || linkName; 
                              
                              dom          := dbms_xmldom.newDOMDocument(resourceXML);
                            
                              -- Get the Original Resource ID for this resource.
                            
                              node         := dbms_xmldom.getFirstChild(dbms_xmldom.makeNode(dom));
                              pi           := dbms_xmldom.makeProcessingInstruction(node);
                              resourceID   := dbms_xmldom.getData(pi);
                              node         := dbms_xmldom.removeChild(dbms_xmldom.makeNode(dom),node);
                              dbms_xmldom.freeNode(node);
                            
                              resourceLink := resourceUploadFolder || '/' || resourceID;
                            
                              -- Check if this resource is a versioned Resource
                              
                              if (resourceXML.existsNode('/r:Resource/r:VCRUID',xdb_namespaces.RESOURCE_PREFIX_R) = 0) then
                              
                                -- Non Versioned Resource
                                -- Non versioned document contains a PI containing path to ACL used to secure this resource
                                 
                                node := dbms_xmldom.getFirstChild(dbms_xmldom.makeNode(dom));
                                pi := dbms_xmldom.makeProcessingInstruction(node);
                                aclPath := dbms_xmldom.getData(pi);
                                node := dbms_xmldom.removeChild(dbms_xmldom.makeNode(dom),node);
                                dbms_xmldom.freeNode(node);
                            
                                aclPath := dbms_xmldom.getData(pi);  
                            
                                if (resourceXML.existsNode('/r:Resource[@Container="true"]',xdb_namespaces.RESOURCE_PREFIX_R)= 1) then
                                
                                  -- Resource is a Folder 
                                  
                                  if (not dbms_xdb.existsResource(resourceLink)) then
                                  
                                    -- Skip if no link found. 
                                    -- Import logic implies that Folders should always exist so we should probably should raise an Exception rather than skip;
                                    
                                    null;
                                  else
                                    
                                    -- Update Resource for the Folder with the resource from the Import File
                                  
                                    updateResource(resourceLink,resourceXML);        
                                  end if;
                                else
                             
                                  if (dbms_xdb.existsResource(resourceLink)) then
                              
                                    -- Create new Link to existing Non Versioned Resource
                                    
                                    if (not dbms_xdb.existsResource(targetPath)) then 
                                      dbms_xdb.link(resourceLink,folderPath,linkName);    
                                    else
                                      -- Hardcoded 'skip' mode. Add code to handle other options (error, update, version)
                                      null;
                                    end if;
                            
                                  else
                                 
                                    -- Create new Resource
                                 
                                    if (not dbms_xdb.existsResource(targetPath)) then 
                                      importResource(targetPath,resourceXML,contentType); 
                                      dbms_xdb.link(targetPath,resourceUploadFolder,resourceID); 
                                    else
                                      -- Hardcoded 'skip' mode. Add code to handle other options (error, update, version)
                                      null;
                                    end if;
                                    
                                  end if; 
                                  
                                  dbms_xdb.setAcl(targetPath,aclPath); 
                                  patchModificationStatus(resourceXML);
                                  
                                end if;
                            
                              else
                            
                                -- Versioned Resource
                            
                                if (dbms_xdb.existsResource(resourceLink)) then
                              
                                  -- Create new Link to existing Versioned Resource
                                    
                                  if (not dbms_xdb.existsResource(targetPath)) then 
                                    dbms_xdb.link(resourceLink,folderPath,linkName);    
                                  else
                                    -- Hardcoded 'skip' mode. Add code to handle other options (error, update, version)
                                    null;
                                  end if;
                            
                                else
                                  
                                  -- New Versioned Resource or Resource Version
                            
                                  importVersionedResource(targetPath,resourceXML,resourceID,contentType);
                            
                                end if;
                                
                              end if;
                                
                            end;
                            --
                            function getLOBLocator(resourceID varchar2) 
                            return BLOB
                            is
                              resourceLink varchar2(512) := resourceUploadFolder || '/' || resourceID;
                              newResourceID raw(16);
                              lobLocator BLOB;
                            begin 
                            
                              select resid
                                into newResourceID
                                from resource_view
                               where equals_path(res,resourceLink) = 1
                                 for update;
                            
                              -- Cannot get LOB Locator for Resource Content via XML APIs
                            
                              select x.xmldata.XMLLob
                                into lobLocator
                                from xdb.xdb$resource x
                               where object_id = newResourceID;
                                 
                              dbms_xdb.deleteResource(resourceLink);
                              
                              return lobLocator;
                            
                            end;
                            --
                            procedure cleanUploadFolder
                            as
                            begin
                              delete 
                                from path_view
                               where under_path(res,1,resourceUploadFolder) = 1
                                 and existsNode(res,'/Resource[@Container = "true" and RefCount > 1]') = 1;
                            end;
                            --
                            procedure deleteUploadFolder
                            as
                            begin
                              dbms_xdb.deleteResource(resourceUploadFolder);
                            end;
                            --
                            procedure temporaryCode
                            is
                              resid raw(16);
                              newModificationDate timestamp;
                              modifierId        raw(16);
                              result boolean;
                              xmlResource xmltype;
                              lastModifier          varchar2(64);
                              lastModificationDate  timestamp;
                            begin
                               result := dbms_xdbz.get_userid(lastModifier,modifierId,dbms_xdbz.NAME_FORMAT_SHORT);
                               update xdb.xdb$resource x
                                  set x.xmldata.modificationDate = newModificationDate,
                                      x.xmldata.lastModifierID = modifierId
                                where object_id = resid;
                              select extractValue(xmlResource,'/Resource/LastModifier','xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"'),
                                     to_timestamp(extractValue(xmlResource,'/Resource/ModificationDate','xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"'),'YYYY-MM-DD"T"HH24:MI:SS.FF')
                                into lastModifier, lastModificationDate
                                from dual;
                            end;
                            --
                            procedure createFolderTable
                            is
                            begin
                              null;
                            end;
                            --
                            begin
                              createResourceUploadFolder;
                              createFolderTable;
                              getResourceSchemaInfo;
                            end;
                            /
                            show errors
                            --
                            create or replace public synonym xdb_import_utilities for xdb_import_utilities_10200
                            /
                            grant execute on xdb_import_utilities_10200 to public
                            /
                            quit
                            • 11. Re: How to export or backup an XML DB repository?
                              mdrake-Oracle
                              package com.oracle.st.xmldb.pm.common.baseApp;
                              import java.io.IOException;
                              import java.io.InputStream;
                              import java.io.InputStreamReader;
                              import java.io.PrintWriter;
                              import java.io.Writer;
                              import java.sql.Connection;
                              import java.sql.SQLException;
                              import oracle.sql.CLOB;
                              import org.xml.sax.SAXException;
                              /**
                               * A Class class.
                               * <P>
                               * @author Mark D. Drake
                               * Please complete these missing tags
                               * @rref
                               * @copyright
                               * @concurrency
                               * @see
                               */
                              
                              public abstract class BaseApplication extends ConnectionProvider
                              {
                                  public static final boolean DEBUG = true;
                              
                                  public abstract void doSomething( String [] args ) throws Exception;
                              
                                  public CLOB createCLOB( Connection conn, InputStream is )
                                  throws SQLException, IOException
                                  {
                                      CLOB clob = CLOB.createTemporary( conn, false, CLOB.DURATION_SESSION);
                                      writeToClob(clob,is);
                                      return clob;
                                  }
                              
                                  public CLOB writeToClob( CLOB clob, InputStream is)
                                  throws SQLException, IOException 
                                  {
                                      InputStreamReader reader = new InputStreamReader( is );
                                      Writer writer = clob.setCharacterStream(0);
                                      
                                      char [] buffer = new char [ clob.getChunkSize() ];
                                      for( int charsRead = reader.read( buffer );
                                      charsRead > - 1;
                                      charsRead = reader.read( buffer ) )
                                      {
                                          writer.write( buffer, 0, charsRead );
                                      }
                                      writer.close();
                                      return clob;
                                  }
                                  /**
                                   * Please complete the missing tags for main
                                   * @param
                                   * @return
                                   * @throws
                                   * @pre
                                   * @post
                                   */
                                  public BaseApplication() 
                                  {
                                      super();
                                  }
                              }
                              • 12. Re: How to export or backup an XML DB repository?
                                mdrake-Oracle
                                package com.oracle.st.xmldb.pm.common.baseApp;
                                import java.io.File;
                                import java.io.FileReader;
                                import java.io.IOException;
                                import java.io.PrintStream;
                                import java.io.PrintWriter;
                                import java.io.Reader;
                                import java.io.StringWriter;
                                import java.io.Writer;
                                import java.sql.DriverManager;
                                import java.sql.Connection;
                                import java.sql.SQLException;
                                import java.util.Properties;
                                import oracle.jdbc.OracleConnection;
                                import oracle.jdbc.pool.OracleOCIConnectionPool;
                                import oracle.jdbc.oci.OracleOCIConnection;
                                import oracle.xml.parser.v2.DOMParser;
                                import oracle.xml.parser.v2.XMLDocument;
                                import oracle.xml.parser.v2.XMLElement;
                                import org.xml.sax.SAXException;
                                import org.w3c.dom.Element;
                                import org.w3c.dom.Text;
                                import org.w3c.dom.NodeList;
                                import java.io.InputStream;
                                public class ConnectionProvider extends Object
                                {
                                    public static final boolean DEBUG = true;
                                
                                    protected OracleConnection connection;
                                
                                    protected XMLDocument connectionDefinition;
                                
                                    protected OracleOCIConnectionPool connectionPool;
                                
                                    public static final String CONNECTION = "Connection";
                                    public static final String DRIVER = "Driver";
                                    public static final String HOSTNAME = "Hostname";
                                    public static final String PORT = "Port";
                                    public static final String SID = "SID";
                                    public static final String SERVICENAME = "ServiceName";
                                    public static final String SERVERMODE = "Server";
                                    public static final String SCHEMA = "Schema";
                                    public static final String PASSWORD = "Password";
                                    public static final String POOL = "Pool";
                                    public static final String THIN_DRIVER = "thin";
                                    public static final String OCI_DRIVER = "oci8";
                                
                                    public static final String DEFAULT_CONNECTION_DEFINITION = "c:\\temp\\connection.xml";
                                    public static final String DEFAULT_DRIVER = OCI_DRIVER;
                                    public static final String DEFAULT_HOSTNAME = "localhost";
                                    public static final String DEFAULT_PORT = "1521";
                                    public static final String DEFAULT_SERVERMODE = "DEDICATED";
                                
                                    public static final String TARGET_DIRECTORY = "targetDirectory";
                                
                                    protected PrintStream log;
                                    
                                    public ConnectionProvider() {
                                        
                                    }
                                    
                                    public void initializeConnection()     
                                    throws SAXException, IOException, SQLException
                                    {
                                        this.initializeConnection(System.out);
                                    }
                                    
                                    public void initializeConnection(PrintStream log)
                                    throws SAXException, IOException, SQLException
                                    {
                                      DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
                                      this.log = log;
                                      loadConnectionSettings();
                                      this.connection = openConnection();
                                    }
                                    
                                    public ConnectionProvider getConnectionProvider() {
                                        return this;
                                    }
                                    
                                    public void initalizeConnection(String connectionLocation, PrintStream log)
                                    throws SAXException, IOException, SQLException
                                    {
                                      DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
                                      this.log = log;
                                      loadConnectionSettings(connectionLocation);
                                      this.connection = openConnection();
                                    }
                                    public void setLogger(PrintStream log)
                                    {
                                      this.log = log;
                                    }
                                    private void setConnectionSettings(XMLDocument doc)
                                    {
                                      this.connectionDefinition = doc;
                                    }
                                
                                    private void dumpConnectionSettings()
                                    throws IOException
                                    {
                                      StringWriter sw = new StringWriter();
                                      PrintWriter pw = new PrintWriter(sw);
                                      this.connectionDefinition.print(pw);
                                      pw.close();
                                      sw.close();
                                    }
                                    private void setConnectionPool(OracleOCIConnectionPool pool)
                                    {
                                      this.connectionPool = pool;
                                    }
                                    public OracleConnection getConnection()
                                    throws SQLException
                                    {
                                      if (this.isPooled())
                                      {
                                        return (OracleOCIConnection) this.connectionPool.getConnection();
                                      }
                                      else
                                      {
                                        return this.connection;
                                      }
                                   }
                                    
                                   public void closeConnection(Connection conn)
                                   throws Exception
                                   {
                                      if (isPooled())
                                      {
                                       conn.close();
                                      }
                                    } 
                                    public Connection getConnection(String schema, String passwd)
                                    throws Exception
                                    {
                                      if (isPooled())
                                      {
                                        return (OracleOCIConnection) this.getConnection(schema,passwd);
                                      }
                                      else
                                      {
                                        return this.connection;
                                      }
                                    }
                                    public String getSetting(String nodeName)
                                    {
                                      return getSetting(nodeName, null);
                                    }
                                 
                                    public String getSetting(String nodeName, String defaultValue)
                                    {
                                      String textValue = null;
                                      XMLElement root = (XMLElement) this.connectionDefinition.getDocumentElement();
                                      NodeList children = root.getChildrenByTagName(nodeName);
                                      if (children.getLength() != 0)
                                      {
                                        Element  element = (Element) children.item(0);
                                        Text text = (Text) element.getFirstChild();
                                        if (text != null)
                                        {
                                            return text.getData();
                                        }
                                      }
                                      return defaultValue;
                                    }   
                                    protected String getDriver()
                                    {
                                        return getSetting(DRIVER,DEFAULT_DRIVER);
                                    }
                                    protected String getHostname()
                                    {
                                        return getSetting(HOSTNAME,DEFAULT_HOSTNAME);
                                    }
                                    protected String getPort()
                                    {
                                        return getSetting(PORT,DEFAULT_PORT);
                                    }
                                    protected String getServerMode()
                                    {
                                        return getSetting(SERVERMODE,DEFAULT_SERVERMODE);
                                    }
                                    protected String getServiceName()
                                    {
                                        return getSetting(SERVICENAME);
                                    }
                                    protected String getSID()
                                    {
                                        return getSetting(SID);
                                    }
                                    protected boolean isPooled()
                                    {
                                        String usePool =  getSetting(POOL,Boolean.FALSE.toString());
                                        return !usePool.equalsIgnoreCase(Boolean.FALSE.toString());
                                    }
                                    protected String getSchema()
                                    {
                                        return getSetting(SCHEMA);
                                    }
                                    protected String getPassword()
                                    {
                                        return getSetting(PASSWORD);
                                    }
                                    
                                    public void loadConnectionSettings()
                                    throws IOException, SAXException
                                    {
                                      String filename = System.getProperty( "com.oracle.st.xmldb.pm.ConnectionParameters", this.DEFAULT_CONNECTION_DEFINITION ) ;
                                      loadConnectionSettings(filename);
                                    }
                                    public void loadConnectionSettings(String filename)
                                    throws IOException, SAXException
                                    {
                                      Reader reader = new FileReader(new File(filename));
                                      DOMParser parser = new DOMParser();
                                      parser.parse(reader);
                                      XMLDocument doc = parser.getDocument();
                                      setConnectionSettings(doc);
                                      if (DEBUG)
                                      {
                                        dumpConnectionSettings();
                                      }
                                    }
                                    protected String getDatabaseURL()
                                    {
                                        if( getDriver() != null)
                                        {
                                          if( getDriver().equalsIgnoreCase( THIN_DRIVER ) )
                                          {
                                              return "jdbc:oracle:thin:@" + getHostname() + ":" + getPort() + ":" + getSID();
                                          }
                                          else
                                          {
                                              return "jdbc:oracle:oci8:@(description=(address=(host=" + getHostname() + ")(protocol=tcp)(port=" + getPort() + "))(connect_data=(service_name=" + getServiceName() + ")(server=" + getServerMode() + ")))";
                                          }
                                        }
                                        else
                                        {
                                          return null;
                                        }
                                    }   
                                    private OracleConnection openConnection()
                                    throws SQLException
                                    {
                                        String user = getSchema();
                                        String password = getPassword();
                                        String connectionString = user + "/" + password + "@" + getDatabaseURL();
                                        OracleConnection conn = null;
                                        if( DEBUG )
                                        {
                                            this.log.println( "ConnectionProvider.establishConnection(): Connecting as " + connectionString );
                                        }
                                        try
                                        {
                                            conn = (OracleConnection) DriverManager.getConnection( getDatabaseURL(), user, password );
                                            if( DEBUG )
                                            {
                                                this.log.println( "ConnectionProvider.establishConnection(): Database Connection Established" );
                                            }
                                        }
                                        catch( SQLException sqle )
                                        {
                                            int err = sqle.getErrorCode();
                                            this.log.println( "ConnectionProvider.establishConnection(): Failed to connect using " + connectionString );
                                            sqle.printStackTrace(this.log);
                                            throw sqle;
                                        }
                                        return conn;
                                    }
                                    public OracleConnection getNewConnection()
                                    throws SQLException
                                    {
                                      return openConnection();
                                    }
                                  
                                    public OracleOCIConnectionPool createConnectionPool()
                                    throws Exception
                                    {
                                      OracleOCIConnectionPool pool;
                                      Properties poolConfig  = new Properties( );
                                      poolConfig.put (OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "1");
                                      poolConfig.put (OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "1");
                                      poolConfig.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT, "0");
                                      return new OracleOCIConnectionPool(getSchema(),getPassword(), getDatabaseURL(),poolConfig);
                                    }
                                   public XMLDocument getConnectionSettings()
                                   {
                                     return this.connectionDefinition;
                                   }
                                }
                                • 13. Re: How to export or backup an XML DB repository?
                                  mdrake-Oracle
                                  An example of the connection file is show below
                                  -bash-2.05b$ cat export.xml
                                  <?xml version='1.0' encoding='windows-1252'?>
                                  <Connection>
                                    <Driver>OCI</Driver>
                                    <Hostname>localhost</Hostname>
                                    <Port>1521</Port>
                                    <ServiceName>ORA10GR2.pm.xdb.oracle.com</ServiceName>
                                    <SID>ORA10GR2</SID>
                                    <ServerMode>DEDICATED</ServerMode>
                                    <Schema>XDB</Schema>
                                    <Password>XDB</Password>
                                    <TargetFolder>/home/MARK.DRAKE</TargetFolder>
                                    <Mode>export</Mode>
                                    <ZipFileName>repository.zip</ZipFileName>
                                  </Connection>