Re: Problem obtaining the correct IRID/IVID from the Repository in Designer

Hi Michael,

as far as I understood the 9i Repository the IRID is the Internal Repository Identifikation Descriptor and the IVID is the Internal Version Identifikation Descriptor. To get the latest Version of an object you have to select the maximum IVID of one IRID. Fortunately the Repository is using surrogate keys therefore you can join the related objects by this ID.

Look at the following: First of all we have a cursor selecting the latest version of all tables in a given container:

  CURSOR cu_tab IS
   SELECT TAB.ID TAB_ID

,            TAB.NAME TD_TABLE
,            'Display Title : '||TAB.display_title TAB_DISPLAY_TITLE
,            TAB.alias TAB_SHORT_NAME
,            TAB.initial_number_of_rows TAB_START_ROWS
,            TAB.maximum_number_of_rows TAB_END_ROWS
,            TAB.table_TYPE tab_type
,            ORT.NAME  TAB_ORT_NAME
,            TAB.remark TAB_COMMENT
,            apptab.folder_reference
,          tab.ivid
FROM    ci_table_definitions TAB
,             ci_oracle_types ORT
,             ci_folder_members apptab
WHERE    TAB.oracle_object_type_reference = ORT.ID(+)
and      apptab.member_object = tab.id
AND      apptab.folder_reference = I_V_CONTAINER
and      apptab.parent_ivid = I_V_VERSION
and      tab.ivid in (
      select max(ivid)
      from   ci_table_definitions TAB2
      where  tab.id = tab2.id)

Then we have another one which selects the attributes to these table definitions using the tab_id and tab.ivid of the statement above:

  CURSOR cu_tab_att IS
  SELECT col.table_reference el_within_id

,              col.sequence_number col_seq_det
,              col.name col_name_txt
,              col.null_indicator COL_NULL_IND,
               DECODE(col.null_indicator, 'NULL', '1', '0') ATT_OPT_INC,
               COL.datatype,
               DECODE(COL.datatype,'NUMBER', 5, 'DATE', 1, 0)
ATT_FORMAT_INC,
               COL.maximum_length,
               COL.decimal_places
,          dom.name COL_DOM_NAME
,          col.uppercase COL_UPPCS
,          seq.name COL_SEQ_NAME
,          col.default_value COL_DEF_VAL
,          col.order_sequence COL_ORD_SEQ
,          col.sorting_order COL_SORT_ORD
,          col.id el_id
,          ort.name COL_ORT_NAME
,          col.object_type_inclusion COL_DEGREE
,          col.remark COL_COMMENT
FROM  ci_columns col
,           ci_sequences seq
,           ci_domains dom
,           ci_oracle_types ort
where   col.sequence_reference = seq.id(+)
and       col.domain_reference = dom.id(+)
and        col.oracle_type_reference = ort.id(+)
and     col.table_reference = I_V_TAB_REF
and     col.parent_ivid = I_V_TAB_VER

regards
Oliver

"Michael Jewell" <michael.jewell_at_uk.thalesgroup.com> schrieb im Newsbeitrag news:b0oerd$7el$1_at_rdel.co.uk...
> I am currently doing some work on Oracle 9i Repository database, and have
> run into problems obtaining the latest version of an object. For example,
I
> have created a view which will obtain all the tables in a container using
> the IVID, and IRID I can do this, however I end up retrieving data on
> previous versions of the containers and tables existance, producing what
> seems to be duplicate results.
>
> Would anyone know if the is a function to obtain the latest version, and
how
> to then obtain the related versions in related tables?????

>