error handling

[Rishik01]

create or replace PROCEDURE BBVA_SP_MIGRATE_CEG
(
--parameter pass

x_migration_status OUT varchar2

) AS

--variable declarations

v_contract_number kndtctc.tctc_cncclipu%type;
v_customer_code kndtcct.tcct_cncctco%type;
v_customer_name varchar2(50);
v_customer_number varchar2(50);
v_account_number varchar2(50);
v_contact_name varchar2(50);
v_phone_number varchar2(25);
v_email_id varchar2(50);
v_connection_type varchar2(50);
v_LBX_OUT1 varchar2(10);
v_LBX_OUT2 varchar2(10);
v_LBX_OUT3 varchar2(10);
v_LBX_OUT4 varchar2(10);
v_LBX_OUT5 varchar2(10);
v_LBX_OUT6 varchar2(10);
v_LBX_OUT7 varchar2(10);
v_LBX_OUT8 varchar2(10);
v_LBX_OUT9 varchar2(10);
v_STOPFILE_IN1 varchar2(10);
v_ARP_OUT1 varchar2(10);
v_ARP_OUT2 varchar2(10);
v_ARP_OUT3 varchar2(10);
v_ARP_OUT4 varchar2(10);
v_ARP_OUT5 varchar2(10);
v_ARP_OUT6 varchar2(10);
v_ARP_OUT7 varchar2(10);
v_ARP_OUT8 varchar2(10);
v_ARP_OUT9 varchar2(10);
v_ARP_IN1 varchar2(10);
v_ARP_IN2 varchar2(10);
v_ARP_IN3 varchar2(10);
v_ARP_IN4 varchar2(10);
v_ARP_IN5 varchar2(10);
v_ARP_IN6 varchar2(10);
v_ARP_IN7 varchar2(10);
v_ARP_IN8 varchar2(10);
v_ARP_IN9 varchar2(10);
v_ACH_OUT1 varchar2(10);
v_ACH_OUT2 varchar2(10);
v_ACH_OUT3 varchar2(10);
v_ACH_OUT4 varchar2(10);
v_ACH_OUT5 varchar2(10);
v_ACH_OUT6 varchar2(10);
v_ACH_OUT7 varchar2(10);
v_ACH_OUT8 varchar2(10);
v_ACH_OUT9 varchar2(10);
v_ACH_IN1 varchar2(10);
v_ACH_IN2 varchar2(10);
v_ACH_IN3 varchar2(10);
v_ACH_IN4 varchar2(10);
v_ACH_IN5 varchar2(10);
v_ACH_IN6 varchar2(10);
v_ACH_IN7 varchar2(10);
v_ACH_IN8 varchar2(10);
v_ACH_IN9 varchar2(10);
v_ANL822FILE_OUT1 varchar2(10);
v_DEPOSITRACKFILE_OUT1 varchar2(10);
v_EDI_OUT1 varchar2(10);
v_CDA_OUT1 varchar2(10);
v_BAI_OUT1 varchar2(10);
v_ICL_IN1 varchar2(10);
v_RECONFILE_OUT1 varchar2(10);
v_SUSPENDCARDFILE_IN1 varchar2(10);
V_ceg_record_found varchar2(1);
v_REC_NUMBER number;
V_BANKCODE varchar2(25);
v_status varchar2(10);
v_timestamp TIMESTAMP(6):=systimestamp;
v_tx VARCHAR2(2000);
v_sysdate DATE := SYSDATE;
v_tz varchar2(2000);
v_num_err exception;

BEGIN

v_tx:=to_char(v_timestamp,'DD-MON-RR HH.MI.SSXFF AM');
v_tz:=to_char(v_sysdate, 'DD-MON-RR');

x_migration_status:='FAIL';
V_ceg_record_found:='N';

<>
for in_rec_number IN 1..500 LOOP

SELECT CUSTOMERNAME,
CUSTOMERNUMBER,
ACCOUNTNUMBER,
CONTACTNAME,
CONTACTPHONE,
CONTACTEMAIL,
CONNECTIONTYPE,
LBX_OUT1,
LBX_OUT2,
LBX_OUT3,
LBX_OUT4,
LBX_OUT5,
LBX_OUT6,
LBX_OUT7,
LBX_OUT8,
LBX_OUT9,
STOPFILE_IN1,
ARP_OUT1,
ARP_OUT2,
ARP_OUT3,
ARP_OUT4,
ARP_OUT5,
ARP_OUT6,
ARP_OUT7,
ARP_OUT8,
ARP_OUT9,
ARP_IN1,
ARP_IN2,
ARP_IN3,
ARP_IN4,
ARP_IN5,
ARP_IN6,
ARP_IN7,
ARP_IN8,
ARP_IN9,
ACH_OUT1,
ACH_OUT2,
ACH_OUT3,
ACH_OUT4,
ACH_OUT5,
ACH_OUT6,
ACH_OUT7,
ACH_OUT8,
ACH_OUT9,
ACH_IN1,
ACH_IN2,
ACH_IN3,
ACH_IN4,
ACH_IN5,
ACH_IN6,
ACH_IN7,
ACH_IN8,
ACH_IN9,
ANL822FILE_OUT1,
DEPOSITRACKFILE_OUT1,
EDI_OUT1,
CDA_OUT1,
BAI_OUT1,
ICL_IN1,
RECONFILE_OUT1,
SUSPENDCARDFILE_IN1,
'Y',
CEG_ID,
BANKCODE,
CEG_STATUS

INTO

v_customer_name,
v_customer_number,
v_account_number,
v_contact_name,
v_phone_number,
v_email_id,
v_connection_type,
v_LBX_OUT1,
v_LBX_OUT2,
v_LBX_OUT3,
v_LBX_OUT4,
v_LBX_OUT5,
v_LBX_OUT6,
v_LBX_OUT7,
v_LBX_OUT8,
v_LBX_OUT9,
v_STOPFILE_IN1,
v_ARP_OUT1,
v_ARP_OUT2,
v_ARP_OUT3,
v_ARP_OUT4,
v_ARP_OUT5,
v_ARP_OUT6,
v_ARP_OUT7,
v_ARP_OUT8,
v_ARP_OUT9,
v_ARP_IN1,
v_ARP_IN2,
v_ARP_IN3,
v_ARP_IN4,
v_ARP_IN5,
v_ARP_IN6,
v_ARP_IN7,
v_ARP_IN8,
v_ARP_IN9,
v_ACH_OUT1,
v_ACH_OUT2,
v_ACH_OUT3,
v_ACH_OUT4,
v_ACH_OUT5,
v_ACH_OUT6,
v_ACH_OUT7,
v_ACH_OUT8,
v_ACH_OUT9,
v_ACH_IN1,
v_ACH_IN2,
v_ACH_IN3,
v_ACH_IN4,
v_ACH_IN5,
v_ACH_IN6,
v_ACH_IN7,
v_ACH_IN8,
v_ACH_IN9,
v_ANL822FILE_OUT1,
v_DEPOSITRACKFILE_OUT1,
v_EDI_OUT1,
v_CDA_OUT1,
v_BAI_OUT1,
v_ICL_IN1,
v_RECONFILE_OUT1,
v_SUSPENDCARDFILE_IN1,
v_CEG_RECORD_FOUND,
v_REC_NUMBER,
V_BANKCODE,
V_Status

FROM t_ceg_migration
WHERE
upper(CEG_STATUS) = 'RECEIVED' and
in_rec_number = to_number(CEG_ID);

IF v_CEG_RECORD_FOUND = 'Y' THEN

DBMS_OUTPUT.PUT_LINE (in_rec_number);

-- get next sequence numbers
V_CONTRACT_NUMBER := tctc_cncclipu_seq.nextval;
V_CUSTOMER_CODE := tcct_cncctco_seq.nextval;

--then inserts into other tables, not relevant to the exception--

x_migration_status := to_char(v_contract_number);


UPDATE t_Ceg_migration
set CEG_status = 'IMPORTED'
where
upper(CEG_STATUS) = 'RECEIVED' and
in_rec_number = to_number(CEG_ID);

end if; -- ceg_record_found
This is where i put the exception after all the updates are supposed to take place for the other tables affected by this procedure.


BEGIN
IF v_rec_number > 500 THEN
RAISE v_num_err;
END IF;
EXCEPTION
WHEN v_num_err THEN
dbms_output.put_line ('Error: Try Again');
End;

end LOOP outer_loop;
END BBVA_SP_MIGRATE_CEG;

where would the exception go to ensure valid records are displayed before running the exception? I am just trying to make sure its in the right place because the procedure compiles ok but at run time it does display what im looking for too but gives an error along with it -

Connecting to the database service-DEV.
ORA-01403: no data found
ORA-06512: at "SERVICE.BBVA_SP_MIGRATE_CEG", line 95
ORA-06512: at line 5
1
2
3
Process exited.
Disconnecting from the database service-DEV.

[gandolf989]

The error no data found is caused by the select into not returning any rows. You are declaring 72 variables and not using
anywhere near that number of variables. You should only declare what you need. If you are declaring a variable for every
column in the t_ceg_migration table, then you should just declare one row level variable as in:

r_t_ceg_migration t_ceg_migration%ROWTYPE;

You can wrap the select into with a begin/end block. Then that begin/end block can have an

EXCEPTION WHEN NO_DATA_FOUND 
THEN

   DBMS_OUTPUT.PUT_LINE (in_rec_number);

   -- get next sequence numbers
   v_contract_number := tctc_cncclipu_seq.nextval;
   v_customer_code   := tcct_cncctco_seq.nextval;

   --then inserts into other tables, not relevant to the exception--

   x_migration_status := to_char(v_contract_number);

   UPDATE t_Ceg_migration
      SET CEG_status = 'IMPORTED'
    WHERE UPPER(CEG_STATUS) = 'RECEIVED' 
      AND in_rec_number = TO_NUMBER(CEG_ID);
END;

You still need to clean up the logic. But this might help you get started in the right direction.
You should buy and read this book as well as anything else that you can find from Stephen Feuerstein.

http://shop.oreilly.com/product/9780596514105.do

[Rishik01]

Thanks for the reply. But i am using the variables in the section where i have stated --then inserts into other tables, not relevant to the exception.

[gandolf989]

Based on the code that you posted above, "The error no data found is caused by the select into not returning any rows." still applies.
If there is other code that you did not post, then I am not commenting on that code, since I did not see it. With a select into you need
to handle the condition where no data gets returned.

Did you copy this code from another site online? If so you may want to go back to the person that you copied it from. Since this looks
like a Peoplesoft database, you might also want to check with Oracle to see if they can help you with the logic.

[gandolf989]

Based on the code that you posted above, "The error no data found is caused by the select into not returning any rows." still applies.
If there is other code that you did not post, then I am not commenting on that code, since I did not see it. With a select into you need
to handle the condition where no data gets returned.

Did you copy this code from another site online? If so you may want to go back to the person that you copied it from. Since this looks
like a Peoplesoft database, you might also want to check with Oracle to see if they can help you with the logic.