Quick Query to report on Entities and Attributes in Oracle Designer

0

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 <br />function get_text<br />( p_text_type in varchar2<br />, p_entity_id in number<br />) <br />return varchar2<br />is<br />  l_text varchar(32000);<br />begin<br />  for txt in ( select txt.txt_text <br />               from   cdi_text txt<br />               where  txt.txt_ref = p_entity_id<br />               and    txt.txt_type = p_text_type<br />               order<br />               by     txt.txt_seq<br />             ) loop<br />    l_text:= l_text||txt.txt_text;    <br />  end loop; -- txt<br />  return l_text;<br />end get_text;<br />/<br />

 
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')<br />&nbsp;

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

The result of the query looks something like:

ENTITY<br />----------------------------------------<br />DESCRIPTION<br />----------------------------------------------------------------------------------------------------<br />ATTRIBUTE<br />-------------------------------------------<br />AUTHOR<br />Someone who makes contributions to books.<br /> - FIRST NAME<br /> - LAST NAME<br /> - INITIALS<br /> - COUNTRY OF BIRTH<br /> - BIOGRAPHY<br />BOOK<br />Hier is de beschrijving van entiteit BOOK. BOOK wordt uitgegeven door een [PUBLISHER]. Een tweede soort verwijzing is naar een plaatje. {hsd505_erd.gif}<br />{REP_FILE=tobias.JPG}<br /> - PUBLISH YEAR<br /> - KEYWORDS<br /> - PUBLISH MONTH<br /> - TITLE<br /> - THUMBNAIL<br /> - ISBN<br /> - LANGUAGE<br />PUBLISHER<br />Company that publishes books<br /> - WEBSITE<br /> - COUNTRY<br /> - 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<br />       when 1<br />       then ent_name<br />       end entity<br />,      case rn<br />       when 1<br />       then get_text('CDIDSC', ent_att.ent_id)<br />       end  description       <br />,      ' - '||attribute attribute<br />from   ( select ent.name  ent_name<br />         ,      ent.id    ent_id<br />         ,      att.name  attribute<br />         ,      att.notes att_comment<br />         ,      att.sequence_number<br />         ,      row_number() over (partition by ent.id order by att.sequence_number) rn<br />         from   ci_entities ent<br />         ,      ci_attributes att<br />         where  ent.id = att.entity_reference         <br />       ) ent_att       <br />order<br />by     ent_name<br />,      ent_att.sequence_number<br />/<br />

 

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)<br />/&nbsp;

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

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

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:

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

And the over all query is now:

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

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.