<p>Hallo Together,</p><p> </p><p>I've a question how to propagate error messages (Exceptions) from ORACLE stored procedures to BIRT.<br />
<br />
Assume, you have a stored procedures HALLO which returns an SYSCURSOR and an error indicator (let say o_message). If something goes wrong within the stored procedure, o_message will hold the appropriate error message (see example below).<br />
<br />
The problem is, that if the exception was raised <strong><em>before</em></strong> the OPEN....Cursor statement, BIRT does not have any information about the return columns and will complain about this:<br />
<em> -> "The following items have errors: Column binding .... has referred to a data set column ... which does not exist"</em><br />
Because of this only error messages from the stored procedures, which have been raised <strong><em>after</em></strong> the CURSOR opening can be handled correctly.<br />
<br />
So, how can I print out PL/SQL exception messages within BIRT in any(!) case ?<br />
<br />
I have added a sample procedure.<br />
<br />
THanks in advance ! Heiko<br />
<br />
<br />
<span style="font-size:10px;">[font="'courier new', courier, monospace;"]CREATE OR REPLACE PROCEDURE hallo ( o_cursor OUT SYS_REFCURSOR, o_message IN OUT VARCHAR2) IS[/font]</span></p><p><span style="font-size:10px;">[font="'courier new', courier, monospace;"]x INTEGER;[/font]</span></p><p><span style="font-size:10px;">[font="'courier new', courier, monospace;"]BEGIN<br />
<br />
-- BIRT will not be able to print out this exception<br />
SELECT 1 / 0 INTO x FROM DUAL; -- => Will raise ORA-01476: Divisor is zero , [color=#0000cd;]but BIRT will complain about the missing cursor columns first[/color][/font]</span></p><p> </p><p><span style="font-size:10px;">[font="'courier new', courier, monospace;"]OPEN o_cursor FOR<br />
SELECT * FROM heiko_bike<br />
WHERE LOWER(color)=DECODE(i_color,NULL,LOWER(color),LOWER(i_color));[/font]</span></p><p> </p><p><span style="font-size:10px;">[font="'courier new', courier, monospace;"]-- BIRT can print out this exception<br />
SELECT 1 INTO x FROM DUAL WHERE rownum=0; -- => Will raise ORA-01403: NO_DATA_FOUND: <br />
o_message:='everthing fine';[/font]</span><br />
</p><p><span style="font-size:10px;">[font="'courier new', courier, monospace;"]EXCEPTION<br />
WHEN OTHERS THEN<br />
o_message:=SUBSTR('! Ups :'||sqlerrm,1,30);<br />
END hallo ;[/font]</span></p>
