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

Lucas Jellema
0 0
Read Time:1 Minute, 41 Second

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.

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %
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 […]
%d bloggers like this: