4 Replies Latest reply: Aug 8, 2007 4:55 PM by 591942 RSS

    PL/SQL table Bulk Insert and Bulk Update

    Forms
      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
          Sho me your efforts...
          • 2. Re: PL/SQL table Bulk Insert and Bulk Update
            Forms
            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
              Forms
              Hi,

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

              Thanks,
              • 4. Re: PL/SQL table Bulk Insert and Bulk Update
                591942
                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;