At my current customer we use queues a lot. They're JMS queues, but in stead of Weblogic JMS, they're served by the Oracle database.

This is not new, in fact the Oracle database supports this since 8i through Advanced Queueing. Advanced Queueing is Oracle's Queueing implementation based on tables and views. That means you can query the queue table to get to the content of the queue. But you might know this already.

What I find few people know is that you shouldn't query the queue table directly but the accompanying AQ$ view instead. So, if your queue table is called MY_QUEUE_TAB, then you should query AQ$MY_QUEUE_TAB. So simply prefix the table name with  AQ$. Why? The AQ$ view is created automatically for you and joins the queue table with accompanying IOT tables to give you a proper and convenient representation of the state, subscriptions and other info of the messages. It is actually the supported wat of query the queue tables.

A JMS queue in AQ is implemented by creating them in queue tables based on the Oracle type
sys.aq$_jms_text_message type.

That is in fact a quite complex type definition that implements common JMS Text Message based queues. There are a few other types to support other JMS message types. But let's leave that.

Although the payload of the queue table is a complex type, you can get to its attributes in the query using the dot notation. But for that it is mandatory to have a table shortname and prefix the view columns with the table shortname.

The sys.aq$_jms_text_message has a few main attributes, such as text_lob for the content and header for the JMS header attributes. The header is based on the type sys.aq$_jms_header. You'll find the JMS type there. But also the properties attribute based on sys.aq$_jms_userproparray. That in its turn  is a varray based on aq$_jms_userproperty. Now, that makes it a bit complex, because we would like to know the values of the JMS properties, right?

We use those queues using the JMS adapter of SOA Suite and that adds properties containing the composite instance ID, ECID, etcetera. And if I happen to have a message that isn't picked up, it would be nice to know which Composite Instance enqueued this message, wouldn't it?

Luckily, a Varray can be considered as a collection of Oracle types. And do you know you  can query those? Simply provide it to the table() function and Oracle threats it as a table. When you know which properties you may expect, and their types, you can select them in the select clause of your query.  I found the properties that are set by SOA Suite and added them to my query. But you could find others as well.

Putting all this knowledge together, I came up with the following  query:

select qtb.queue
, qtb.msg_id
, qtb.msg_state
,qtb.enq_timestamp
--,qtb.user_data.header.replyto
,qtb.user_data.header.type type
,qtb.user_data.header.userid userid
,qtb.user_data.header.appid appid
,qtb.user_data.header.groupid groupid
,qtb.user_data.header.groupseq groupseq
--, qtb.user_data.header.properties properties
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_compositeInstanceId') tracking_compositeInstanceId
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'JMS_OracleDeliveryMode') JMS_OracleDeliveryMode
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_ecid') tracking_ecid
, (select num_value from table (qtb.user_data.header.properties) prp where prp.name = 'JMS_OracleTimestamp') JMS_OracleTimestamp
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_parentComponentInstanceId') tracking_prtCptInstanceId
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_conversationId') tracking_conversationId
,qtb.user_data.header
,qtb.user_data.text_lob text
from AQ$MY_QUEUE_TAB qtb
where qtb.queue = 'MY_QUEUE'
order by enq_timestamp desc;

This delivered me an actual message that was not picked up by my process. And I could use  the property tracking_compositeInstanceId to find my soa composite instance in EM.

Very helpful if you are able to pause the consumption of your messages.

This also shows you how to query tables with complex nested tables.

A quick one. In the past I used to generate ddl based on queries, like the following. But I find myself to re-invent them again. So to have it saved for my offspring: here's one on deleting obsolete columns as generated on importing an excel sheet in SQLDeveloper:

declare
  l_schema_name varchar2(30) := 'MY_SCHEMA';
  l_table_name varchar2(30) := 'A_TABLE';
  cursor c_cols is 
    select column_name 
    from all_tab_columns col 
    where col.table_name = l_table_name 
    and col.owner = l_schema_name
    and col.column_name like 'COLUMN%';
begin
  for r_cols in c_cols loop
    execute immediate 'alter table '||l_schema_name||'.'||l_table_name||' drop column '||r_cols.column_name;
  end loop;
end;
/

And here's one to generate a check constraint on all index colunns of a table:

declare
  l_schema_name varchar2(30) := 'MY_SCHEMA';
  l_table_name varchar2(30) := 'A_TABLE';
  l_constraint_name_pfx varchar2(30) := 'XXX_ALIAS_CHK';
  l_idx pls_integer := 1;
  cursor c_cols is 
    select column_name 
    from all_tab_columns col 
    where col.table_name = l_table_name 
    and col.owner = l_schema_name
    and col.column_name like 'IND_%'; 
begin
  for r_col in c_col loop
    execute immediate 'ALTER TABLE '||l_schema_name||'.'||l_table_name||' ADD CONSTRAINT '||l_constraint_name_pfx||l_idx||' CHECK ('||r_col.column_name||' in (''J'',''N''))ENABLE';
    l_idx := l_idx+1;
  end loop;
end;
/

You might have tables that contain columns with a userdefined datatypes. For instance from 11g onwards SOASuite contain Integration B2B, with that datamodel

B2B works with advanced queueing with the queue-table ip_qtab based on the IP_MESSAGE_TYPE Oracle Type wich is defined like:

create or replace type IP_MESSAGE_TYPE as OBJECT (
        MSG_ID                                          VARCHAR2(128),
        INREPLYTO_MSG_ID                                VARCHAR2(128),
 FROM_PARTY                                      VARCHAR2(512),
 TO_PARTY                                        VARCHAR2(512),
        ACTION_NAME                                     VARCHAR2(512),
        DOCTYPE_NAME                                    VARCHAR2(512),
        DOCTYPE_REVISION                                VARCHAR2(512),
        MSG_TYPE                                        INT,
        PAYLOAD                                         CLOB,
        ATTACHMENT                                      BLOB
);

In the queuetable you then have a payload column based on this type. When you do a select on such a table the payload column has actually several attributes. Tools like Pl/Sql Developer from Allroundautomations or TOAD apparently encounter that the column is based on the Oracle Type, so they actually show the seperate attributes in the grid.

SQLDeveloper (currently 4.3) apparently does not so. But it is quite easy to add this information in your select. For a select on the queuetable (actually with AQ you shouldn't query the queuetable, but the accompanying AQ$<queuetable> view) it will look like:

SELECT QTB.QUEUE,
  QTB.MSG_ID,
  QTB.CORR_ID,
  QTB.MSG_PRIORITY,
  QTB.MSG_STATE,
  QTB.RETRY_COUNT,
  QTB.USER_DATA.MSG_ID MSG,
  QTB.USER_DATA.INREPLYTO_MSG_ID INREPLYTO_MSG_ID,
  QTB.USER_DATA.FROM_PARTY FROM_PARTY,
  QTB.USER_DATA.TO_PARTY TO_PARTY,
  QTB.USER_DATA.ACTION_NAME ACTION_NAME,
  QTB.USER_DATA.DOCTYPE_NAME DOCTYPE_NAME,
  QTB.USER_DATA.DOCTYPE_REVISION DOCTYPE_REVISION,
  QTB.USER_DATA.MSG_TYPE MSG_TYPE,
  QTB.USER_DATA.PAYLOAD PAYLOAD,
  QTB.CONSUMER_NAME,
  QTB.PROTOCOL
FROM AQ$IP_QTAB QTB;

You see that the trick is to just add the attribute as a seperate identifier to the user_data-column, using the dot-notation.

If you're certain that the selected rows contain a valid XML document in the Payload attribute you could provide that attribute to the xmltype() constructor:

SELECT QTB.QUEUE,
  QTB.MSG_ID,
  QTB.CORR_ID,
  QTB.MSG_PRIORITY,
  QTB.MSG_STATE,
  QTB.RETRY_COUNT,
  QTB.USER_DATA.MSG_ID MSG,
  QTB.USER_DATA.INREPLYTO_MSG_ID INREPLYTO_MSG_ID,
  QTB.USER_DATA.FROM_PARTY FROM_PARTY,
  QTB.USER_DATA.TO_PARTY TO_PARTY,
  QTB.USER_DATA.ACTION_NAME ACTION_NAME,
  QTB.USER_DATA.DOCTYPE_NAME DOCTYPE_NAME,
  QTB.USER_DATA.DOCTYPE_REVISION DOCTYPE_REVISION,
  QTB.USER_DATA.MSG_TYPE MSG_TYPE,
  xmltype(QTB.USER_DATA.PAYLOAD) PAYLOAD,
  QTB.CONSUMER_NAME,
  QTB.PROTOCOL
FROM AQ$IP_QTAB QTB;

And of course this works for other tables as well. This is just a quick example for a table based on an object type. Unfortunately I don't have some example data in the queue at the moment.

In my current project I need to query an MS SqlServer database.
Unfortunately the dates are stored as a BigInt instead of a proper date datatype.
So I had to find out how to do compare the dates with the systemdate, and how to get the system date. To log this for possible later use, as an exception, a blog about SqlServer.

To get the system date, you can do:

(SELECT dt=GETDATE()) a 

It's maybe my Oracle background, but I would write this like:

(SELECT GETDATE() dt) a 

An alternative is:

select CURRENT_TIMESTAMP

I found this at this blog. Contrary to the writer of that blog I would prefer this version, since I found that it works on Oracle too. There are several ways to convert this to a bigint, but the most compact I found is:

  ( SELECT  YEAR(DT)*10000+MONTH(dt)*100+DAY(dt) sysdateInt
FROM
  -- Test Data
  (SELECT  GETDATE() dt) a ) utl

The way I wrote this, makes it usefull as a subquery or a joined query:

SELECT
  Ent.* ,
  CASE
    WHEN Ent.endDate  IS NOT NULL
    AND Ent.endDate-1 < sysdateInt
    THEN Ent.endDate-1
    ELSE sysdateInt
  END refEndDateEntity ,
  utl.sysdateInt
FROM
  SomeEntity Ent,
  ( SELECT  YEAR(DT)*10000+MONTH(dt)*100+DAY(dt) sysdateInt
FROM
  -- Test Data
  (SELECT  GETDATE() dt) a ) utl;

To convert a bigint to a date, you can do the following:

CONVERT(DATETIME, CONVERT(CHAR(8), ent.endDate))

However, I found that although this works in a select clause, in the where-clause this would run into a "Data Truncation" error. Maybe it is due to the use of SqlDeveloper and thus a JDBC connection to SqlServer, but I'm not so enthousiastic about the error-responses of SqlServer... I assume the error has to do with the fact that it has to do with the fact that SqlServer has to interpret a column-value of a row when it did not already selected it, that is when evaluating wheter to add the row (or not) to the result set. So to make it work I added the construction as a determination value in the select clause of a 1:1 view on the table, and use that view in stead of the table. Then the selected value can be used in the where clause.

Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here.
It describes how to create an xml document as an XMLType with an Oracle SQL Query.

The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data as xml, but then: how about the foreign key relations?

It turns out pretty straightforward, that you can probably figure out yourself. But hey, I'm not a bad guy, so how about sharing it to you?

The query selects employees with their addresses and goes as follows:

select xmlelement("emp:employees"
  , xmlattributes( 'http://xmlnls.darwin-it.nl/doe/xsd/v1/employee' as "xmlns"
                 , 'http://xmlnls.darwin-it.nl/doe/xsd/v1/employee' as "xmlns:emp"
                 , 'http://xmlnls.darwin-it.nl/doe/xsd/v1/address' as "xmlns:ads")
  , xmlagg
  ( xmlelement
    ( "emp:employee"
    , xmlforest
      ( emp.title as "emp:title"
      , emp.firstname as "emp:firstName"
      , emp.last_name as "emp:lastName"
      , emp.gender as "emp:gender"
      , emp.birth_date as "emp:birthDate"
      )
    , ( select xmlelement( "emp:addresses"
        , xmlagg(
             xmlelement("ads:address"
             , xmlattributes( ate.code as "type"
                            , ate.description as "description")
             , xmlforest
               ( ads.adress_line1 as "ads:addressLine1"
               , ads.adress_line2 as "ads:addressLine2"
               , ads.adress_line3 as "ads:addressLine3"
               , ads.postal_code as "ads:postalCode"
               , ads.city as "ads:city"
               , ads.country as "ads:country"
               )
             )
          )
        )
        from doe_party_addresses pae
        join doe_addresses ads on ads.id = pae.ads_id
        join doe_address_types ate on ate.id= pae.ate_id
        where pae.emp_id = emp.id
        )
      )
    )
  )  xml
from doe_employees emp;

The doe_addresses table is joined with the doe_employees table via a couple-table named doe_party_addresses. This is because an employee can have multiple addresses, but with different types. An employee can have a Business address and a Home address. Like a customer can have a shipping, billing and visiting addresses. Also an address can be used by multiple parties. You see here that the addresses are selected as a subselect. The output of the sub-select is an XMLType that can be embedded in the xmlelement (and other xml-) functions. What you also see is that I added namespace declarations as xmlattributes on the top-level element. The element and attribute names are prefixed with the corresponding 'emp:' or 'ads:' namespace-prefixes. Oh, and the output of the query is something like:

<?xml version="1.0" encoding="UTF-8" ?>
<emp:employees xmlns="http://xmlnls.darwin-it.nl/doe/xsd/v1/employee"
               xmlns:emp="http://xmlnls.darwin-it.nl/doe/xsd/v1/employee"
               xmlns:ads="http://xmlnls.darwin-it.nl/doe/xsd/v1/address">
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>Ed</emp:firstName>
    <emp:lastName>Bushes</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1968-01-20</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Maasstraat 19</ads:addressLine1>
        <ads:postalCode>3812HS</ads:postalCode>
        <ads:city>Amersfoort</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>M.</emp:firstName>
    <emp:lastName>Outback</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1961-10-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Rocky Road 2</ads:addressLine1>
        <ads:postalCode>20001</ads:postalCode>
        <ads:city>StoneHench</ads:city>
        <ads:country>UNITED KINGDOM</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>M.</emp:firstName>
    <emp:lastName>Outback</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1961-10-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Hofvijver 12</ads:addressLine1>
        <ads:postalCode>2000XX</ads:postalCode>
        <ads:city>Den Haag</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Ms.</emp:title>
    <emp:firstName>Pat</emp:firstName>
    <emp:lastName>Darwin</emp:lastName>
    <emp:gender>F</emp:gender>
    <emp:birthDate>1980-03-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Rijnzathe 6</ads:addressLine1>
        <ads:postalCode>3140ZP</ads:postalCode>
        <ads:city>De Meern</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>T.</emp:firstName>
    <emp:lastName>Barakus</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1970-02-11</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Erasmusstraat 312</ads:addressLine1>
        <ads:postalCode>1234GK</ads:postalCode>
        <ads:city>Rotterdam</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Ms.</emp:title>
    <emp:firstName>Debby</emp:firstName>
    <emp:lastName>Waters</emp:lastName>
    <emp:gender>F</emp:gender>
    <emp:birthDate>1982-01-20</emp:birthDate>
    <emp:addresses>
      <ads:address type="WORK" description="Work address">
        <ads:addressLine1>Darwinplein 11</ads:addressLine1>
        <ads:postalCode>4321PS</ads:postalCode>
        <ads:city>Amsterdam</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Newtonweg 41</ads:addressLine1>
        <ads:postalCode>6543AB</ads:postalCode>
        <ads:city>Maasland</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>Wally</emp:firstName>
    <emp:lastName>Waters</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1963-06-13</emp:birthDate>
    <emp:addresses></emp:addresses>
  </emp:employee>
</emp:employees>

2014-04-25, update: I changed the namespaces and I found a curiosity in my database content: all birthdates were in the future...

Yesterday I wrote about how you can (re)compile an object with the debug option. Now it might be handy to recompile an object with the debug option off. But to do that automatically you need to know which objects are compiled with the debug option. For you don't want to recompile your whole schema do you? I don't want it, since my schema is the APPS schema of EBS and it contains a billion packages...

So how would you do that? I was looking for a neat view that gives me this information. And browsing through all the 'ALL_%' views I stumbled on the nice view: all_stored_settings. It contains all kinds of parameters about the stored pl/sql objects.
The one I wanted to query on is the plsql_debug parameter. Mark that the parameter names are lower-case while the True/False values are Upper-case.

Besides Owner, Object_Name, Object_Type columns it also has the Object_id column. So to getall the packages with the debug option on simply join the view with the all_objects view:

select obj.* 
from all_objects obj
, all_stored_settings ssg
where ssg.object_id = obj.object_id
and ssg.param_name = 'plsql_debug'
and ssg.param_value = 'TRUE'
and obj.object_type in ('PACKAGE', 'PACKAGE BODY')

So to recompile all these packages I changed the script of yesterday:

declare
  cursor c_obj 
  is select obj.* 
  from all_objects obj
  , all_stored_settings ssg
  where ssg.object_id = obj.object_id
  and ssg.param_name = 'plsql_debug'
  and ssg.param_value = 'TRUE'
  and obj.object_type in ('PACKAGE', 'PACKAGE BODY');
  l_command varchar2(32767);
  procedure put_line(p_text in varchar2) is
    l_text varchar2(32767) := p_text;
    l_line varchar2(255);
  begin
    while length(l_text) > 0 loop
      l_line := substr(l_text, 1, 255);
      dbms_output.put_line(l_line);
      l_text := substr(l_text, 256);
    end loop;
  end put_line;
begin
  for r_obj in c_obj
  loop
    begin
      l_command := 'alter package ' ||r_obj.object_name || ' compile';
      if r_obj.object_type = 'PACKAGE BODY' then
        l_command := l_command || ' body';
      end if;
      put_line('Executing: '||l_command);
      execute immediate l_command;
      put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name|| '.'));
    exception
      when others then
        put_line('Error compiling '||lower(r_obj.object_type||' '||r_obj.object_name));
    end;
  end loop;
end;

Of course this script can also easily be changed to add debug info for those packages that haven't yet...

This week I teached a course about the Do's and Don'ts in Oracle 10gR2 (SQL and PL/SQL) I had a room full of experienced Oracle Oracle 7 and Oracle 8 programmers.

I talked about grouping and the rollup function in SQL.
One of the questions was: How can we use the grouping totals in our reporting tool.

A solution:

Let's look at the salaries per job per department in our emp table:

What if I want the following results in a report:
- How much do all SALESMAN earn in department 30?
- How much do all employees earn in total?
By using rollup in combination with the grouping function we came up with the following query

This query results in the following output: Note the grouping function.
It shows the level of the group by
0: No grouping at this level
1: Grouping on this level

Now we can anwer the questions.
- How much do all SALESMAN earn in department 30? Answer: 5600
- How much do all employees earn in total? Answer: 27725

How can I refer to the (grouping) results in a report.

You could create a nested query or a view:

Now you can base your report on this query.

or

Note: I did some updates on the original emp-table...

Last week I read a nice article of my con-colleague Lucas Jellema about “PL/SQL Table-to-Java Bean (and Data to Java Bean Manager) generator - useful for data driven demos without database ” at the AMIS-blog on:http://technology.amis.nl/blog/?p=3272.

In this blog Lucas writes how he generated java-code from Pl/Sql. It drew me back to a little project I did for myself and my former employer as a BusinessDevelopment-job. I wanted to make a simple method to enable a convential custom development application, like a Designer/Developer Forms application, like we build a million times in the past. Since it is primarly aimed for the hundreds of Forms applications at our customers that are primarly build with Designer and Headstart, I could do it in pl/sql also or even create a Headstart utility for it. But for the latter option I would learn myself Headstart-utility principals again, that did not look to useful for me. Pl/Sql would do. But I would have it flexible having the ability to generate several pieces of code from one source.

The problem with the code of Lucas, that is when you see it as a problem, is that all the code generation is in pl/sql. To change the outcome would mean that you should change your pl/sql code. I would like to have a more template-based solution.

A few years ago I wrote an article on “XML using Sql” for our monthly consulting paper at Oracle. See my post: http://darwin-it.blogspot.com/2008/07/xml-using-sql.html. With this knowledge it is easy to generate XML out of the datadictionary of the database. Then based on this xml you can generate all you want using ... XSLT.

Read further (pdf).

A few years ago already I created an article that explained how to generate XML using Oracle SQL. It works from Oracle 9iR2,10g onwards. It's in Dutch, but it contains step-by-step examples so maybe also usefull for non-Dutch.
You can download the article here.

The application described in the previous post consists of a lot of tables.
Some of them are populated by the developers and are not maintainable by end users.
Most of these tables have columns that have to be translated. We found a generic solution for these kind of tables.

In the application all tables have an artificial primary key that is populated by a sequence.
We created a table [column_translations] with the following structure

create table COLUMN_TRANSLATIONS
(
 ID                      NUMBER(10)                  not null
 ,TABLE_NAME              VARCHAR2(30)      not null
 ,COLUMN_NAME             VARCHAR2(30)    not null
 ,RECORD_ID               NUMBER(14)           not null
 ,LANGUAGE                VARCHAR2(2)          not null
 ,TRANSLATION             VARCHAR2(4000) not null
)

Given a table name/column name and a record id the translation is actually stored. Around this table a generic function is created that retrieves the translation given a certain language

function get_translation
( p_table_name        in column_translations.table_name%type
, p_column_name      in column_translations.column_name%type
, p_record_id          in column_translations.record_id%type
, p_language            in column_translations.language%type
)
return column_translations.translation%type
;

Now every table gets a seperate view that contains translations for the columns that needs to be translated.
For example:

create or replace view general_messages_vw
(id, code, message, .....)
as
select osc.id
,      osc.code
,      nvl(package.get_translation
      ('GENERAL_MESSAGES'
      ,'MESSAGE'
      , osc.id
      ,package.get_language -- this function returns the actual language
      )
     ,....
     )

Now you have to redirect all calls to the original table to the newly created view. You can do this by shuffling around synonyms and grants.
We decided to actually redirect the calls by changing the calls in database and in the Oracle Forms and Oracle Reports.
We used this mechanism only for referential tables that are not maintained by end-users but you could use it for other referential tables as well.