exception handling bulk collect


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: exception handling bulk collect

  1. #1
    josie cayetano Guest

    exception handling bulk collect


    This program works successfully inserting records but when I attempt to add
    the error_table for exception handling it bombs saying 'indx must not declared'.
    How can I make this work correctly so any exceptions get loaded to my errors
    table. Examples would be appreciated. Thanks,

    declare
    a_bank_acct_id dbms_sql.number_table ;
    a_bank_acct_name dbms_sql.varchar2_table ;
    a_acct_number dbms_sql.varchar2_table ;
    a_bank_id dbms_sql.varchar2_table ;
    a_update_date dbms_sql.date_table ;
    a_updated_by dbms_sql.number_table ;
    a_updated_login dbms_sql.number_table ;
    a_create_date dbms_sql.date_table ;
    a_created_by dbms_sql.number_table ;
    a_bank_acct_num dbms_sql.varchar2_table ;
    a_bank_branch_id dbms_sql.number_table ;
    a_sob_id dbms_sql.number_table ;
    a_currency dbms_sql.varchar2_table ;
    a_bank_acct_type dbms_sql.varchar2_table ;
    a_currency_flag dbms_sql.varchar2_table ;
    a_acct_type dbms_sql.varchar2_table ;
    a_org_id dbms_sql.number_table ;
    a_acct_holder dbms_sql.varchar2_table ;
    ---
    a_agy_rgn_dist dbms_sql.varchar2_table ;
    a_vend_type dbms_sql.varchar2_table ;
    a_vendor_ssn dbms_sql.varchar2_table ;
    a_address_name dbms_sql.varchar2_table ;
    a_irs_id dbms_sql.varchar2_table ;
    a_site_name dbms_sql.varchar2_table ;
    a_rec_ctr dbms_sql.number_table ;
    ---
    v_tot_cnt number := 0 ;
    v_row_cnt PLS_INTEGER := 0 ;
    v_bulk_collect_limit PLS_INTEGER := 1000 ;
    v_dup_ctr PLS_INTEGER := 0 ;
    v_oth_ctr PLS_INTEGER := 0 ;
    ---
    CURSOR c1 IS
    select
    BANK_ACCOUNTS_S.NEXTVAL,
    ACCOUNT_HOLDER,
    ACCOUNT_NUMBER,
    BANK_ID ,
    SYSDATE,
    CONV_STANDARD.CREATED_BY_CONVERSION,
    CONV_STANDARD.CREATED_BY_CONVERSION,
    TO_DATE('01-OCT-92', 'DD-MON-RR'),
    CONV_STANDARD.CREATED_BY_CONVERSION,
    ACCOUNT_NUMBER,
    CONV_FUNCTIONS.BANK_BRANCH_ID(BANK_ID),
    CONV_FUNCTIONS.PO_SET_OF_BOOKS_ID(AGY_RGN_DIST),
    'USD',
    DECODE(ACCOUNT_TYPE, 'C', 'Checking', 'S', 'Saving', NULL) ,
    'N',
    'SUPPLIER',
    CONV_STANDARD.PO_ORG_ID(AGY_RGN_DIST),
    ACCOUNT_HOLDER
    FROM
    CUSTOM.ACQ_VENDORS ;
    begin
    v_row_cnt := 0 ;
    open c1 ;
    loop
    fetch c1 bulk collect
    into
    a_bank_acct_id ,
    a_bank_acct_name ,
    a_acct_number ,
    a_bank_id ,
    a_update_date ,
    a_updated_by ,
    a_updated_login ,
    a_create_date ,
    a_created_by ,
    a_bank_acct_num ,
    a_bank_branch_id ,
    a_sob_id ,
    a_currency ,
    a_bank_acct_type ,
    a_currency_flag ,
    a_acct_type ,
    a_org_id ,
    a_acct_holder
    limit v_bulk_collect_limit ;
    --
    if c1%NOTFOUND AND v_row_cnt = c1%ROWCOUNT
    then
    exit ;
    else
    v_row_cnt := v_row_cnt + 1 ;
    end if ;
    --
    BEGIN
    FORALL indx IN a_bank_acct_name.FIRST ..a_bank_acct_name.LAST
    INSERT INTO jdc_test_table
    ( bank_account_id,
    bank_account_name,
    last_update_date,
    last_updated_by,
    last_update_login,
    creation_date,
    created_by,
    bank_account_num,
    bank_branch_id,
    set_of_books_id,
    currency_code,
    bank_account_type,
    multi_currency_flag,
    account_type,
    org_id,
    account_holder_name
    )
    VALUES
    ( a_bank_acct_id (indx) ,
    a_bank_acct_name (indx) ,
    a_update_date (indx) ,
    a_updated_by (indx) ,
    a_updated_login (indx) ,
    a_create_date (indx) ,
    a_created_by (indx) ,
    a_bank_acct_num (indx) ,
    a_bank_branch_id (indx) ,
    a_sob_id (indx) ,
    a_currency (indx) ,
    a_bank_acct_type (indx) ,
    a_currency_flag (indx) ,
    a_acct_type (indx) ,
    a_org_id (indx) ,
    a_acct_holder (indx)
    ) ;
    EXCEPTION
    when dup_val_on_index then
    v_dup_ctr := v_dup_ctr + 1 ;
    begin
    update ACQ_VENDOR_ERRORS
    SET AGY_RGN_DIST = a_agy_rgn_dist (indx) ,
    VEND_TYPE = a_vend_type (indx) ,
    VENDOR_SSN = a_vendor_ssn (indx) ,
    ADDRESS_NAME = a_address_name (indx) ,
    IRS_ID = a_irs_id (indx) ,
    SITE_NAME = a_site_name (indx) ,
    INSERT_FLAG = 'D' ,
    rec_ctr = a_rec_ctr (indx) ,
    ERROR_MESSAGE = 'test' ;
    end ;
    -- if v_dup_ctr = 1 then
    -- dbms_output.put_line(SUBSTR( SQLERRM, 1, 450 )) ;
    -- end if ;
    when others then
    v_oth_ctr := v_oth_ctr + 1 ;
    if v_oth_ctr = 1 then
    dbms_output.put_line(SUBSTR( SQLERRM, 1, 450 )) ;
    end if ;
    END ;
    end loop ;
    close c1 ;
    rollback ;
    dbms_output.put_line('total count: ' || v_row_cnt ) ;
    end ;


  2. #2
    Boris Milrud Guest

    Re: exception handling bulk collect


    Josie,

    You can not make any references to index variable ("inxd" in your case) outside
    of FORALL statement.

    Which version of Oracle database are you on?

    Oracle 9i offers new SAVE EXCEPTIONS clause of FORALL command. It causes
    the FORALL loop to continue even if some DML operations fail. The details
    of the errors are available after the loop in SQL%BULK_EXCEPTIONS. Sounds
    like that's exactly what you need, right?

    All exceptions raised during the execution are saved in the new cursor attribute
    %BULK_EXCEPTIONS, which stores a collection of records. Each record has two
    fields. The first field, %BULK_EXCEPTIONS(i).ERROR_INDEX, holds the "iteration"
    of the FORALL statement during which the exception was raised. The second
    field, %BULK_EXCEPTIONS(i).ERROR_CODE, holds the corresponding Oracle error
    code. The values stored by %BULK_EXCEPTIONS always refer to the most recently
    executed FORALL statement.

    If you are interested, I could post a sample code to show the usage of this
    Oracle 9i feature. However, if you are on Oracle 8i, then you have to do
    the all the work programatically.

    Boris.

    "josie cayetano" <josie_cayetano@hotmail.com> wrote:
    >
    >This program works successfully inserting records but when I attempt to

    add
    >the error_table for exception handling it bombs saying 'indx must not declared'.
    >How can I make this work correctly so any exceptions get loaded to my errors
    >table. Examples would be appreciated. Thanks,
    >
    >declare
    > a_bank_acct_id dbms_sql.number_table ;
    > a_bank_acct_name dbms_sql.varchar2_table ;
    > a_acct_number dbms_sql.varchar2_table ;
    > a_bank_id dbms_sql.varchar2_table ;
    > a_update_date dbms_sql.date_table ;
    > a_updated_by dbms_sql.number_table ;
    > a_updated_login dbms_sql.number_table ;
    > a_create_date dbms_sql.date_table ;
    > a_created_by dbms_sql.number_table ;
    > a_bank_acct_num dbms_sql.varchar2_table ;
    > a_bank_branch_id dbms_sql.number_table ;
    > a_sob_id dbms_sql.number_table ;
    > a_currency dbms_sql.varchar2_table ;
    > a_bank_acct_type dbms_sql.varchar2_table ;
    > a_currency_flag dbms_sql.varchar2_table ;
    > a_acct_type dbms_sql.varchar2_table ;
    > a_org_id dbms_sql.number_table ;
    > a_acct_holder dbms_sql.varchar2_table ;
    > ---
    > a_agy_rgn_dist dbms_sql.varchar2_table ;
    > a_vend_type dbms_sql.varchar2_table ;
    > a_vendor_ssn dbms_sql.varchar2_table ;
    > a_address_name dbms_sql.varchar2_table ;
    > a_irs_id dbms_sql.varchar2_table ;
    > a_site_name dbms_sql.varchar2_table ;
    > a_rec_ctr dbms_sql.number_table ;
    > ---
    > v_tot_cnt number := 0 ;
    > v_row_cnt PLS_INTEGER := 0 ;
    > v_bulk_collect_limit PLS_INTEGER := 1000 ;
    > v_dup_ctr PLS_INTEGER := 0 ;
    > v_oth_ctr PLS_INTEGER := 0 ;
    > ---
    > CURSOR c1 IS
    > select
    > BANK_ACCOUNTS_S.NEXTVAL,
    > ACCOUNT_HOLDER,
    > ACCOUNT_NUMBER,
    > BANK_ID ,
    > SYSDATE,
    > CONV_STANDARD.CREATED_BY_CONVERSION,
    > CONV_STANDARD.CREATED_BY_CONVERSION,
    > TO_DATE('01-OCT-92', 'DD-MON-RR'),
    > CONV_STANDARD.CREATED_BY_CONVERSION,
    > ACCOUNT_NUMBER,
    > CONV_FUNCTIONS.BANK_BRANCH_ID(BANK_ID),
    > CONV_FUNCTIONS.PO_SET_OF_BOOKS_ID(AGY_RGN_DIST),
    > 'USD',
    > DECODE(ACCOUNT_TYPE, 'C', 'Checking', 'S', 'Saving', NULL) ,
    > 'N',
    > 'SUPPLIER',
    > CONV_STANDARD.PO_ORG_ID(AGY_RGN_DIST),
    > ACCOUNT_HOLDER
    > FROM
    > CUSTOM.ACQ_VENDORS ;
    > begin
    > v_row_cnt := 0 ;
    > open c1 ;
    > loop
    > fetch c1 bulk collect
    > into
    > a_bank_acct_id ,
    > a_bank_acct_name ,
    > a_acct_number ,
    > a_bank_id ,
    > a_update_date ,
    > a_updated_by ,
    > a_updated_login ,
    > a_create_date ,
    > a_created_by ,
    > a_bank_acct_num ,
    > a_bank_branch_id ,
    > a_sob_id ,
    > a_currency ,
    > a_bank_acct_type ,
    > a_currency_flag ,
    > a_acct_type ,
    > a_org_id ,
    > a_acct_holder
    > limit v_bulk_collect_limit ;
    > --
    > if c1%NOTFOUND AND v_row_cnt = c1%ROWCOUNT
    > then
    > exit ;
    > else
    > v_row_cnt := v_row_cnt + 1 ;
    > end if ;
    > --
    > BEGIN
    > FORALL indx IN a_bank_acct_name.FIRST ..a_bank_acct_name.LAST
    > INSERT INTO jdc_test_table
    > ( bank_account_id,
    > bank_account_name,
    > last_update_date,
    > last_updated_by,
    > last_update_login,
    > creation_date,
    > created_by,
    > bank_account_num,
    > bank_branch_id,
    > set_of_books_id,
    > currency_code,
    > bank_account_type,
    > multi_currency_flag,
    > account_type,
    > org_id,
    > account_holder_name
    > )
    > VALUES
    > ( a_bank_acct_id (indx) ,
    > a_bank_acct_name (indx) ,
    > a_update_date (indx) ,
    > a_updated_by (indx) ,
    > a_updated_login (indx) ,
    > a_create_date (indx) ,
    > a_created_by (indx) ,
    > a_bank_acct_num (indx) ,
    > a_bank_branch_id (indx) ,
    > a_sob_id (indx) ,
    > a_currency (indx) ,
    > a_bank_acct_type (indx) ,
    > a_currency_flag (indx) ,
    > a_acct_type (indx) ,
    > a_org_id (indx) ,
    > a_acct_holder (indx)
    > ) ;
    > EXCEPTION
    > when dup_val_on_index then
    > v_dup_ctr := v_dup_ctr + 1 ;
    > begin
    > update ACQ_VENDOR_ERRORS
    > SET AGY_RGN_DIST = a_agy_rgn_dist (indx)

    ,
    > VEND_TYPE = a_vend_type (indx)

    ,
    > VENDOR_SSN = a_vendor_ssn (indx)

    ,
    > ADDRESS_NAME = a_address_name (indx)

    ,
    > IRS_ID = a_irs_id (indx)

    ,
    > SITE_NAME = a_site_name (indx)

    ,
    > INSERT_FLAG = 'D'

    ,
    > rec_ctr = a_rec_ctr (indx)

    ,
    > ERROR_MESSAGE = 'test' ;
    > end ;
    > -- if v_dup_ctr = 1 then
    > -- dbms_output.put_line(SUBSTR( SQLERRM, 1, 450 )) ;
    > -- end if ;
    > when others then
    > v_oth_ctr := v_oth_ctr + 1 ;
    > if v_oth_ctr = 1 then
    > dbms_output.put_line(SUBSTR( SQLERRM, 1, 450 )) ;
    > end if ;
    > END ;
    > end loop ;
    > close c1 ;
    > rollback ;
    > dbms_output.put_line('total count: ' || v_row_cnt ) ;
    > end ;
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center