Quick Query to report on Entities and Attributes in Oracle Designer

One of my colleagues needed a simple report on the Entities with their Attributes as defined in an Oracle Designer Repository. Having spent far too much time in the Designer API, I felt it should not be tremendous challenge to me, so accepted his invitation to create a simple SQL script. He required the name, description of the Entities and the name of the Attributes. We need the views CI_ENTITIES and CI_ATTRIBUTES for this, and because the Description is a multiline text property, we need to extract the value of the description from CDI_TEXT.

It’s easiest, though not required if you grant the proper privileges, to run the queries as Repository Owner. First, we will create a simple PL/SQL function that returns a multi line text property of a specific type for a given element as a concattenated string:

create or replace 
function get_text
( p_text_type in varchar2
, p_entity_id in number
) 
return varchar2
is
  l_text varchar(32000);
begin
  for txt in ( select txt.txt_text 
               from   cdi_text txt
               where  txt.txt_ref = p_entity_id
               and    txt.txt_type = p_text_type
               order
               by     txt.txt_seq
             ) loop
    l_text:= l_text||txt.txt_text;    
  end loop; -- txt
  return l_text;
end get_text;
/

Before executing the query, we should set the workarea context. If we would not set the workarea, the query would include all versions of all entities that the current user has access to. Setting the workarea context is quite simple: pass the name of the workarea to the jr_context.set_workarea procedure:

exec jr_context.set_workarea('ARTIS')

Now we can execute the query itself. You can easily modify the query to add properties you would also like to retrieve.

select case rn
       when 1
       then ent_name
       end entity
,      case rn
       when 1
       then description
       end  description       
,      ' - '||attribute attribute
from   ( select ent.name  ent_name
         ,      get_text('CDIDSC', ent.id) description
         ,      att.name  attribute
         ,      att.notes att_comment
         ,      att.sequence_number
         ,      row_number() over (partition by ent.id order by att.sequence_number) rn
         from   ci_entities ent
         ,      ci_attributes att
         where  ent.id = att.entity_reference         
       ) ent_att       
order
by     ent_name
,      ent_att.sequence_number
/

The result of the query looks something like:

ENTITY
----------------------------------------
DESCRIPTION
----------------------------------------------------------------------------------------------------
ATTRIBUTE
-------------------------------------------
AUTHOR
Someone who makes contributions to books.
 - FIRST NAME
 - LAST NAME
 - INITIALS
 - COUNTRY OF BIRTH
 - BIOGRAPHY
BOOK
Hier is de beschrijving van entiteit BOOK. BOOK wordt uitgegeven door een [PUBLISHER]. Een tweede soort verwijzing is naar een plaatje. {hsd505_erd.gif}
{REP_FILE=tobias.JPG}
 - PUBLISH YEAR
 - KEYWORDS
 - PUBLISH MONTH
 - TITLE
 - THUMBNAIL
 - ISBN
 - LANGUAGE
PUBLISHER
Company that publishes books
 - WEBSITE
 - COUNTRY
 - NAME

Query Improvement

While the query returns the correct results, from a performance point of view it is far from ideal. Currently, the PL/SQL Function get_text is invoked for each attribute instead of just once for every entity. The easiest rewrite to accomplish just a single call per entity – the minimum we can hope for – looks something like:

select case rn
       when 1
       then ent_name
       end entity
,      case rn
       when 1
       then get_text('CDIDSC', ent_att.ent_id)
       end  description       
,      ' - '||attribute attribute
from   ( select ent.name  ent_name
         ,      ent.id    ent_id
         ,      att.name  attribute
         ,      att.notes att_comment
         ,      att.sequence_number
         ,      row_number() over (partition by ent.id order by att.sequence_number) rn
         from   ci_entities ent
         ,      ci_attributes att
         where  ent.id = att.entity_reference         
       ) ent_att       
order
by     ent_name
,      ent_att.sequence_number
/

Furthermore, it is not ideal we need this rather specialized function in the first place. We could use a user defined aggregation function – see our blog article Oracle Data Cartridge – Extending the Database. In an Oracle 10g database, we should be able to leverage the COLLECT aggregator (see for example Oracle-Base on String Aggregation techniques). The COLLECT can be used to aggregate multiple records into a single Collection type. For example to aggregate Strings we would use an Oracle Type, defined like this:

CREATE OR REPLACE TYPE STRING_TABLE AS TABLE OF VARCHAR2(4000)
/

We can the rewrite the query, using COLLECT and this new type definition:

with entities as
( select ent.name  ent_name
  ,      ent.id    ent_id
  ,      cast( collect( dsc.txt_text ) as string_table) ent_description
  from   ( select txt.txt_text
           ,      txt.txt_ref
           from   cdi_text txt
           where  txt.txt_type = 'CDIDSC'
           order
           by     txt.txt_seq
           ) dsc
  ,      ci_entities ent
  where  dsc.txt_ref = ent.id
  group
  by     ent.id
  ,      ent.name
) 
select case rn
       when 1
       then ent_name
       end entity
,      case rn
       when 1
       then ent_description
       end  description       
,      ' - '||attribute attribute
from   ( select ent.*
         ,      att.name  attribute
         ,      att.notes att_comment
         ,      att.sequence_number
         ,      row_number() over (partition by ent.ent_id order by att.sequence_number) rn
         from   entities ent
         ,      ci_attributes att
         where  ent.ent_id = att.entity_reference         
       ) ent_att       
order
by     ent_name
,      ent_att.sequence_number
/

The output is not yet ideally formatted: the Entity Description is returned as a STRING_TABLE. We would now like to turn the STRING_TABLE into a concattenated string. For this, we need another PL/SQL Function. So what have we gained you might ask? Well, it is totally different function: a very generic one that does not do any database access and can be reused in many situations. This function looks like:

CREATE OR REPLACE 
FUNCTION sum_string_table
(p_string_table  IN  string_table
) RETURN VARCHAR2 
IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_string_table.FIRST .. p_string_table.LAST LOOP
    l_string := l_string || p_string_table(i);
  END LOOP;
  RETURN l_string;
END sum_string_table;
/

And the over all query is now:

with entities as
( select ent.name  ent_name
  ,      ent.id    ent_id
  ,      cast( collect( dsc.txt_text ) as string_table) ent_description
  from   ( select txt.txt_text
           ,      txt.txt_ref
           from   cdi_text txt
           where  txt.txt_type = 'CDIDSC'
           order
           by     txt.txt_seq
           ) dsc
  ,      ci_entities ent
  where  dsc.txt_ref = ent.id
  group
  by     ent.id
  ,      ent.name
) 
select case rn
       when 1
       then ent_name
       end entity
,      case rn
       when 1
       then sum_string_table(ent_description)
       end  description       
,      ' - '||attribute attribute
from   ( select ent.ent_name  ent_name
         ,      ent.ent_id    ent_id
         ,      ent.ent_description    ent_description
         ,      att.name  attribute
         ,      att.notes att_comment
         ,      att.sequence_number
         ,      row_number() over (partition by ent.ent_id order by att.sequence_number) rn
         from   entities ent
         ,      ci_attributes att
         where  ent.ent_id = att.entity_reference         
       ) ent_att       
order
by     ent_name
,      ent_att.sequence_number
/