Syntax

DBMS_XMLSTORAGE_MANAGE.DISABLEINDEXESANDCONSTRAINTS (
   owner_name    IN  VARCHAR2 DEFAULT USER, 
   table_name    IN  VARCHAR2, 
   column_name   IN  VARCHAR2 DEFAULT NULL,
   clear         IN  BOOLEAN  DEFAULT FALSE);

Parameters

Usage Notes

Passing XMLTYPE tables

For XMLType tables, you must pass the XMLType table name on which the bulk load operation is to be performed. For XMLType columns, you must pass the relational table name and the corresponding XMLType column name.

Using clear to Enable and Disable Indexes and Constraints

Therefore, you must ensure that all disabled indexes and constraints are re-enabled on the table or column before you call the DISABLEINDEXESANDCONTRAINTS procedure with clear set to TRUE.

Ideally, it is recommended that you set clear set to TRUE for the first execution. For any subsequent executions (due to errors while disabling or enabling indexes) clear should be set to FALSE, the default value. Once you have successfully re-enabled all the indexes and constraints following the bulk load operation, you can call this procedure again with clear set to TRUE for the next bulk load operation.

Example

The following example illustrates the use of clear in the DISABLEINDEXESANDCONSTRAINTS procedure and the ENABLEINDEXESANDCONSTRAINTS Procedure.

First, add a not-NULL constraint on comment element of the PURCHASEORDER_TAB table:

ALTER TABLE PURCHASEORDER_TAB ADD CONSTRAINT c1 check            ("XMLDATA"."comment" IS NOT NULL);

Then, disable all the indexes and constraints by passing the clear as TRUE, by calling the DISABLEINDEXESANDCONSTRAINTS procedure:

BEGIN
     XDB.DBMS_XMLSTORAGE_MANAGE.DISABLEINDEXESANDCONSTRAINTS
                                ( USER,'PURCHASEORDER_TAB',NULL,TRUE );
END;
/
 

Next, perform a bulk load operation (such as datapump import) which violates constraint c1 in the ALTER table statement. This does not raise an error because the constraint is disabled:

host impdp orexample/orexample directory=dir dumpfile=dmp.txt
   tables=OREXAMPLE.PURCHASEORDER_TAB content = DATA_ONLY;
 

NOTE: To view the disabled constraints and indexes use:

SELECT constraint_name,table_name,status FROM all_constraints  
  WHERE owner = user;

Finally, try to enable the constraint using the ENABLEINDEXESANDCONSTRAINTS procedure. It raises an error because c1, the not null constraint, is violated by the bulk load operation:

BEGIN
     XDB.DBMS_XMLSTORAGE_MANAGE.ENABLEINDEXESANDCONSTRAINTS
                                         ( USER,'PURCHASEORDER_TAB');
END;
/

To disable all the indexes and constraints, again use DISABLEINDEXESANDCONSTRAINTS, but set clear= FALSE (because the ENABLEINDEXESANDCONSTRAINTS failed to complete successfully). Note: clear = FALSE by default, so we do not need to pass it explicitly in the next call.

BEGIN
     xdb.DBMS_XMLSTORAGE_MANAGE.DISABLEINDEXESANDCONSTRAINTS
                                ( USER,'PURCHASEORDER_TAB');
END;
/
 

Then, delete the incorrect rows entered into the table

DELETE FROM purchaseorder_tab p
     WHERE p.xmldata."comment" IS NULL;
 

Re-enable the indexes and constraints using ENABLEINDEXESANDCONSTRAINTS, which completes successfully.

BEGIN
     xdb.DBMS_XMLSTORAGE_MANAGE.ENABLEINDEXESANDCONSTRAINTS
                                         ( USER,'PURCHASEORDER_TAB');
END;
/

When column_name is passed, it does the same for this XMLType column.

Syntax

DBMS_XMLSTORAGE_MANAGE.ENABLEINDEXESANDCONSTRAINTS (
   owner_name   IN VARCHAR2 DEFAULT USER, 
   table_name   IN VARCHAR2, 
   column_name  IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

This procedure reverses DISABLEINDEXESANDCONSTRAINTS Procedure.

Example

See DISABLEINDEXESANDCONSTRAINTS Procedure

Syntax

DBMS_XMLSTORAGE_MANAGE.EXCHANGEPOSTPROC (
   owner_name   IN VARCHAR2 DEFAULT USER, 
   table_name   IN VARCHAR2);

Parameters

Syntax

DBMS_XMLSTORAGE_MANAGE.EXCHANGEPREPROC (
   owner_name   IN VARCHAR2 DEFAULT USER, 
   table_name   IN VARCHAR2);

Parameters

If the procedure creates multiple REF columns, it appends _1, _2, and so on to their names.

Syntax

DBMS_XMLSTORAGE_MANAGE.INDEXXMLREFERENCES (
   owner_name    IN VARCHAR2 DEFAULT USER, 
   table_name    IN VARCHAR2,
   column_name   IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

This procedure is only used if the REFs are scoped. See SCOPEXMLREFERENCES Procedure.

Indexed REFs lead to better performance when joins between the base table and a child table occur in the query plan.

• If the base table has a higher selectivity than the child table, there is no need to index the REFs.

• If the selectivity of the child table is higher than that of the base table and if no indexes are present, then the join of one row in the child table with the base table leads to a full table scan of the base table.

INDEXXMLREFERENCES does not index REFs recursively in child tables of a table it is called on. To do this, Oracle recommends calling the procedure from within a loop over the XML_OUT_OF_LINE_TABLES or XML_NESTED_TABLES view. This creates the index names from the current value of a column in the view.

An XPath expression specifies the collection table, starting from the XMLtype base table or an XMLType column of the base table.

This procedure provides the only way to derive a collection table name from the corresponding collection type name because there is no direct schema annotation for the purpose.

Syntax

DBMS_XMLSTORAGE_MANAGE.RENAMECOLLECTIONTABLE (
   owner_name             IN VARCHAR2 DEFAULT USER,
   table_name             IN VARCHAR2,
   column_name            IN VARCHAR2 DEFAULT NULL,
   xpath                  IN VARCHAR2,
   collection_table_name  IN VARCHAR2
   namespaces             IN VARCHAR2 default NULL);  // For release 11.2 only

Parameters

Usage Notes

Call this procedure after registering the XML schema.

The table name serves as a prefix to the index names.

Oracle recommends using this function because it makes query execution plans more readable.

Report errors that occur while this procedure runs to the user that called the procedure.

For Oracle Database 11g Release 2 (11.2) and higher, only, this function accepts XPath notation as well as DOT notation. If XPath notation is used, a namespaces parameter may also be required.

Example

The collection table name will be EMP_TAB_NAMELIST. You can verify this using SELECT * FROM user_nested_tables.

Using DOT Notation:

call  XDB.DBMS_XMLSTORAGE_MANAGE.RENAMECOLLECTIONTABLE (
            USER,
            'EMP_TAB',
            NULL,
            '"XMLDATA"."EMPLOYEE"."NAME"',
           ''EMP_TAB_NAMELIST); 

Using XPath Notation:

XPath notation is available with Oracle Database 11g Release 2 (11.2) and higher.

call  XDB.DBMS_XMLSTORAGE_MANAGE.RENAMECOLLECTIONTABLE (
               USER,
               'EMP_TAB',
               NULL,
               '/e:Employee/Name',
               'EMP_TAB_NAMELIST',
               '''http://www.oracle.com/emp.xsd'' as "e"'); 

Syntax

DBMS_XMLSTORAGE_MANAGE.SCOPEXMLREFERENCES;

Usage Notes

• If you have used SETOUTOFLINE Procedure in the DBMS_XMLSTORAGE_MANAGE package to avoid raising '1000 column limit' errors during XML schema registration, you should also use SCOPEXMLREFERENCES Procedure.

• Using SCOPEXMLREFERENCES after XML schema registration and before loading XML instance data, makes these reference scoped to the out-of-line table only.

Syntax

DBMS_XMLSTORAGE_MANAGE.XPATH2TABCOLMAPPING (
   owner_name   IN  VARCHAR2 DEFAULT USER, 
   table_name   IN  VARCHAR2,
   column_name  IN  VARCHAR2 DEFAULT NULL,
   xpath        IN  VARCHAR2,
   namespaces   IN  VARCHAR2 DEFAULT NULL)
 RETURN XMLTYPE;

Parameters

Examples

XPath2TablColMapping evaluated on XMLType table with Xpath Notation, namespaces provided

SELECT XDB.DBMS_XMLSTORAGE_MANAGE.XPATH2TABCOLMAPPING (
   USER, 'XML_TAB', '', '//n1:item/n1:location','''xdbXmark'' as "n1"') 
FROM DUAL;

This produces a result, for example:

<Result>
<Mapping TableName="SYS_NT12345" ColumnName="location"/>
</Result>

This allows us to define an index or constraint on table SYS_NT12345 and column location.

XPath2TablColMapping evaluated on table not of XMLType but with XMLType column by means of DOT notation

SELECT XDB.DBMS_XMLSTORAGE_MANAGE.XPATH2TABCOLMAPPING (   
   USER,'PurchaseOrderTab','XMLCOL','xmldata.LineItems.LineItem', '')
FROM DUAL;