View to select CG$ERRORS from (easing RuleFrame pain using a Table Function)

Lucas Jellema

On my current project I am doing a lot of work with CDM RuleFrame and the Oracle Designer Table API. I just learned that the TAPI cannot survive MERGE operations, which meant I had to rewrite a substantial volume of code. One other frustration is the cg$errors package, stack etc. Time and again I need to write some PL/SQL to retrieve the values from the message stack. No more. I have created a simple function and view that allow we to simply select the cg$errors using select * from qms_errors. It may not even be the best way of doing it, but I thought I’d just share it.
First create a database collection type:

create type string_tbl_type is table of varchar2(4000)

Then create the function that will return the collection holding the strings from the message stack:

create or replace function get_qms_errors
return string_tbl_type
  l_string_tbl string_tbl_type:= string_tbl_type();
  l_message_rectype_tbl hil_message.message_tabtype;
  l_message_count number := 0;
  l_error         varchar2(2000);
  l_raise_error   boolean := false;
      procedure add(p_text in varchar2)
        l_string_tbl(l_string_tbl.last):= p_text;
      end add;
      add('CG$Error Stack:');
      ( l_message_rectype_tbl
      , l_message_count
      , l_raise_error
      if l_message_count > 0
         for i in 1..l_message_count loop
            l_error := cg$errors.get_display_string
                       ( p_msg_code => l_message_rectype_tbl(i).msg_code
                       , p_msg_text => l_message_rectype_tbl(i).msg_text
                       , p_msg_type => l_message_rectype_tbl(i).severity
            add( l_error);
         end loop;
      end if;
      return l_string_tbl;

Now you are ready to create a view that will select from this function:

 create view qms_errors
 select *
 from   table( get_qms_errors)

From now on, you can find the errors on the CG$ERRORS stack using select * from qms_errors.

Next Post

SQLX How to easily generate XML

I recently had the need to generate XML files based on data stored in relational tables. This was done via an XML DOM implementation on the project I am currently working on. This works fine, but it’s difficult to maintain. Modifications have to be made simple because the XSD’s are […]