Oracle exception handling for .NET application

Question

Below is my code snippet from a Oracle stored procedure which inserts into a table "A" from table "B", I have created a nested block (as I would have more insert statements later, so wanted to maintain some modularity). I have exception block with the generic handler "others" for catching the errors, and within that I have an autonomous procedure log_errors_SP which I am using to log errors. I have used raise_application_error within the exception block to notify the invoker of this procedure(.NET code) about exceptions else update the log table with completion status (the last update statement in the code). I need to understand:-

1. if this the correct way to program as for some reason when the procedure is executed from .NET code the log_errors_sp is not executed.
2. log_errors_sp is executed fine when I run my parent stored procedure manually from Oracle developer (so no issues with log_errors_sp).
3. Is it because the error is being trapped on .NET side by it's try catch block rather than the stored procedure's exception block ?.
4. if point 2 is valid how can I change the stored procedure to have the log_errors_SP invoked ?.

create or replace procedure some_sp (P_JSON CLOB) is
BEGIN
    do  the basic IN parameter check here..

--nested block to insert data
BEGIN
    insert into A
    (
        CREATED_DT,
        CREATEDBY,
        LASTUPDATED_DT,
        LASTUPDATEDBY
    )
        select sysdate,'SMICLOAD',sysdate,' '
            from b where <conditions>;
                
    EXCEPTION
            WHEN OTHERS THEN
            ov_err_code := 'Table Fail: '||SQLERRM;
            log_errors_SP (p_id,ov_err_code);
            raise_application_error(-20101, 'A:Unable to Insert into the table');
        
    END;
     
    
     UPDATE p_prcs_log 
        set
        COMPLETION_TS=sysdate,
        STATUS='C',
        ERR_MSG='NONE'
        where prcs_id=p_id;
  

END

question from:https://stackoverflow.com/questions/65835917/oracle-exception-handling-for-net-application

Answer 1

Waitting for answers