SQL> @?/demo/schema/order_entry/coe_xml
SQL> Rem
SQL> Rem $Header: coe_xml.sql 25-feb-2005.12:38:42 cbauwens Exp $
SQL> Rem
SQL> Rem coe_xml.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      coe_xml.sql - Create XML DB data for user OE
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      Create XML DB data for user OE
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      .
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cbauwens	02/24/05 - drop 2 more xdb objects after xml schema creation
SQL> Rem    cbauwens	08/04/04 - drop xdb packages after xml schema creation
SQL> Rem    cbauwens	03/16/04 - enhanced xml shema
SQL> Rem    cbauwens	12/23/03 -
> Rem	 cbauwens    10/28/03 - set nls_numeric_characters
SQL> Rem    ahunold	02/13/03 - typo: REFERENCE_IS_UNQIUE
SQL> Rem    ahunold	10/12/02 - revoke ANY DIRECTORY privilege when done
SQL> Rem    ahunold	10/07/02 - Created
SQL> Rem
SQL> 
SQL> PROMPT

SQL> PROMPT specify password for OE as parameter 1:
specify password for OE as parameter 1:
SQL> DEFINE pass_oe  = &1
Enter value for 1: oe
SQL> PROMPT

SQL> PROMPT PROMPT password for SYS as parameter 2:
PROMPT password for SYS as parameter 2:
SQL> DEFINE pass_sys = &2
Enter value for 2: oracle
SQL> PROMPT

SQL> 
SQL> 
SQL> --
SQL> -- CONNECT as SYS. Add roles AND privileges to OE.
SQL> --
SQL> CONNECT sys/&pass_sys AS SYSDBA;
Connected.
SQL> 
SQL> 
SQL> GRANT xdbadmin TO oe;

Grant succeeded.

SQL> GRANT create any directory TO oe;

Grant succeeded.

SQL> GRANT drop any directory TO oe;

Grant succeeded.

SQL> GRANT alter session TO oe;

Grant succeeded.

SQL> 
SQL> -- Create stored objects
SQL> 	@?/demo/schema/order_entry/xdbSupport
SQL> Rem
SQL> Rem $Header: xdbSupport.sql 23-sep-2004.13:45:33 cbauwens Exp $
SQL> Rem
SQL> Rem xdbSupport.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2004, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      xdbSupport.sql - Setup scripts for xml db env
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      .
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      .
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cbauwens	09/23/04 - cbauwens_bug3031915
SQL> Rem    cbauwens	03/16/04 - Created
SQL> 
SQL> 
SQL> 
SQL> 
SQL> -- Prepare environment
SQL> @?/demo/schema/order_entry/xdbConfiguration
SQL> Rem
SQL> Rem $Header: xdbConfiguration.sql 23-sep-2004.13:45:33 cbauwens Exp $
SQL> Rem
SQL> Rem xdbConfiguration.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2004, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      xdbConfiguration.sql - Create view DATABASE_SUMMARY and package XDB_CONFIGURATION
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      .
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      .
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cbauwens	09/23/04 - cbauwens_bug3031915
SQL> Rem    cbauwens	03/16/04 - Created
SQL> R
  1* GRANT alter session TO oe

Grant succeeded.

SQL> 
SQL> CREATE OR REPLACE VIEW DATABASE_SUMMARY
  2  AS
  3  SELECT d.NAME, p.VALUE "SERVICE_NAME", i.HOST_NAME, n.VALUE "DB_CHARACTERSET"
  4    FROM v$system_parameter p, v$database d, v$instance i, nls_database_parameters n
  5    WHERE p.name = 'service_names'
  6  	 AND n.parameter='NLS_CHARACTERSET';

View created.

SQL> /

View created.

SQL> 
SQL> 
SQL> GRANT SELECT ON DATABASE_SUMMARY TO PUBLIC
  2  /

Grant succeeded.

SQL> ALTER SESSION SET current_schema = XDB
  2  /

Session altered.

SQL> 
SQL> CREATE OR REPLACE PACKAGE XDB_CONFIGURATION
  2  AUTHID CURRENT_USER
  3  AS
  4    PROCEDURE  setHTTPport (PORT_NUMBER number);
  5    PROCEDURE  setFTPport  (PORT_NUMBER number);
  6    FUNCTION   getDatabaseSummary return xmltype;
  7    PROCEDURE  folderDatabaseSummary;
  8  END XDB_CONFIGURATION;
  9  /

Package created.

SQL> 
SQL> 
SQL> CREATE OR REPLACE PACKAGE BODY XDB_CONFIGURATION AS
  2  
  3  ftp_xpath	 VARCHAR2(256) := '/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port';
  4  http_xpath  VARCHAR2(256) := '/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port';
  5  
  6  FUNCTION getDatabaseSummary
  7    RETURN XMLType
  8    AS
  9  	 summary XMLType;
 10  	 dummy XMLType;
 11  BEGIN
 12    SELECT DBMS_XDB.cfg_get()
 13  	 INTO dummy
 14  	 FROM dual;
 15  
 16    SELECT xmlElement
 17  	      (
 18  		"Database",
 19  		XMLAttributes
 20  		(
 21  		  x.NAME as "Name",
 22  		  extractValue(config,'/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port') as "HTTP",
 23  		  extractValue(config,'/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port') as "FTP"
 24  		),
 25  		xmlElement
 26  		(
 27  		  "Services",
 28  		  (
 29  		    xmlForest(SERVICE_NAME as "ServiceName")
 30  		  )
 31  		),
 32  		xmlElement
 33  		(
 34  		  "NLS",
 35  		  (
 36  		    XMLForest(DB_CHARACTERSET as "DatabaseCharacterSet")
 37  		  )
 38  		),
 39  		xmlElement
 40  		(
 41  		  "Hosts",
 42  		  (
 43  		    XMLForest(HOST_NAME as "HostName")
 44  		  )
 45  		),
 46  		xmlElement
 47  		(
 48  		  "VersionInformation",
 49  		  ( XMLCONCAT
 50  			   (
 51  			     (select XMLAGG(XMLElement
 52  			     (
 53  				"ProductVersion",
 54  				BANNER
 55  			     )
 56  			     )from V$VERSION),
 57  			     (select XMLAGG(XMLElement
 58  			     (
 59  				"ProductVersion",
 60  				BANNER
 61  			     )
 62  			     ) from ALL_REGISTRY_BANNERS)
 63  			   )
 64  		  )
 65  		)
 66  	      )
 67  	 INTO summary
 68  	 FROM SYS.DATABASE_SUMMARY x, (SELECT DBMS_XDB.cfg_get() config FROM dual);
 69    summary := XMLType(summary.getClobVal());
 70    RETURN summary;
 71  END;
 72  
 73  PROCEDURE folderDatabaseSummary
 74  AS
 75  	result BOOLEAN;
 76  	targetResource VARCHAR2(256) := '/sys/databaseSummary.xml';
 77  
 78  	xmlref REF XMLType;
 79  
 80  BEGIN
 81  
 82  	BEGIN
 83  	  DBMS_XDB.deleteResource(targetResource, DBMS_XDB.DELETE_FORCE);
 84  	EXCEPTION
 85  	  WHEN OTHERS THEN
 86  	    NULL;
 87  	END;
 88  
 89  	SELECT make_ref(DATABASE_SUMMARY,'DATABASE_SUMMARY')
 90  	  INTO xmlref
 91  	  FROM DATABASE_SUMMARY;
 92  	result := DBMS_XDB.createResource(targetResource, xmlref);
 93  	DBMS_XDB.setAcl(targetResource, '/sys/acls/bootstrap_acl.xml');
 94  END;
 95  
 96  PROCEDURE setXDBport(port_xpath VARCHAR2, port_number NUMBER)
 97  AS
 98  	config XMLType;
 99  BEGIN
100  	config := DBMS_XDB.cfg_get();
101  	SELECT updateXML(config, port_xpath, port_number)
102  	  INTO config
103  	  FROM DUAL;
104  	DBMS_XDB.cfg_update(config);
105  	COMMIT;
106  	DBMS_XDB.cfg_refresh();
107  END;
108  --
109  -- Create the setHTTPport and setFTPport procudures
110  
111  PROCEDURE setHTTPport (port_number NUMBER)
112  AS
113  BEGIN
114    setXDBport(HTTP_XPATH || '/text()', port_number);
115  END;
116  
117  PROCEDURE setFTPport(port_number NUMBER)
118  AS
119  BEGIN
120    setXDBport(FTP_XPATH || '/text()', port_number);
121  END;
122  
123  END XDB_CONFIGURATION;
124  /

Warning: Package Body created with compilation errors.

SQL> 
SQL> 
SQL> CREATE OR REPLACE VIEW database_summary OF XMLType
  2  WITH object id
  3  (
  4  'DATABASE_SUMMARY'
  5  )
  6  AS SELECT xdb_configuration.getDatabaseSummary() FROM DUAL
  7  /

View created.

SQL> 
SQL> 
SQL> ALTER PACKAGE XDB_CONFIGURATION compile
  2  /

Package altered.

SQL> 
SQL> 
SQL> ALTER VIEW database_summary COMPILE
  2  /

View altered.

SQL> 
SQL> 
SQL> GRANT SELECT ON database_summary TO PUBLIC
  2  /

Grant succeeded.

SQL> 
SQL> CREATE OR REPLACE TRIGGER no_dml_operations_allowed
  2  INSTEAD OF INSERT OR UPDATE OR DELETE ON database_summary
  3  BEGIN
  4   NULL;
  5  END;
  6  /

Trigger created.

SQL> 
SQL> 
SQL> CREATE OR REPLACE PUBLIC SYNONYM XDB_CONFIGURATION FOR XDB_CONFIGURATION
  2  /

Synonym created.

SQL> GRANT EXECUTE ON XDB_CONFIGURATION TO PUBLIC
  2  /

Grant succeeded.

SQL> CALL XDB_CONFIGURATION.folderDatabaseSummary()
  2  /

Call completed.

SQL> ALTER SESSION SET current_schema = SYS
  2  /

Session altered.

SQL> @?/demo/schema/order_entry/xdbUtilities
SQL> Rem
SQL> Rem $Header: xdbUtilities.sql 23-sep-2004.13:45:33 cbauwens Exp $
SQL> Rem
SQL> Rem xdbUtilities.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2004, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      xdbUtilities.sql - Collection of stored objects to support xml db schema
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      .
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      XDB_UTILITES should be created as XDB
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cbauwens	09/23/04 - cbauwens_bug3031915
SQL> Rem    cbauwens	03/16/04 - Created
SQL> 
SQL> 
SQL> ALTER SESSION SET current_schema = XDB
  2  /

Session altered.

SQL> 
SQL> CREATE OR REPLACE FUNCTION XMLROOT (XML XMLType, PI VARCHAR2 DEFAULT NULL)
  2  RETURN XMLType DETERMINISTIC
  3  IS
  4    tempCLOB CLOB;
  5    tempXML XMLType;
  6  BEGIN
  7    SELECT XMLParse(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?>' || PI ||
  8  		       XML.getClobVal() WELLFORMED)
  9  	 INTO tempXML
 10  	 FROM DUAL;
 11    RETURN tempXML;
 12  END;
 13  /

Function created.

SQL> SELECT * FROM all_errors WHERE owner = 'XDB'
  2  /

no rows selected

SQL> CREATE OR REPLACE PUBLIC SYNONYM XMLROOT FOR XMLROOT
  2  /

Synonym created.

SQL> GRANT EXECUTE ON XMLROOT TO PUBLIC
  2  /

Grant succeeded.

SQL> CREATE OR REPLACE PACKAGE xdb_namespaces
  2  AS
  3  	 RESOURCE_NAMESPACE CONSTANT VARCHAR2(128) :=
  4  	   'http://xmlns.oracle.com/xdb/XDBResource.xsd';
  5  	 METADATA_NAMESPACE CONSTANT VARCHAR2(128) :=
  6  	   'http://xmlns.oracle.com/xdb/userMetaData';
  7  	 RESOURCE_PREFIX_R  CONSTANT VARCHAR2(128) :=
  8  	   'xmlns:r="'	|| RESOURCE_NAMESPACE || '"';
  9  	 METADATA_PREFIX_M  CONSTANT VARCHAR2(128) :=
 10  	   'xmlns:m="'	|| METADATA_NAMESPACE || '"';
 11  END;
 12  /

Package created.

SQL> SELECT * FROM all_errors WHERE owner = 'XDB'
  2  /

no rows selected

SQL> CREATE OR REPLACE PUBLIC SYNONYM XDB_NAMESPACES FOR XDB_NAMESPACES
  2  /

Synonym created.

SQL> GRANT EXECUTE ON XDB_NAMESPACES TO PUBLIC
  2  /

Grant succeeded.

SQL> CREATE OR REPLACE PACKAGE xdb_DOM_helper
  2  AS
  3    FUNCTION boolean_to_varchar(input BOOLEAN) RETURN VARCHAR2 DETERMINISTIC;
  4    FUNCTION raw_to_varchar(input RAW) RETURN VARCHAR2 DETERMINISTIC;
  5    FUNCTION varchar_to_boolean(input VARCHAR2) RETURN BOOLEAN DETERMINISTIC;
  6    FUNCTION raw_to_boolean(input RAW) RETURN BOOLEAN DETERMINISTIC;
  7    FUNCTION boolean_to_raw(input BOOLEAN) RETURN RAW DETERMINISTIC;
  8    FUNCTION varchar_to_raw(input VARCHAR2) RETURN RAW DETERMINISTIC;
  9    FUNCTION getTextNodeValue(parent DBMS_XMLDOM.DOMELEMENT, child VARCHAR2)
 10  	 RETURN VARCHAR2 DETERMINISTIC;
 11    FUNCTION getBooleanValue(parent DBMS_XMLDOM.DOMELEMENT, child VARCHAR2)
 12  	 RETURN RAW DETERMINISTIC;
 13    FUNCTION appendElement(Parent XMLType, Child XMLType)
 14  	 RETURN XMLType DETERMINISTIC;
 15  END;
 16  /

Package created.

SQL> 
SQL> 
SQL> SELECT * FROM all_errors WHERE owner = 'XDB'
  2  /

no rows selected

SQL> CREATE OR REPLACE PACKAGE BODY xdb_DOM_helper
  2  AS
  3  FUNCTION raw_to_varchar(input RAW)
  4  RETURN VARCHAR2 DETERMINISTIC
  5  IS
  6  BEGIN
  7    IF (input = hexToRaw('01')) THEN
  8  	 RETURN 'TRUE';
  9    END IF;
 10    IF (input = hexToRaw('00')) THEN
 11  	 RETURN 'FALSE';
 12    END IF;
 13    RETURN NULL;
 14  END;
 15  
 16  FUNCTION boolean_to_varchar(input BOOLEAN)
 17  RETURN VARCHAR2 DETERMINISTIC
 18  IS
 19  BEGIN
 20    IF (input = TRUE) THEN
 21  	 RETURN 'TRUE';
 22    END IF;
 23    IF (input =FALSE) THEN
 24  	 RETURN 'FALSE';
 25    END IF;
 26    RETURN NULL;
 27  END;
 28  
 29  FUNCTION varchar_to_raw(input VARCHAR2)
 30  RETURN RAW DETERMINISTIC
 31  IS
 32  BEGIN
 33    IF (upper(input) IN ('TRUE', 'Y', 'YES', '1')) THEN
 34  	 RETURN hexToRaw('01');
 35    END IF;
 36    IF (upper(input) IN ('FALSE', 'N', 'NO', '0')) THEN
 37  	 RETURN hexToRaw('00');
 38    END IF;
 39    RETURN NULL;
 40  END;
 41  
 42  FUNCTION boolean_to_raw(input BOOLEAN)
 43  RETURN RAW DETERMINISTIC
 44  IS
 45  BEGIN
 46    IF (input = TRUE) THEN
 47  	 RETURN hexToRaw('01');
 48    END IF;
 49    IF (input = FALSE) THEN
 50  	 RETURN hexToRaw('00');
 51    END IF;
 52    RETURN NULL;
 53  END;
 54  
 55  FUNCTION varchar_to_boolean(input VARCHAR2)
 56  RETURN BOOLEAN DETERMINISTIC
 57  IS
 58  BEGIN
 59    IF (upper(input) IN ('TRUE', 'Y', 'YES', '1')) THEN
 60  	 RETURN TRUE;
 61    END IF;
 62    IF (upper(input) IN ('FALSE', 'N', 'NO', '0')) THEN
 63  	 RETURN FALSE;
 64    END IF;
 65    RETURN NULL;
 66  END;
 67  
 68  FUNCTION raw_to_boolean(input RAW)
 69  RETURN BOOLEAN DETERMINISTIC
 70  IS
 71  BEGIN
 72    IF (input = hexToRaw('01')) THEN
 73  	 RETURN TRUE;
 74    END IF;
 75    IF (input = hexToRaw('00')) THEN
 76  	 RETURN FALSE;
 77    END IF;
 78    RETURN NULL;
 79  END;
 80  
 81  FUNCTION getBooleanValue(parent DBMS_XMLDOM.DOMELEMENT, child VARCHAR2)
 82  RETURN RAW DETERMINISTIC
 83  AS
 84  BEGIN
 85    RETURN varchar_to_raw(getTextNodeValue(parent, child));
 86  END;
 87  
 88  FUNCTION getTextNodeValue(parent DBMS_XMLDOM.DOMELEMENT, child VARCHAR2)
 89  RETURN VARCHAR2 DETERMINISTIC
 90  AS
 91    nodeList      DBMS_XMLDOM.DOMNODELIST;
 92    childNode     DBMS_XMLDOM.DOMNODE;
 93    value	     VARCHAR2(2048) := NULL;
 94  BEGIN
 95    nodeList := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(parent, child);
 96    IF (DBMS_XMLDOM.getlength(nodeList) > 0) THEN
 97  	 childNode := DBMS_XMLDOM.ITEM(nodeList, 0);
 98  	 childNode := DBMS_XMLDOM.GETFIRSTCHILD(childNode);
 99  	 value	   := DBMS_XMLDOM.GETNODEVALUE(childNode);
100    END IF;
101    RETURN value;
102  END;
103  
104  FUNCTION appendElement(PARENT XMLType, Child XMLType)
105  RETURN XMLType DETERMINISTIC
106  IS
107    PARENT_DOCUMENT	  DBMS_XMLDOM.DOMDOCUMENT;
108    PARENT_ROOTNODE	  DBMS_XMLDOM.DOMNODE;
109  
110    CHILD_DOCUMENT	  DBMS_XMLDOM.DOMDOCUMENT;
111    CHILD_ROOTNODE	  DBMS_XMLDOM.DOMNODE;
112  BEGIN
113    PARENT_DOCUMENT := DBMS_XMLDOM.newDOMDocument(PARENT);
114    PARENT_ROOTNODE :=
115  	 DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement(PARENT_DOCUMENT));
116  
117    CHILD_DOCUMENT  := DBMS_XMLDOM.newDOMDocument(CHILD);
118    CHILD_ROOTNODE  :=
119  	 DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement(CHILD_DOCUMENT));
120  
121  -- Comment out the following line if working with a pre 10g database...
122  
123    CHILD_ROOTNODE  := DBMS_XMLDOM.importNode(PARENT_DOCUMENT, CHILD_ROOTNODE, TRUE);
124    PARENT_ROOTNODE := DBMS_XMLDOM.appendChild(PARENT_ROOTNODE, CHILD_ROOTNODE);
125    RETURN PARENT;
126  END;
127  
128  END;
129  /

Package body created.

SQL> 
SQL> 
SQL> 
SQL> SELECT * FROM all_errors WHERE owner = 'XDB'
  2  /

no rows selected

SQL> CREATE OR REPLACE PUBLIC SYNONYM XDB_DOM_HELPER FOR XDB_DOM_HELPER
  2  /

Synonym created.

SQL> GRANT EXECUTE ON XDB_DOM_HELPER TO PUBLIC
  2  /

Grant succeeded.

SQL> CREATE OR REPLACE PACKAGE XDB_UTILITIES
  2  AUTHID CURRENT_USER
  3  AS
  4  	FUNCTION getBinaryContent(file BFILE) RETURN BLOB;
  5  	FUNCTION getBinaryContent(filename VARCHAR2,
  6  				  directoryName VARCHAR2 DEFAULT USER)
  7  	  RETURN BLOB;
  8  	FUNCTION getBinaryContent(file BFILE, tempBLOB IN OUT BLOB) RETURN BLOB;
  9  	FUNCTION getBinaryContent(filename VARCHAR2,
 10  				  directoryName VARCHAR2 DEFAULT USER,
 11  				  tempBLOB IN OUT BLOB)
 12  	  RETURN BLOB;
 13  
 14  	FUNCTION getFileContent(file BFILE, charset VARCHAR2 DEFAULT 'WE8MSWIN1252')
 15  	  RETURN CLOB;
 16  	FUNCTION getFileContent(filename VARCHAR2,
 17  				directoryName VARCHAR2 DEFAULT USER,
 18  				charset VARCHAR2 DEFAULT 'WE8MSWIN1252')
 19  	  RETURN CLOB;
 20  	FUNCTION getFileContent(file BFILE,
 21  				charset VARCHAR2 DEFAULT 'WE8MSWIN1252',
 22  				tempCLOB IN OUT CLOB)
 23  	  RETURN CLOB;
 24  	FUNCTION getFileContent(filename VARCHAR2,
 25  				directoryName VARCHAR2 DEFAULT USER,
 26  				charset VARCHAR2 DEFAULT 'WE8MSWIN1252',
 27  				tempCLOB IN OUT CLOB)
 28  	  RETURN CLOB;
 29  
 30  	PROCEDURE createHomeFolder(userName VARCHAR2);
 31  	PROCEDURE createDirectoryTree(path VARCHAR2);
 32  	PROCEDURE uploadFiles(file_list VARCHAR2 DEFAULT 'ls.xml',
 33  			      upload_directory_name VARCHAR2 DEFAULT USER,
 34  			      repository_folder_path VARCHAR2 DEFAULT '/public',
 35  			      batch_size NUMBER DEFAULT 1);
 36  
 37  	PROCEDURE put_xml(XML XMLType);
 38  
 39  	PROCEDURE addUserMetaDataNode(OID VARCHAR2);
 40  
 41  	PROCEDURE renameCollectionTable (xmltable VARCHAR2,
 42  					 xpath VARCHAR2,
 43  					 collection_table_name VARCHAR2);
 44  
 45  END XDB_UTILITIES;
 46  /

Package created.

SQL> 
SQL> 
SQL> SELECT * FROM all_errors WHERE owner = 'XDB'
  2  /

no rows selected

SQL> CREATE OR REPLACE PACKAGE BODY XDB_UTILITIES
  2  AS
  3  --
  4  -- When using getFileContent(), the application must explicitly free
  5  -- the CLOB that the function returns
  6  --
  7  FUNCTION getBinaryContent(file BFILE, tempBLOB IN OUT BLOB)
  8  RETURN BLOB
  9  IS
 10    targetFile      BFILE;
 11  
 12    dest_offset     NUMBER :=  1;
 13    src_offset      NUMBER := 1;
 14    lang_context    NUMBER := 0;
 15    conv_warning    NUMBER := 0;
 16    BEGIN
 17  	 targetFile := file;
 18  	 IF (tempBLOB IS NULL) THEN
 19  	   DBMS_LOB.createTemporary(tempBLOB, true, DBMS_LOB.SESSION);
 20  	 ELSE
 21  	   DBMS_LOB.trim(tempBLOB, 0);
 22  	 END IF;
 23  	 DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
 24  	 DBMS_LOB.loadBlobfromFile(tempBLOB,
 25  				   targetFile,
 26  				   DBMS_LOB.getLength(targetFile),
 27  				   dest_offset,
 28  				   src_offset);
 29  	 DBMS_LOB.fileclose(targetFile);
 30  	 RETURN tempBLOB;
 31  END;
 32  
 33  
 34  FUNCTION getBinaryContent(file BFILE)
 35  RETURN BLOB
 36  IS
 37    tempBLOB BLOB := NULL;
 38  BEGIN
 39    RETURN getBinaryContent(file, tempBLOB);
 40  END;
 41  
 42  FUNCTION getBinaryContent(filename VARCHAR2,
 43  			       directoryName VARCHAR2 DEFAULT USER,
 44  			       tempBLOB IN OUT BLOB)
 45  RETURN BLOB
 46  IS
 47  	file		BFILE := bfilename(directoryName, filename);
 48  BEGIN
 49    RETURN getBinaryContent(file, tempBLOB);
 50  END;
 51  
 52  FUNCTION getBinaryContent(filename VARCHAR2,
 53  			       directoryName VARCHAR2 DEFAULT USER)
 54  RETURN BLOB
 55  IS
 56  	tempBLOB BLOB := NULL;
 57  BEGIN
 58    RETURN getBinaryContent(filename, directoryName, tempBLOB);
 59  END;
 60  
 61  FUNCTION getFileContent(file BFILE,
 62  			     charset VARCHAR2 DEFAULT 'WE8MSWIN1252',
 63  			     tempCLOB IN OUT CLOB)
 64  RETURN CLOB
 65  IS
 66    targetFile      BFILE;
 67  
 68    dest_offset     NUMBER :=  1;
 69    src_offset      NUMBER := 1;
 70    lang_context    NUMBER := 0;
 71    conv_warning    NUMBER := 0;
 72    BEGIN
 73  	 targetFile := file;
 74  	 IF (tempCLOB IS NULL) THEN
 75  	   DBMS_LOB.createTemporary(tempCLOB, true, DBMS_LOB.SESSION);
 76  	 ELSE
 77  	   DBMS_LOB.trim(tempCLOB, 0);
 78  	 END IF;
 79  	 DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
 80  	 DBMS_LOB.loadClobfromFile(tempCLOB,
 81  				   targetFile,
 82  				   DBMS_LOB.getLength(targetFile),
 83  				   dest_offset,
 84  				   src_offset,
 85  				   nls_charset_id(charset),
 86  				   lang_context,
 87  				   conv_warning);
 88  	 DBMS_LOB.fileclose(targetFile);
 89  	 RETURN tempCLOB;
 90  END;
 91  
 92  FUNCTION getFileContent(file BFILE,
 93  			     charset VARCHAR2 DEFAULT 'WE8MSWIN1252')
 94  RETURN CLOB
 95  IS
 96    tempCLOB CLOB := NULL;
 97  BEGIN
 98    RETURN getFileContent(file, charset, tempCLOB);
 99  END;
100  --
101  -- When using getFileContent() the application must explicitly free
102  -- the CLOB that the function returns
103  --
104  FUNCTION getFileContent(filename VARCHAR2,
105  			     directoryName VARCHAR2 DEFAULT USER,
106  			     charset VARCHAR2 DEFAULT 'WE8MSWIN1252',
107  			     tempCLOB IN OUT CLOB)
108  RETURN CLOB
109  IS
110  	file		BFILE := bfilename(directoryName, filename);
111  BEGIN
112    RETURN getFileContent(file, charset, tempCLOB);
113  END;
114  
115  FUNCTION getFileContent(filename VARCHAR2,
116  			     directoryName VARCHAR2 DEFAULT USER,
117  			     charset VARCHAR2 DEFAULT 'WE8MSWIN1252')
118  RETURN CLOB
119  IS
120  	tempCLOB CLOB := NULL;
121  BEGIN
122    RETURN getFileContent(filename, directoryName, charset, tempCLOB);
123  END;
124  
125  PROCEDURE createHomeFolder(userName VARCHAR2)
126  AS
127    targetResource VARCHAR2(256);
128    realUserName VARCHAR2(64);
129    result BOOLEAN;
130  BEGIN
131  
132    realUserName := upper(userName);
133    targetResource := '/home';
134  
135    IF (NOT DBMS_XDB.existsResource(targetResource)) THEN
136  	 result := DBMS_XDB.createFolder(targetResource);
137  	 DBMS_XDB.setAcl(targetResource, '/sys/acls/bootstrap_acl.xml');
138    END IF;
139  
140    targetResource := '/home/' || realUserName;
141  
142    IF (NOT DBMS_XDB.existsResource(targetResource)) THEN
143  	 result := DBMS_XDB.createFolder(targetResource);
144    END IF;
145  
146    DBMS_XDB.setAcl(targetResource, '/sys/acls/all_owner_acl.xml');
147  
148    UPDATE RESOURCE_VIEW
149  	      SET res = updateXml(res, '/Resource/Owner/text()', realUserName)
150    WHERE equals_path(res, targetResource) = 1;
151  
152  END;
153  
154  PROCEDURE createDirectoryTree(path VARCHAR2)
155  AS
156    pathSeperator VARCHAR2(1) := '/';
157    parentFolderPath VARCHAR2(256);
158    result BOOLEAN;
159    folderExists NUMBER(6):= 1;
160  BEGIN
161    -- DBMS_OUTPUT.put_line('Processing ' || path);
162    SELECT count(*)
163    INTO folderExists
164    FROM RESOURCE_VIEW
165    WHERE equals_path(RES, path) = 1;
166    -- DBMS_OUTPUT.put_line('FolderExists = ' || folderExists);
167    IF (folderExists = 0) THEN
168  	 parentFolderPath := substr(path, 1, instr(path, pathSeperator, -1) -1);
169  	 -- DBMS_OUTPUT.put_line('FolderExists = ' || FolderExists);
170  	 createDirectoryTree(parentFolderPath);
171  	 result := DBMS_XDB.createFolder(path);
172    END IF;
173  END;
174  
175  PROCEDURE uploadFiles(file_list VARCHAR2 DEFAULT 'ls.xml',
176  			   upload_directory_name VARCHAR2 DEFAULT USER,
177  			   repository_folder_path VARCHAR2 DEFAULT '/public',
178  			   batch_size NUMBER DEFAULT 1)
179  AS
180    pathSeperator VARCHAR2(1) := '/';
181  
182    directory_path	   VARCHAR2(256);
183  
184    subdirectory_path   VARCHAR2(256);
185    target_folder_path  VARCHAR2(256);
186    target_file_path    VARCHAR2(256);
187    target_file_name    VARCHAR2(256);
188    resource_path	   VARCHAR2(256);
189    last_folder_path    VARCHAR2(256) := ' ';
190  
191    sqlStatement	   VARCHAR2(256);
192  
193    filelist_xml	   XMLTYPE := XMLType(bfilename(upload_directory_name,
194  							file_list),
195  					      nls_charset_id('AL32UTF8'));
196    content_xml	   XMLType;
197  
198    result		   BOOLEAN;
199  
200    filecount	   BINARY_INTEGER := 0;
201  
202    filelist_DOM    DBMS_XMLDOM.DOMDOCUMENT;
203    files_nl        DBMS_XMLDOM.DOMNODELIST;
204    directory_nl    DBMS_XMLDOM.DOMNODELIST;
205    filename_nl     DBMS_XMLDOM.DOMNODELIST;
206    files_node      DBMS_XMLDOM.DOMNODE;
207    directory_node  DBMS_XMLDOM.DOMNODE;
208    file_node       DBMS_XMLDOM.DOMNODE;
209    text_node       DBMS_XMLDOM.DOMNODE;
210    encoding_attr   DBMS_XMLDOM.DOMATTR;
211    replace_attr    DBMS_XMLDOM.DOMATTR;
212  
213    path	       VARCHAR2(256);
214    file_name       VARCHAR2(256);
215    encoding_text   VARCHAR2(32);
216    attr_value      VARCHAR2(256);
217    replace_option  BOOLEAN;
218    replace_default BOOLEAN;
219  
220    debug_buffer    VARCHAR2(255);
221  BEGIN
222  
223    -- Create the set of Folders in the XDB Repository
224  
225    filelist_DOM := DBMS_XMLDOM.newDOMDocument(filelist_xml);
226  
227    directory_nl := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(filelist_DOM, 'directory');
228    FOR i IN 0 .. (DBMS_XMLDOM.GETLENGTH(directory_nl) - 1) LOOP
229  	 directory_node := DBMS_XMLDOM.ITEM(directory_nl, i);
230  	 text_node	:= DBMS_XMLDOM.GETFIRSTCHILD(directory_node);
231  	 directory_path := DBMS_XMLDOM.GETNODEVALUE(text_node);
232  	 directory_path := repository_folder_path || directory_path;
233  	 createDirectoryTree(directory_path);
234  
235    END LOOP;
236  
237    -- Find the Local File System Path to the target Directory.
238  
239    SELECT DIRECTORY_PATH
240  	 INTO directory_path
241  	 FROM ALL_DIRECTORIES
242  	 WHERE DIRECTORY_NAME = upload_directory_name;
243  
244    -- DBMS_OUTPUT.put_line('OS Root =  ' || directory_path);
245  
246    -- Load the Resources into the XML DB Repository
247  
248    files_nl 	  := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(filelist_DOM, 'files');
249    files_node	  := DBMS_XMLDOM.ITEM(files_nl, 0);
250  
251    replace_default	  := FALSE;
252    replace_attr := DBMS_XMLDOM.getAttributeNode(DBMS_XMLDOM.MAKEELEMENT(files_node),
253  						    'replace');
254  
255    IF NOT (DBMS_XMLDOM.ISNULL(replace_attr)) THEN
256  	 replace_default :=
257  	   xdb_dom_helper.varchar_to_boolean(DBMS_XMLDOM.getVALUE(replace_attr));
258    END IF;
259  
260    filename_nl := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(filelist_DOM, 'file');
261    FOR i IN 0 .. (DBMS_XMLDOM.GETLENGTH(filename_nl) - 1) LOOP
262  	 file_node	    := DBMS_XMLDOM.ITEM(filename_nl, i);
263  
264  	 text_node	    := DBMS_XMLDOM.GETFIRSTCHILD(file_node);
265  	 target_file_path   := DBMS_XMLDOM.GETNODEVALUE(text_node);
266  	 -- DBMS_OUTPUT.put_line('Source =  ' || target_file_path);
267  	 target_file_name   := substr(target_file_path,
268  				      instr(target_file_path, pathSeperator, -1) +1);
269  
270  	 -- DBMS_OUTPUT.put_line('File =  ' || target_file_name);
271  
272  	 target_folder_path := substr(target_file_path, 1,
273  				      instr(target_file_path, pathSeperator, -1));
274  	 target_folder_path := substr(target_folder_path,
275  				      instr(target_folder_path, pathSeperator));
276  	 target_folder_path := substr(target_folder_path,
277  				      1,
278  				      length(target_folder_path)-1);
279  
280  	 encoding_attr :=
281  	   DBMS_XMLDOM.getAttributeNode(DBMS_XMLDOM.MAKEELEMENT(file_node),
282  					'encoding');
283  	 encoding_text	    := 'AL32UTF8';
284  	 IF NOT (DBMS_XMLDOM.ISNULL(encoding_attr)) THEN
285  	   encoding_text    := DBMS_XMLDOM.getValue(encoding_attr);
286  	   DBMS_OUTPUT.put_line('Encoding for ' || target_file_name || ' =  ' ||
287  				encoding_text);
288  	 END IF;
289  
290  	 replace_attr :=
291  	   DBMS_XMLDOM.getAttributeNode(DBMS_XMLDOM.MAKEELEMENT(file_node),
292  						      'Replace');
293  	 replace_option     := replace_default;
294  	 IF NOT (DBMS_XMLDOM.ISNULL(replace_attr)) THEN
295  	   replace_option :=
296  	     xdb_dom_helper.varchar_to_boolean(DBMS_XMLDOM.getValue(replace_attr));
297  	 END IF;
298  
299  	 IF (last_folder_path != target_folder_path) THEN
300  	   subdirectory_path := directory_path || target_folder_path;
301  	   -- DBMS_OUTPUT.put_line('Directory =  ' || subdirectory_path);
302  	   sqlStatement :=
303  	     'CREATE OR REPLACE DIRECTORY subdir AS ''' || subdirectory_path || '''';
304  	   execute immediate sqlStatement;
305  	   last_folder_path := target_folder_path;
306  	 END IF;
307  
308  	 content_xml := XMLType(bfilename('SUBDIR', target_file_name),
309  				nls_charset_id(encoding_text));
310  	 resource_path :=
311  	   repository_folder_path || target_folder_path || '/' || target_file_name;
312  	 -- DBMS_OUTPUT.put_line('Target = ' || resource_path);
313  
314  	 IF (replace_option AND DBMS_XDB.existsResource(resource_path)) THEN
315  	   DBMS_XDB.deleteResource(resource_path);
316  	 END IF;
317  
318  	 result := DBMS_XDB.createResource(resource_path, content_xml);
319  
320  	 filecount := filecount + 1;
321  
322  	 IF (filecount = BATCH_SIZE) THEN
323  	   filecount := 0;
324  	   COMMIT;
325  	 END IF;
326    END LOOP;
327  END;
328  
329  PROCEDURE put_xml(XML XMLType)
330  AS
331    buffer CLOB;
332    offset BINARY_INTEGER := 1;
333    maxLength BINARY_INTEGER;
334    endofLine BINARY_INTEGER := 1;
335    linesize BINARY_INTEGER;
336  BEGIN
337    buffer	 := xml.getClobVal();
338    maxLength := DBMS_LOB.getLength(buffer);
339    endofline := DBMS_LOB.instr(buffer, chr(10), offset, 1);
340    WHILE (endofLine > 0) LOOP
341  	  linesize  := endofline - offset;
342  	  IF (linesize > 255) THEN
343  	    DBMS_OUTPUT.put_line(DBMS_LOB.substr(buffer, 255, offset));
344  	    offset := offset + 255;
345  	  ELSE
346  	    DBMS_OUTPUT.put_line(DBMS_LOB.substr(buffer, linesize, offset));
347  	    offset := offset + linesize + 1;
348  	  END IF;
349  	  endofline := DBMS_LOB.instr(buffer, chr(10), offset, 1);
350    END LOOP;
351    linesize := maxLength - offset;
352    DBMS_OUTPUT.put_line(DBMS_LOB.substr(buffer, linesize+1, offset));
353  END;
354  
355  PROCEDURE addUserMetaDataNode(OID VARCHAR2)
356  AS
357    metadata_xml XMLType :=
358  	 XMLType('<m:UserMetaData ' || XDB_NAMESPACES.METADATA_PREFIX_M || '/>');
359  BEGIN
360    -- EXECUTE IMMEDIATE 'ALTER SESSION SET events=''19027 trace name context forever,level 4'' ';
361    UPDATE /*+ NO_TRIGGER */ XDB.XDB$RESOURCE r
362  	 SET r.xmldata.RESEXTRA = metadata_xml.getClobVal()
363  	 WHERE sys_nc_oid$ = hextoraw(OID)
364  	  AND existsNode(OBJECT_VALUE,
365  			 '/r:Resource/r:Contents',
366  			 XDB_NAMESPACES.RESOURCE_PREFIX_R)
367  	      = 1
368  	  AND existsNode(OBJECT_VALUE,
369  			 '/r:Resource/m:UserMetaData',
370  			 XDB_NAMESPACES.RESOURCE_PREFIX_R || ' ' ||
371  			 XDB_NAMESPACES.METADATA_PREFIX_M)
372  	       = 0;
373    -- EXECUTE IMMEDIATE 'ALTER SESSION SET events=''19027 trace name context forever,level 0'' ';
374  END;
375  
376  PROCEDURE renameCollectionTable (xmltable VARCHAR2,
377  				      xpath VARCHAR2,
378  				      collection_table_name VARCHAR2)
379  AS
380  	system_generated_name VARCHAR2(256);
381  	rename_statement VARCHAR2(4000);
382  BEGIN
383  
384  	SELECT TABLE_NAME
385  	  INTO system_generated_name
386  	  FROM ALL_NESTED_TABLES
387  	  WHERE PARENT_TABLE_NAME = xmltable
388  	    AND PARENT_TABLE_COLUMN = xpath
389  	    AND OWNER = USER;
390  
391  	rename_statement :=
392  	  'ALTER TABLE ' || USER || '."' || system_generated_name || '" RENAME TO "' ||
393  	  collection_table_name || '"';
394  	-- DBMS_OUTPUT.put_line(rename_statement);
395  	EXECUTE IMMEDIATE rename_statement;
396  
397  	BEGIN
398  	  SELECT INDEX_NAME
399  	    INTO system_generated_name
400  	    FROM ALL_INDEXES
401  	    WHERE TABLE_NAME = collection_table_name
402  	      AND INDEX_NAME LIKE 'SYS%IOT%'
403  	      AND OWNER = USER;
404  
405  	  rename_statement :=
406  	    'ALTER INDEX ' || USER || '."' || system_generated_name ||
407  	    '" RENAME TO "' ||collection_table_name || '_DATA"';
408  	  -- DBMS_OUTPUT.put_line(rename_statement);
409  	  EXECUTE IMMEDIATE rename_statement;
410  	EXCEPTION
411  	  WHEN NO_DATA_FOUND THEN
412  	    NULL;
413  	END;
414  
415  	BEGIN
416  	  SELECT INDEX_NAME
417  	    INTO system_generated_name
418  	    FROM ALL_IND_COLUMNS
419  	    WHERE COLUMN_NAME = xpath
420  	      AND TABLE_NAME =	xmltable
421  	      AND TABLE_OWNER = USER;
422  
423  	  rename_statement :=
424  	    'ALTER INDEX ' || USER || '."' || system_generated_name ||
425  	    '" RENAME TO "' || collection_table_name || '_MEMBERS"';
426  	  -- DBMS_OUTPUT.put_line(rename_statement);
427  	  EXECUTE IMMEDIATE rename_statement;
428  	EXCEPTION
429  	  WHEN NO_DATA_FOUND THEN
430  	    NULL;
431  	END;
432  END;
433  
434  END XDB_UTILITIES;
435  /

Package body created.

SQL> 
SQL> 
SQL> SELECT * FROM ALL_ERRORS WHERE owner = 'XDB'
  2  /

no rows selected

SQL> GRANT EXECUTE ON XDB_UTILITIES TO PUBLIC
  2  /

Grant succeeded.

SQL> CREATE OR REPLACE PUBLIC SYNONYM XDB_UTILITIES FOR XDB_UTILITIES
  2  /

Synonym created.

SQL> --
SQL> -- Install the XDB_TOOLS package
SQL> --
SQL> CREATE OR REPLACE PACKAGE XDB_TOOLS
  2  AUTHID DEFINER
  3  AS
  4  	PROCEDURE touchResource(path VARCHAR2);
  5  	PROCEDURE releaseDAVLocks;
  6  END XDB_TOOLS;
  7  /

Package created.

SQL> 
SQL> 
SQL> SELECT * FROM ALL_ERRORS WHERE owner = 'XDB'
  2  /

no rows selected

SQL> CREATE OR REPLACE PACKAGE BODY XDB_TOOLS
  2  AS
  3  PROCEDURE touchResource(path VARCHAR2)
  4  AS
  5  BEGIN
  6  	 UPDATE XDB$RESOURCE r
  7  	 SET r.XMLDATA.MODIFICATIONDATE = SYSTIMESTAMP
  8  	 WHERE ROWID = (SELECT ROWID FROM RESOURCE_VIEW
  9  			WHERE equals_path(res, path) = 1);
 10  END;
 11  
 12  PROCEDURE releaseDAVLocks
 13  AS
 14  BEGIN
 15  	 DELETE FROM XDB$NLOCKS;
 16  	 UPDATE XDB.xdb$resource r
 17  	 SET r.XMLDATA.LOCKS = NULL
 18  	 WHERE r.XMLDATA.LOCKS IS NOT NULL;
 19  END;
 20  
 21  END XDB_TOOLS;
 22  /

Package body created.

SQL> 
SQL> 
SQL> SELECT * FROM ALL_ERRORS WHERE owner = 'XDB'
  2  /

no rows selected

SQL> CREATE OR REPLACE PUBLIC SYNONYM XDB_TOOLS FOR XDB_TOOLS
  2  /

Synonym created.

SQL> GRANT EXECUTE ON XDB_TOOLS TO PUBLIC
  2  /

Grant succeeded.

SQL> --
SQL> -- End xdbUtilities
SQL> --
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> -- Create directory object, instantiated by createUser.sql.sbs
SQL>  @?/demo/schema/order_entry/createUser
SQL> Rem
SQL> Rem $Header: createUser.sql.sbs 23-sep-2004.13:45:32 cbauwens Exp $
SQL> Rem
SQL> Rem coe_xml.sql.sbs
SQL> Rem
SQL> Rem Copyright (c) 2002, 2004, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      createUser.sql.sbs - Create a user, directory, and XDB folder
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      .
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      Instantiates createUser.sql. Sets s_oePath
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cbauwens	09/23/04 - cbauwens_bug3031915
SQL> Rem    cbauwens	03/16/04 - Created
SQL> 
SQL> 
SQL> 
SQL> DECLARE
  2    targetFolder VARCHAR2(256) := '/home';
  3    result boolean;
  4  BEGIN
  5    IF (DBMS_XDB.existsResource(targetFolder)) THEN
  6  	 DBMS_XDB.deleteResource(targetFolder, DBMS_XDB.DELETE_RECURSIVE);
  7    END IF;
  8  
  9    result := DBMS_XDB.createFolder(targetFolder);
 10    targetFolder := targetFolder || '/OE';
 11    result := DBMS_XDB.createFolder(targetFolder);
 12    DBMS_XDB.setAcl(targetFolder, '/sys/acls/all_all_acl.xml');
 13    xdb_utilities.createHomeFolder('OE');
 14  END;
 15  /
DECLARE
*
ERROR at line 1:
ORA-31018: Error deleting XML document 
ORA-00942: table or view does not exist 
ORA-06512: at "XDB.DBMS_XDB", line 331 
ORA-06512: at line 6 


SQL> 
SQL> CONNECT OE/&pass_oe
Connected.
SQL> 
SQL> --Create Oracle directory object
SQL> CREATE OR REPLACE DIRECTORY XMLDIR as 'D:\oracle\product\11.1.0\db_1\demo\schema\order_entry\'
  2  /

Directory created.

SQL> 
SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> CONNECT oe/&pass_oe;
Connected.
SQL> 
SQL> --
SQL> -- set . and , as decimal point and thousand separator for the session
SQL> -- as the unit prices are hard coded, which might cause NLS issues
SQL> --
SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,';

Session altered.

SQL> 
SQL> -- Create folders and load
SQL>  @?/demo/schema/order_entry/xdb03usg
SQL> Rem
SQL> Rem $Header: xdb03usg.sql 25-may-2005.16:42:54 cbauwens Exp $
SQL> Rem
SQL> Rem coe_xml.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      xdb03usg.sql - Create XML DB data for user OE
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      .
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      .
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cbauwens	05/25/05 - rename nested tables
SQL> Rem    cbauwens	09/23/04 - cbauwens_bug3031915
SQL> Rem    cbauwens	03/16/04 - Created
SQL> 
SQL> --
SQL> --
SQL> -- Create Repository Folder Hierarchy
SQL> --
SQL> @?/demo/schema/order_entry/createFolders.sql
SQL> Rem
SQL> Rem $Header: createFolders.sql 23-sep-2004.13:45:32 cbauwens Exp $
SQL> Rem
SQL> Rem createFolders.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2004, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      createFolders.sql - Create Repository Folder Hierarchy
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      .
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      .
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cbauwens	09/23/04 - cbauwens_bug3031915
SQL> Rem    cbauwens	03/16/04 - Created
SQL> 
SQL> DECLARE
  2    res BOOLEAN;
  3  BEGIN
  4    res := DBMS_XDB.createFolder('/home/OE/xsd');
  5    res := DBMS_XDB.createFolder('/home/OE/xsl');
  6    res := DBMS_XDB.createFolder('/home/OE/PurchaseOrders');
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-31003: Parent /home/OE/ already contains child entry xsd 
ORA-06512: at "XDB.DBMS_XDB", line 316 
ORA-06512: at line 4 


SQL> 
SQL> 
SQL> --
SQL> -- Load example documents into the XDB repository
SQL> --
SQL> @?/demo/schema/order_entry/createResources.sql
SQL> Rem
SQL> Rem $Header: createResources.sql 23-sep-2004.13:45:32 cbauwens Exp $
SQL> Rem
SQL> Rem createResources.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2004, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      createResources.sql - Load example documents into the XDB repository
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      .
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      .
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cbauwens	09/23/04 - cbauwens_bug3031915
SQL> Rem    cbauwens	03/16/04 - add empdept
SQL> Rem    cbauwens	03/14/04 - Created
SQL> 
SQL> 
SQL> DECLARE
  2    res BOOLEAN;
  3  BEGIN
  4    res := DBMS_XDB.createResource('/home/OE/purchaseOrder.xsd',
  5  				      bfilename('XMLDIR', 'purchaseOrder.xsd'),
  6  				      nls_charset_id('AL32UTF8'));
  7    res := DBMS_XDB.createResource('/home/OE/purchaseOrder.xsl',
  8  				      bfilename('XMLDIR', 'purchaseOrder.xsl'),
  9  				      nls_charset_id('AL32UTF8'));
 10  
 11  
 12    res := DBMS_XDB.createResource('/home/OE/xsl/empdept.xsl',
 13  				      bfilename('XMLDIR', 'empdept.xsl'),
 14  				      nls_charset_id('AL32UTF8'));
 15  
 16  END;
 17  /
DECLARE
*
ERROR at line 1:
ORA-31003: Parent /home/OE/ already contains child entry purchaseOrder.xsd 
ORA-06512: at "XDB.DBMS_XDB", line 250 
ORA-06512: at "XDB.DBMS_XDB", line 291 
ORA-06512: at line 4 


SQL> 
SQL> 
SQL> --
SQL> --Register schema
SQL> --
SQL> BEGIN
  2    DBMS_XMLSCHEMA.registerSchema('http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',
  3  				     XDBURIType('/home/OE/purchaseOrder.xsd').getClob(),
  4  				     TRUE,
  5  				     TRUE,
  6  				     FALSE,
  7  				     TRUE);
  8  END;
  9  /
BEGIN
*
ERROR at line 1:
ORA-31085: schema 
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd" already 
registered 
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 20 
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 31 
ORA-06512: at line 2 


SQL> 
SQL> --
SQL> --Rename the cryptic nested tables
SQL> --
SQL> call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."LINEITEMS"."LINEITEM"','LINEITEM_TABLE')
  2  /
call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."LINEITEMS"."LINEITEM"','LINEITEM_TABLE')
     *
ERROR at line 1:
ORA-00955: name is already used by an existing object 
ORA-06512: at "XDB.XDB_UTILITIES", line 395 


SQL> call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."ACTIONS"."ACTION"','ACTION_TABLE')
  2  /
call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."ACTIONS"."ACTION"','ACTION_TABLE')
     *
ERROR at line 1:
ORA-00955: name is already used by an existing object 
ORA-06512: at "XDB.XDB_UTILITIES", line 395 


SQL> 
SQL> --
SQL> -- Upload the Directory containing the sample documents
SQL> --
SQL> BEGIN
  2   XDB_UTILITIES.uploadFiles('filelist.xml',
  3  				    'XMLDIR',
  4  				    '/home/OE/PurchaseOrders');
  5  END;
  6  /
BEGIN
*
ERROR at line 1:
ORA-31003: Parent /home/OE/PurchaseOrders/2002/Apr/ already contains child 
entry AMCEWEN-20021009123336171PDT.xml 
ORA-06512: at "XDB.DBMS_XDB", line 192 
ORA-06512: at "XDB.XDB_UTILITIES", line 318 
ORA-06512: at line 2 


SQL> 
SQL> 
SQL> --
SQL> -- CONNECT as SYS. Revoke "ANY" privs
SQL> --
SQL> 
SQL> CONNECT sys/&pass_sys AS SYSDBA;
Connected.
SQL> 
SQL> REVOKE create any directory FROM oe;

Revoke succeeded.

SQL> REVOKE drop any directory FROM oe;

Revoke succeeded.

SQL> REVOKE alter session FROM oe;

Revoke succeeded.

SQL> 
SQL> DROP PACKAGE xdb.xdb_configuration;

Package dropped.

SQL> DROP PACKAGE xdb.xdb_namespaces;

Package dropped.

SQL> DROP PACKAGE xdb.xdb_dom_helper;

Package dropped.

SQL> DROP PACKAGE xdb.xdb_utilities;

Package dropped.

SQL> DROP PACKAGE xdb.xdb_tools;

Package dropped.

SQL> DROP TRIGGER xdb.no_dml_operations_allowed;

Trigger dropped.

SQL> DROP VIEW	  xdb.database_summary;

View dropped.

SQL> 
SQL> 
SQL> CONNECT oe/&&pass_oe;
Connected.
SQL> 
SQL> spool off
