Procedure question

[ssmith001]

I have a procedure that contains about 40 queries. One of these queries is bombing out with an "ORA-01422: exact fetch returns more than requested number of rows" error. Here's my question...short of putting DBMS_OUTPUT statements before and after each query to find out which one is causing the error, is there an easier way to trap which query is generating the error? I use WHENEVER SQLERROR EXIT SQL.SQLCODE which works great, but it doesn't tell me which query caused the error.

[marist89]

Originally posted by ssmith001
I have a procedure ...I use WHENEVER SQLERROR EXIT SQL.SQLCODE which works great

Not in a procedure you don't. Maybe you could explain what you are trying to do more clearly with examples. (and not all 40 queries)

[ssmith001]

Here's an example of 1 of the queries...

SELECT ltrim(to_char(NVL(SUM(sc.haul_cost),0),'0999999.99')) INTO l_bol_line_haul
FROM fp_so_cost sc
, fp_shipment_order so
WHERE so.cost_key = sc.cost_key
AND sc.cost_type = 'I'
AND so.load_id = bol_rec.inv_bol;

Is there anything I can do before or after executing this SQL stmt that will help me know if there has been any SQL errors during execution?

[marist89]

sigh.

Do you have these multiple queries in a script that you are running via sqlplus?

Have you experimented with the "set" commands ("set echo on", perhaps).