0 
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.
Related posts:
Anydata and Collections Death of the End Date – How LAG and LEAD help fight redundancy 
Aspect Oriented Programming (AOP) in PL/SQL – Is it hot? Is it cool? Or utterly useless? Dynamic SQL Pivoting – Stealing Anton’s Thunder Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table
