This discussion is archived
4 Replies Latest reply: Aug 8, 2007 2:55 PM by 591942 RSS

PL/SQL table Bulk Insert and Bulk Update

540531 Newbie
Currently Being Moderated
hi,

I have to load data from staging table to the main table.

I have this problem. I am bulk inserting data using FOR ALL clause. But it is not necessary that all the records in the PL/SQL table to be inserted (coz of data Errors).

So I am using the Save Exceptions clause and I am identifying the Errored Records. I am also populating the rowid into a nother PL/SQL table indexed by Varchar2.

At the end of the Bulk insert, I just need to Bulk update a flag on the stagiung only for the records that have been successfully inserted. (Bulk Update approach only)

Is there any way to accomplish this. Please help me. This is very urgent.

Thanks
  • 1. Re: PL/SQL table Bulk Insert and Bulk Update
    591942 Newbie
    Currently Being Moderated
    Sho me your efforts...
  • 2. Re: PL/SQL table Bulk Insert and Bulk Update
    540531 Newbie
    Currently Being Moderated
    Hi,

    Will this work?

    BEGIN

         FORALL i IN t_item.FIRST .. t_item.LAST SAVE EXCEPTIONS
         INSERT INTO tran_data
         (item, rms_dept, dept, class, subclass, pack_ind, store, wh, tran_date,
         tran_code, dtl_code, adj_code, units, total_cost, total_retail, ref_no_1,
         ref_no_2, old_unit_retail, new_unit_retail, pgm_name, sales_type, vat_rate,
         av_cost, timestamp, include_gp_ind, gp_gl_ind, ap_ind, sdd_vendor_ind, vendor_no,
         ap_batch_no, invoice_type, invoice_no, batch_no, doc_no, duns_no)
         VALUES
         (t_item(i), t_rms_dept(i), t_dept(i), t_class(i),t_subclass(i),NULL, t_store(i),
         t_wh(i), t_tran_date(i),t_tran_code(i), t_dtl_code(i), NULL, NULL,t_total_cost(i),
         t_total_retail(i), NULL, NULL, NULL, NULL, t_pgm_name(i),NULL, NULL,
         t_total_sup_cost(i), t_create_datetime(i),t_include_gp_ind(i), t_gp_gl_ind(i),
         t_ap_ind(i),t_sdd_vendor_ind(i), t_vendor_no(i), t_ap_batch_no(i), t_invoice_type(i),
         t_invoice_no(i), t_batch_no(i), t_doc_no(i), t_duns_no(i));
         

    --- Additional PL/SQL Tables Fetched

         ---t_process_ind(i), t_create_id(i),
         --- t_last_update_id(i), t_last_update_datetime(i),t_rowid(i));

         --- Additional PL/SQL Tables Fetched
    EXCEPTION
    WHEN bulk_errors THEN
    dbms_output.put_line('I am in the Bulk Exception handler');
    FOR j in 1..SQL%BULK_EXCEPTIONS.COUNT
    LOOP
         
         L_error_ind:=SQL%BULK_EXCEPTIONS(j).error_index;
         L_error_message:= SQLERRM(-SQL%bulk_exceptions(j).error_code);
    t_err_rowid(t_rowid(L_error_ind)):= t_rowid(L_error_ind);
    END LOOP;
    END;
    --- Begin block added To Trap the errored Records Ends

         --- Updating Maintenance Records as Processed
    L_upd_counter:=1;
    FOR i in t_rowid.FIRST .. t_rowid.LAST LOOP

    IF NOT t_err_rowid.EXISTS(t_rowid(i)) THEN
    t_upd_rowid(L_upd_counter):=t_rowid(i);
    L_upd_counter:= L_upd_counter+1;
    END IF;

    END LOOP;

         FORALL i IN t_upd_rowid.FIRST .. t_upd_rowid.LAST
         UPDATE anp_slg_tran_data_stg
         SET process_ind='Y'
         WHERE ROWID= t_upd_rowid(i);
  • 3. Re: PL/SQL table Bulk Insert and Bulk Update
    540531 Newbie
    Currently Being Moderated
    Hi,

    Can anyone please let me know the answer for my questions?

    Thanks,
  • 4. Re: PL/SQL table Bulk Insert and Bulk Update
    591942 Newbie
    Currently Being Moderated
    create or replace procedure test123 as
    TYPE STG_TEID IS TABLE OF STG.EID%TYPE INDEX BY BINARY_INTEGER;
    TYPE STG_TENAME IS TABLE OF STG.ENAME%TYPE INDEX BY BINARY_INTEGER;
    TYPE rec_lookup IS RECORD
         ( EID STG.EID%TYPE,
         ENAME STG.ENAME%TYPE);

    STG_PLTBLEID STG_TEID ;
    STG_PLTBLENAME STG_TENAME ;
    TYPE tbl_lookup IS TABLE OF rec_lookup INDEX BY BINARY_INTEGER;
    TMP_STG_PLTBL tbl_lookup ;

    errors NUMBER;
    dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);



    BEGIN

    SELECT EID,ENAME BULK COLLECT INTO STG_PLTBLEID ,STG_PLTBLENAME FROM STG;


    begin
    FORALL i IN STG_PLTBLEID.FIRST .. STG_PLTBLEID.LAST SAVE EXCEPTIONS
    --for i in STG_PLTBLEID.FIRST..STG_PLTBLEID.LAST
    --loop
    INSERT INTO maintbl(eid,ename) VALUES(STG_PLTBLEID(i),STG_PLTBLENAME(i))
    returning eid, ename BULK COLLECT INTO TMP_STG_PLTBL ;
    --end loop;
    EXCEPTION
    WHEN dml_errors THEN -- Now we figure out what failed and why.
    errors := SQL%BULK_EXCEPTIONS.COUNT;
    DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || errors);
    FOR i IN 1..errors LOOP
    DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||
    'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
    DBMS_OUTPUT.PUT_LINE('Error message is ' ||
    SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
    END;


    for i in TMP_STG_PLTBL.FIRST .. TMP_STG_PLTBL.LAST loop
    UPDATE STG
    SET ERRFLG='Y'
    WHERE eid = TMP_STG_PLTBL(i).eid and ename=TMP_STG_PLTBL(i).ename ;--.eid and ename=TMP_STG_PLTBL(i).ename;
    end loop;
    END;