exception handling bulk collect

[josie cayetano]

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 ;

[Boris Milrud]

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 ;
>