HOWTO: Check if an XMLType View or Table is Hierarchy Enabled

The following simple, code snippet, demonstrates how you can check if an XMLType view or table is hierarchy enabled via using DBMS_XDBZ.

/* ------------------------------------------------------------------------------------  
   http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xdbz.htm#ARPLS69487  
   DBMS_XDBZ.IS_HIERARCHY_ENABLED(  
   object_schema   IN  VARCHAR2,  
   object_name     IN  VARCHAR2,  
   hierarchy_type  IN  PLS_INTEGER := IS_ENABLED_CONTENTS)  
   RETURN BOOLEAN;  
   -------------------------------------------------------------------------------------   
*/

For default database “table” “SYS”.”DUAL”…

--
DECLARE  
  --  
  v_boolean        BOOLEAN      := false;  
  v_object_schema  VARCHAR2(30) := 'SYS';  
  v_object_name    VARCHAR2(30) := 'DUAL';  
  v_hierarchy_type VARCHAR2(100):= 'DBMS_XDBZ.IS_ENABLED_CONTENTS';  
  --  
BEGIN  
  --  
  v_boolean := DBMS_XDBZ.IS_HIERARCHY_ENABLED(v_object_schema, v_object_name, DBMS_XDBZ.IS_ENABLED_CONTENTS);  
  --  
  IF (v_boolean) THEN  
    dbms_output.put_line('Object '||v_object_schema||'.'||v_object_name||' is XDB hierarchy enabled');  
  ELSE  
    dbms_output.put_line('Object '||v_object_schema||'.'||v_object_name||' is NOT XDB hierarchy enabled');  
  END IF;  
  --  
END;  
/  
      
anonymous block completed  
      
Object SYS.DUAL is NOT XDB hierarchy enabled  

--

For demo XMLType table “OE”.”PURCHASEORDER” (Oracle example schema)

--
DECLARE  
  --  
  v_boolean        BOOLEAN      := false;  
  v_object_schema  VARCHAR2(30) := 'OE';  
  v_object_name    VARCHAR2(30) := 'PURCHASEORDER';  
  v_hierarchy_type VARCHAR2(100):= 'DBMS_XDBZ.IS_ENABLED_CONTENTS';  
  --  
BEGIN  
  --  
  v_boolean := DBMS_XDBZ.IS_HIERARCHY_ENABLED(v_object_schema, v_object_name, DBMS_XDBZ.IS_ENABLED_CONTENTS);  
  --  
  IF (v_boolean) THEN  
    dbms_output.put_line('Object '||v_object_schema||'.'||v_object_name||' is XDB hierarchy enabled');  
  ELSE  
    dbms_output.put_line('Object '||v_object_schema||'.'||v_object_name||' is NOT XDB hierarchy enabled');  
  END IF;  
  --  
END;  
/  
      
anonymous block completed  
      
Object OE.PURCHASEORDER is XDB hierarchy enabled   

--

HTH/M