Oracle Designer/Oracle SCM Meta Model

Lucas Jellema 7
0 0
Read Time:1 Minute, 1 Second

This post – or rather its comments – contain some investigations into the Designer 9i/10g Meta Model and Meta Meta Model. That is: the tables that contain the definitions for the Designer Model. There are three levels at stake here:

  1. Model: the definition of a table as defined by the developer and to be generated into the Application Schema in the Database
  2. Meta Model (or Designer Model): the Designer tables that contains the table and column definitions as created by the developer; this table is called i$sdd_tab
  3. Meta Meta Model (or Designer Meta Model): the Repository Model table that contains the definition of the table i$sdd_tab along with the tables that contain definitions for all properties (columns) of the table definitions (the i$sdd_tab table); these tables are called i$rm_element_types and i$rm_properties (for the logical definitions) and i$rm_sql_tables and i$rm_sql_columns

A good place to start working is the Designer Meta Model Application System (Dump), as presented on the AMIS website, see Article on Designer 9i/10g Meta Model.

I use the Designer Meta Model primarily in the extensions to the Repository Object Browser I am currently working on.

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
0 %
0 %
0 %
0 %
0 %
0 %

Average Rating

5 Star
4 Star
3 Star
2 Star
1 Star

7 thoughts on “Oracle Designer/Oracle SCM Meta Model

  1. The following query returns the list of Child Elements – and the tables in the Designer Schema that hold their data – for a given (master) element:

    select distinct
    ‘i$’||tab2.table_name child_tab_name
    , col.column_name child_to_parent_ref_column
    , child_et.ID child_type_id
    , child_et.SHORT_NAME child_type_of
    from i$RM_SQL_TABLES tab1
    , i$RM_SQL_TABLES tab2
    , rm_element_types et
    , rm_element_types child_et
    , i$rm_sql_row_types srt
    , i$rm_sql_row_types srt_child
    where et.irid = :master_type_id
    and = et.primary_row_type
    and = child_et.primary_row_type
    and tab2.irid = srt_child.table_mapped
    and con1.table_irid = srt.table_mapped
    and tab2.irid=con2.table_irid
    and con2.r_constraint_name=con1.constraint_name
    and con2.is_owning_fk!=’N’
    and col.constraint_irid=con2.irid
    and tab2.irid!= con1.table_irid

    For example for master element type Entity (:master_type_id = 5002) this query returns:

  2. How to Design Capture the Designer 9i/10g (Meta) Model

    These steps describe how to create an Application System in Oracle Designer that contains Table and View Definitions for the database objects that make up the Oracle Designer Repository. You will need some PL/SQL packages to get the most refined results; you can download these from the AMIS website

    1. Install Designer 9i or 10g Client and Server (Repository, Oracle 9i or 10g SCM)
    2. Enable Version Support (in RAU, Menu Options)
    3. Create Workarea “Design Capture Designer 10g Meta Model”
    3. Get Workarea Irid: Select Workarea, click on Name property,press F5 and copy IRID value to clipboard
    4. Create Root Folder Designer 10g Model
    5. Get Folder Irid: Select Folder Designer 10g Model, click on Name property,press F5 and copy IRID value to clipboard, paste to some text file
    6. Connect to Repository Owner database account using SQL*Plus or TOAD
    7. Create the following packages (from the zip-file at rm_domains, rm_el, rm_col, rm_fk, rm_fk_upd, rm_move
    8. Design capture all Domains used by the columns (element properties) in the meta-model:
    (examples are: {true,false}, {client,both,server,none}, {char,number,varchar,date,timestamp,….})
    – begin
    ( p_wa_irid => — context workarea Design Capture Designer 10g Meta Model
    , p_folder_irid => — target container Designer 10g Model
    , p_name_after => ‘0’
    , p_name_before => ‘G’
    9. Use the Server Design Capture functionality in the Design Editor to Design capture all Element Types (Primary and Secondary) to Table Definitions:
    – first in SQL*Plus or TOAD, connected as Repository Owner, execute this statement:
    set rn_res_name = ‘X’||rn_res_name

    this removes the filter on Design Capture that prevents it from Design Capturing Designer’s own elements

    – Design Capture (from Design Editor) all i$sdd_… tables
    – Design Capture (from Design Editor) all sdd_… views
    – Design Capture (from Design Editor) all ci_… views
    set rn_res_name = substr(rn_res_name,2)

    to re-establish the filer

    10. Use RM_EL to embellish Element Types/CI-View definitions (note:processing UID fails because setting the alias to UID is not allowed since UID is a reserved word!)
    11. Use RM_COLS to embellish column definitions (in view definitions)
    12. Use RM_FK to create foreign keys for view definitions
    13. Use rm_fk_upd to define ARCs for the foreign keys

  3. This query can be used to find the parent type of any (child) element type:

    select lnk_to.defined_against parent_type_id
    , e.short_name
    , e.TYPES
    from i$rm_properties prp
    , i$rm_link_properties lnk_from
    , i$rm_link_properties lnk_to
    , rm_element_types e
    where prp.descriptor_sequence = 1
    and prp.types = 16
    and prp.defined_against = :child_type_id
    and =
    and lnk_from.link_type = lnk_to.link_type
    and !=
    and = lnk_to.defined_against

    For example for :child_type_id = 5010 (Attribute) this query returns:

    5002 ENT 8 4968

Comments are closed.

Next Post

JBoss EJB anomalies (JBoss 3.2.4, Eclipse, Xdoclet & Lomboz)!

Developing CMP entity beans with JBoss 3.2.4, Eclipse and the Lomboz plug-in, I encountered the following anomalies: Related posts: Enabling Role-based security management in Oracle Designer 6i/9i/10g through the Repository Object Browser Dinsdag 18 september: Oracle Open World & JavaOne 2012 Preview mini-conferenties – 12 presentaties op 1 avond Hotsos […]
%d bloggers like this: