Type Dependency Tree
While trying to explain a student question about Oracle object types, it seemed necessary to show how to write a dependency tree. I did some poking around and found there wasn’t a convenient script at hand. So, I decided to write one.
This assumes the following Oracle object types, which don’t have any formal methods (methods are always provided by PL/SQL or Java language implementations):
CREATE OR REPLACE TYPE base_t AS OBJECT ( base_id NUMBER ) NOT FINAL; / CREATE OR REPLACE TYPE person_t UNDER base_t ( first_name VARCHAR2(20) , middle_name VARCHAR2(20) , last_name VARCHAR2(20)) NOT FINAL; / CREATE OR REPLACE TYPE driver_t UNDER person_t ( license VARCHAR2(20)); / |
Here’s a query to show the hierarchy of object types and attributes by object-level in the hierarchy:
COL type_name FORMAT A20 HEADING TYPE_NAME COL attr_no FORMAT 999 HEADING ATTR_NO COL attr_name FORMAT A20 HEADING ATTR_NAME COL TYPE FORMAT A12 HEADING TYPE SELECT DISTINCT LPAD(' ',2*(LEVEL-1)) || ut.type_name AS type_name , uta.attr_no , uta.attr_name , CASE WHEN uta.attr_type_name = 'NUMBER' THEN uta.attr_type_name WHEN uta.attr_type_name = 'VARCHAR2' THEN uta.attr_type_name || '(' || uta.LENGTH || ')' END AS TYPE FROM user_types ut , user_type_attrs uta WHERE ut.typecode = 'OBJECT' AND ut.type_name = uta.type_name AND uta.inherited = 'NO' START WITH ut.type_name = 'BASE_T' CONNECT BY PRIOR ut.type_name = ut.supertype_name ORDER BY uta.attr_no; |
It should return the following:
TYPE_NAME ATTR_NO ATTR_NAME TYPE
-------------------- ------- -------------------- ------------
BASE_T 1 BASE_ID NUMBER
PERSON_T 2 FIRST_NAME VARCHAR2(20)
PERSON_T 3 MIDDLE_NAME VARCHAR2(20)
PERSON_T 4 LAST_NAME VARCHAR2(20)
DRIVER_T 5 LICENSE VARCHAR2(20) |
As always, I hope this helps those looking to discover an Oracle object type hierarchy without examining each object type in turn.
Subscribe via RSS