Accessing CDM Ruleframe error stack.

Accessing CDM Ruleframe's error stack is usually a pain. The "canonical" method, that is, the one I used, was a tricky construction involving autonomous_session and dbms_pipe. Then, one day, I found something vastly better by Lucas Jellema.

It uses a view on a table function to access the error stack. Very neat.

Here is the cut and paste:

create or replace type string_tbl_type is table of(4000);
/

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;
/

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

select * from qms_errors;

drop view qms_errors;
/

drop function get_qms_errors;
/

drop type string_tbl_type;
/

SQL string tokenizer

There are many ways to tokenize a string in Oracle, most of them involving PL/SQL.
If you wish to use the API, at first glance and based upon its name, DBMS_UTILITY.COMMA_TO_TABLE seems to be a good solution. Well, it is not, because, among others: "The procedure fails if the string between separators is longer than 30 bytes".
You will have to dig a little deeper to find APEX_UTIL.STRING_TO_TABLE, or create your own function.

A good solution, detailed by ALEX NUIJTEN is to use a hierarchical query that uses a regular expression to transform your string into a list:

SQL> select regexp_substr('this,is,a,list','[^,]+',1,rownum) ord_list_element
  2  from dual
  3  connect by level<=length(regexp_replace('this,is,a,list','[^,]+'))+1
  4  ;

ORD_LIST_ELEME
--------------
this
is
a
list

SQL>

From there, you may wish to write your own COMMA_TO_TABLE:

SQL> CREATE OR REPLACE PACKAGE MY_UTILITY
  2  IS
  3    /* To be used instead of dbms_utility.lname_array */
  4    TYPE LNAME_ARRAY IS TABLE OF VARCHAR2(32667) INDEX BY BINARY_INTEGER;
  5    /* Tokenizes a comma separated list. */
  6    PROCEDURE COMMA_TO_TABLE(LIST IN VARCHAR2
  7                            ,TABLEN OUT BINARY_INTEGER
  8                            ,TAB OUT MY_UTILITY.LNAME_ARRAY);
  9  END MY_UTILITY;
 10  /

Package crÚÚ.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY MY_UTILITY IS
  2    /* Tokenizes a comma separated list. */
  3    PROCEDURE COMMA_TO_TABLE(LIST IN VARCHAR2
  4                            ,TABLEN OUT BINARY_INTEGER
  5                            ,TAB OUT MY_UTILITY.LNAME_ARRAY)
  6    IS
  7    BEGIN
  8        select regexp_substr(LIST, '[^,]+',1,rownum) astring
  9        bulk collect into TAB
 10        from dual
 11        connect by level<= length(regexp_replace(LIST, '[^,]+'))+1;
 12        TABLEN := TAB.count;
 13    END COMMA_TO_TABLE;
 14  END MY_UTILITY;
 15  /

Corps de package crÚÚ.

SQL>

Cleanup:

drop package my_utility;