Database Answers Cord Car, Auburn, Indiana, 1936 - click for large picture.

Home Ask a Question Careers Contact us Data Models First Timers Search Site Map
User-Extendable Data Dictionary
Back to the Data Model
The Requirements have been defined by the User as follows :-
A Data Dictionary that can be extended by the non-IT User to define new Entry Types and new Entries.

A. The Tables to implement this User-Extendable Facility include :-
A.1 XTC Entry Types
A.2 XTC Entry Definitions
A.3 XTC Data

Typical XTC Entry Types include :-
  • Addresses, Data Items, etc. C. How are these Things of Interest related ? C.1 An XTC Entry Types can have one or many Entry Defintions. C.2 An Entry Definition can have zero, one or many XTC Data values. Entries in these table for an Address Type would be :- 1) In the XTC_Entry_Types Table :- user_login_name xtc_entry_type_name parent_xtc_entry_type_name xtc_entry_type_description date_created dba_admin Address A 3-line UK Address 07/10/2004 2) In the XTC_Entry_Type_Definitions Table :- user_login_name xtc_entry_type_name fld_seq fld_name dba_admin Address 1 Building dba_admin Address 2 Street dba_admin Address 3 Locality dba_admin Address 4 City dba_admin Address 5 State/County dba_admin Address 6 Country 3) In the XTC_Entry_Data Table :- user_login_name xtc_entry_type_name rcd_seq fld_seq fld_value dba_admin Address 1 1 Mansion House dba_admin Address 1 2 17,Thames Side dba_admin Address 1 3 Laleham dba_admin Address 1 4 Windsor dba_admin Address 1 5 Berkshire dba_admin Address 1 6 England SQL to create sample data ...
    PL/SQL Code to display User-Defined Data Dictionary Entry Types ...
    The output from this Code is shown lower down the page. -- Code starts here ... Prompt Displaying XTC Definitions and Details (and Data) set long 30000 set serveroutput on size 3000 -- Declare v_parent_user_login_name XTC_Entry_Types.user_login_name%TYPE; v_parent_xtc_entry_type_name XTC_Entry_Types.xtc_entry_type_name%TYPE; v_xtc_entry_type_description XTC_Entry_Types.xtc_entry_type_description%TYPE; -- v_child_user_login_name XTC_Entry_Type_Definitions.user_login_name%TYPE; v_child_xtc_entry_type_name XTC_Entry_Type_Definitions.xtc_entry_type_name%TYPE; v_child_fld_seq XTC_Entry_Type_Definitions.fld_seq%TYPE; v_child_fld_name XTC_Entry_Type_Definitions.fld_name%TYPE; -- v_data_user_login_name XTC_Entry_Data.user_login_name%TYPE; v_data_xtc_entry_type_name XTC_Entry_Data.xtc_entry_type_name%TYPE; v_data_rcd_seq XTC_Entry_Data.rcd_seq%TYPE; v_data_fld_seq XTC_Entry_Data.fld_seq%TYPE; v_data_fld_value XTC_Entry_Data.fld_value%TYPE; -- count_parents number; count_children number; count_data_rcds number; loop_counter number; x_txt_No_Children varchar2(32000); x_txt_Heading varchar2(32000); x_txt_DataRow varchar2(32000); -- Declare First Cursor (Parents) ... Cursor c_Parents is SELECT user_login_name,xtc_entry_type_name,xtc_entry_type_description,date_created FROM scott.XTC_Entry_Types ORDER BY user_login_name,xtc_entry_type_name; -- -- Declare Second Cursor (Children) ... Cursor c_Children is SELECT user_login_name,xtc_entry_type_name,fld_seq,fld_name FROM scott. XTC_Entry_Type_Definitions WHERE user_login_name = v_parent_user_login_name AND xtc_entry_type_name = v_parent_xtc_entry_type_name ORDER BY user_login_name,xtc_entry_type_name,fld_seq; -- -- Declare Third Cursor (Data Rcds) ... Cursor c_Data is SELECT user_login_name,xtc_entry_type_name rcd_seq,fld_seq,fld_value FROM scott. XTC_Entry_Data WHERE user_login_name = v_parent_user_login_name AND xtc_entry_type_name = v_parent_xtc_entry_type_name ORDER BY user_login_name,xtc_entry_type_name,rcd_seq,fld_seq; -- -- Outer Loop for Parents begins here ... BEGIN count_children := 0; count_parents := 0; count_data_rcds := 0; loop_counter := 0; -- For cParents in c_parents LOOP count_parents := count_parents + 1; v_parent_user_login_name := cParents.user_login_name; v_parent_xtc_entry_type_name := cParents.xtc_entry_type_name; -- dbms_output.put_line('Entry Type Name = ' || v_parent_xtc_entry_type_name); x_txt_Heading :=| ; -- For cChildren in c_children LOOP count_children := count_children + 1; v_child_fld_seq := cChildren.fld_seq; v_child_fld_name := cChildren.fld_name; -- x_txt_Heading := x_txt_Heading || v_child_fld_name || | ; -- end of Child Row and Heading end LOOP; dbms_output.put_line( x_txt_Heading); x_txt_DataRow := | ; For cData in c_data LOOP count_data_rcds := count_data_rcds + 1; v_data_fld_seq := cData.fld_seq; v_data_fld_value := cData.fld_value; x_txt_DataRow := x_txt_DataRow || v_data_fld_value || | ; end LOOP; dbms_output.put_line(x_txt_DataRow); -- end of Parent Loop - end LOOP; dbms_output.put_line('Total Entry Type Count = ' || to_char(count_parents)); dbms_output.put_line('Total Entry Definition Field Count = ' || to_char(count_children)); dbms_output.put_line('Total Data record Count = ' || to_char(count_data_rcds)); end; /
    and here's the output ... SQL> / Entry Type Name = Address | Building | Street | Locality | City | State/County | Country | | Mansion House | 17, Thames Side | Laleham | Windsor | Berkshire | England | Total Entry Type Count = 1 Total Entry Definition Field Count = 6 Total Data record Count = 6 PL/SQL procedure successfully completed.

  • Barry Williams
    7th. October 2004
    Principal Consultant
    Database Answers

     

    Home Ask a Question Careers Contact us Data Models First Timers Search Site Map