CODEWITHSUNDEEP
oracle-databasefor-loopstored-proceduresplsql
Young Al Capone
Young Al Capone

Reputation: 399

How to handle exceptions in for loop for insert rows using a stored procedure? Oracle PLSQL

I'm coding a complex PLSQL block (complex for me hahaha) to insert rows using information from the FOR LOOP CURSOR and add parameters to insert using a stored procedure. The current problem is there are around 200 rows to be inserted but when a simple row fail to insert all rows inserted broke and oracle execute a ROLLBACK command (I think so). So... How could I handle exceptions to insert succefully all rounds I can and when any rows fail show it in screen? Thanks

FOR i IN c_mig_saldos LOOP
        IF i.tipo_comprobante = 'P' THEN -- Nota de debito (positivo)
            v_cmp_p.prn_codigo := 'VIV';
            v_cmp_p.tcm_codigo := 'NRA';
            v_cmp_p.cmp_fecha_emision := TRUNC(SYSDATE);
            v_cmp_p.cmp_fecha_contable := TRUNC(SYSDATE);
            v_cmp_p.cmp_observacion := 'GENERACION AUTOMATICA DE SALDOS';
            v_cmp_p.cli_codigo := i.cli_codigo;

            v_tab_dco_p(1).cnc_codigo := 'VIA';
            v_tab_dco_p(1).dco_precio_unitario := i.total_final;
            v_tab_dco_p(1).dco_cantidad := 1;
            v_tab_dco_p(1).dco_importe := i.total_final;

            -- Insert a new row using stored procedure but when a itereted fail, no rows has inserted in table
            PKG_COMPROBANTES.PRC_INSERTAR_COMPROBANTE(v_cmp_p, v_tab_dco_p, v_tab_pgc_p, v_tab_apl_p, v_tab_mar_p); 
            COMMIT;
        END IF;
END LOOP;

Upvotes: 1

Views: 2034

Answers (3)

Jon H
Jon H

Reputation: 11

Agree with comment that more information is needed, but a couple of things to consider:

  1. Does this need to be done as a loop - if you can write your query as a select statement, then you can do a simple insert without the need for PLSQL, which would be simpler and likely quicker (set based SQL vs row-by-row PLSQL)
  2. You say a ROLLBACK is occuring - you have a commit inside your IF statement, so any records which make it into your IF statement and succesfully make it through your insert procedure will be committed i.e. they will not be rolled back; You should consider if some records you think are being rolled back are actually not making it into the IF statement at all

Can you provide example data, or an example of the error you are receiving?

Upvotes: 0

Michael O'Neill
Michael O'Neill

Reputation: 954

To keep this simple, since there's not enough information to know the what and why of the question, this will kick out some text information about failures as desired.

SQL> set serveroutput on

Then here's an anonymous PL/SQL block:

BEGIN
    FOR i IN c_mig_saldos 
    LOOP
        -- not relevant

        BEGIN
            PKG_COMPROBANTES.PRC_INSERTAR_COMPROBANTE(v_cmp_p, v_tab_dco_p, v_tab_pgc_p, v_tab_apl_p, v_tab_mar_p); 
        EXCEPTION
            -- The goal of this is to ignore but output information about your failures
            WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('whatever you want about v_cmp_p, v_tab_dco_p, v_tab_pgc_p, v_tab_apl_p, v_tab_mar_p or SQLERRM/SQLCODE or the error stack - not enough info in the question');
        END;
    END LOOP;
END;
/

Note: I have removed the commit from the execution. Then if you like what you see...

SQL> commit;

Ideally, if I knew more about why the insert failures were expected to occur and what I wanted to do about them, I would not insert them in the first place.

Upvotes: 0

gfrobenius
gfrobenius

Reputation: 4067

-- Insert a new row using stored procedure but when a itereted fail, no rows has inserted in table
begin
    PKG_COMPROBANTES.PRC_INSERTAR_COMPROBANTE(v_cmp_p, v_tab_dco_p, v_tab_pgc_p, v_tab_apl_p, v_tab_mar_p);
    exception
        when others then --this could be made more specific but you didn't say what type of error you were getting
            -- Log to a table so you can export failed inserts later.
            -- Make sure log table cols are large enough to store everything that can possibly be inserted here...
            ErrorLogProc(the, cols, you, want, to, see, and, SQLERRM);
end;

In the ErrorLogProc() I'd recommend a couple things, here is a snippet of some things I do in my error logging proc that you may find helpful (it's just a few snippets, not intended to fully work, but you should get the idea)...

oname varchar2(100);
pname varchar2(100);
lnumb varchar2(100);
callr varchar2(100);
g1B CHAR(2) := chr(13)||chr(10);

PRAGMA AUTONOMOUS_TRANSACTION;  --important!

begin

  owa_util.who_called_me(oname, pname, lnumb, callr);

  --TRIM AND FORMAT FOR ERRORLOG
  lv_errLogText := 'Package: '||pname||' // Version: '||version_in||' // Line Number: '||lnumb||' // Error: ';
  lv_string1    := mid(errStr_in,1,4000-Length(lv_errLogText));
  lv_errLogText := lv_errLogText||lv_string1;
  lv_errLogText := lv_errLogText||g1B||'Error Backtrace: '||replace(dbms_utility.format_error_backtrace,'ORA-', g1b||'ORA-');

  insertIntoYourErrorLogTable(lv_errLogText, and, whatever, else, you, need);

commit;

Upvotes: 0

Related Questions