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

0

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
is
  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)
      is
      begin
        l_string_tbl.extend;
        l_string_tbl(l_string_tbl.last):= p_text;
      end add;
   begin
      add('CG$Error Stack:');
      add('---------------');
      cg$errors.get_error_messages
      ( l_message_rectype_tbl
      , l_message_count
      , l_raise_error
      );
      if l_message_count > 0
      then
         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;
   end;
 /
 

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

 create view qms_errors
 as
 select *
 from   table( get_qms_errors)
 /
 

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

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.