Handling PL / SQL Exceptions in Procedures

Question

This is a question about best practices. I have a PL / SQL block like this

DECLARE

    --work variables

    PROCEDURE p1(in_parameter1, out_parameter1, out_parameter2...) IS
    BEGIN    
        --do stuff
        --select ( ... ) into ( ... ) from t1 where ( ... )
    END;

    PROCEDURE p2(in_parameter1, out_parameter1, out_parameter2...) IS
    BEGIN 
        --do stuff
        --insert/update tables
        --do more stuff
    END;    

BEGIN -- MAIN PROCESS STARTS HERE

    open c1;
    fetch c1 into c1RowData;
    EXIT WHEN c1%NOTFOUND    
     --call procedure1
     --do stuff
     --call procedure2
     --do stuff
     --do stuff
     --call procedure1
     --call procedure2
END;
/
EXIT;

The p1 and p2 operators can throw exceptions (NO_DATA_FOUND, DUP_VAL_ON_INDEX, ...).

What do you think is the best way to handle these exceptions? Should they be processed inside the procedures or do you think that I should surround each procedure call in the main body with the TRY-CATCH block?

Answer 1

, . , , . , , , .

'Failed to find a matching row in table a for value b'

,

'no data found'

:

• , ,

, , 2, 1 . 1 . , 2.

, , , 1 , , .

, - , , , , , .

Answer 2

(.. , ). , , , , , Oracle .

, , , StevieG , , , , / PL/SQL.

ASKTOM : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4684561825338

Answer 3

, . . , , . , , , .