Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01002: Fetch Out of Sequence
ORA-01002: Fetch Out of Sequence [message #39592] Wed, 24 July 2002 22:44 Go to next message
Keshav Tripathy
Messages: 30
Registered: November 2001
Member
Dear All

I have the following scenerio.
There is a Table named as XFR_LOANS which has 77000 Records. I have written a Stored Procedure to READ
these records from this table and write into another
table named as CL_LOAN. During this Process I am getting an ORA-01002 Error : Fetch Out of Sequence.
The Stored Procedure Code is given below.
Can you tell us where we should look for the Problem.
If you need the table structure write me I can send it back to you.

Thanks and Regards.

Keshav

procedure loan_upload (v_file_dir varchar2) as
--****************************************************************************************
-- Stored Procedure : loan_upload
-- System : Certilink
-- Description : This stored procedure will load data from the temporary table in to
-- the CL_LOAN table.
-- Maintenance Log :
-- Programmer Date Description
-- record processing variables
v_orig_eff_dte varchar2(8);
v_ren_ptd_dte varchar2(8);
v_nxt_prem_due varchar2(8);
v_eff_inact_dte varchar2(8);
v_can_refund_dte varchar2(8);
v_mo_amor_insd_date varchar2(8);
v_suspense_balance varchar2(12);
v_plan_covrg varchar2(6);
v_ren_prem_amt varchar2(8);
v_prem_amt varchar2(8);
v_can_refund_amt varchar2(8);
v_loan_amt varchar2(9);
v_orig_insd_amt varchar2(9);
v_curr_insd_amt varchar2(9);
v_appr_val varchar2(9);
v_sls_pr varchar2(9);
v_mo_amor_nxt_insd varchar2(9);
v_mo_amor_bill_insd varchar2(9);
-- cursor definition
cursor tmp_tab_cur is
select DB_IND,
MAST_POL,
CERT,
LEND_LOAN,
BOR_LAST,
BOR_FIRST,
BOR_MID_IN,
BOR_SUFFIX,
NBR_BOR_SSN,
CBOR_LAST,
CBOR_FIRST,
CBOR_MID_IN,
CBOR_SUFFIX,
NBR_CBOR_SSN,
PROP_ADDR_1,
PROP_ADDR_2,
PROP_CTY_ST,
PROP_ZIP,
PROP_ST,
ORIG_EFF_DTE,
LOAN_AMT,
REN_PTD_DTE,
ORIG_INSD_AMT,
CURR_INSD_AMT,
NXT_PREM_DUE,
REN_PREM_AMT,
PLAN_COVRG,
REASON_INACT,
EFF_INACT_DTE,
PREM_AMT,
POOL_POLICY_NUM,
REFUND_IND,
SUSPENSE_BALANCE,
LEVEL_OR_AMORT,
RATE_TYPE,
OWNER_ORG_KEY,
APPR_VAL,
IMP_RATE_KEY,
REASON_INACT_DESC_CD,
CAN_REFUND_DTE,
CAN_REFUND_AMT,
OWNER_OCC,
XREF_CERT,
SLS_PR,
LOAN_PURPOSE,
MO_AMOR_NXT_INSD,
MO_AMOR_BILL_INSD,
MO_AMOR_INSD_DATE,
RENEW_IND,
rowid
from xfr_Loans; --where cert='09601085297';
tmp_tab_rec tmp_tab_cur%rowtype;
-- Log file variables
log_file_handle UTL_FILE.FILE_TYPE;
log_file_name varchar2(50) := 'loan_upload_log.log';
log_file_buffer varchar2(250) := ' ';
-- Misc variables
stored_procedure_name varchar2(30) := 'upload_oracle.loan_upload';
write_mode varchar2(1) := 'w';
current_date_time varchar2(25) := ' ';
input_count pls_integer := 0;
sql_code number := 0;
sql_msg varchar2(150) := ' ';
loan_add_cnt pls_integer := 0;
loan_del_cnt pls_integer := 0;
loan_upd_cnt pls_integer := 0;
loan_rec_cnt pls_integer := 0;
loan_mis_cnt pls_integer := 0;
loan_fail_cnt pls_integer := 0;
--var1 char(5);
begin
-- open files
log_file_handle := UTL_FILE.FOPEN(v_file_dir, log_file_name, write_mode);
-- write start time to log file
select to_char(sysdate, 'MM/DD/YYYY HH:MI:SS')
into current_date_time
from dual;
log_file_buffer := 'Procedure ' || stored_procedure_name || ' started at ' || current_date_time;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
-- open cursor and process the records
open tmp_tab_cur;

loop
begin

fetch tmp_tab_cur into tmp_tab_rec;
-- dbms_output.put_line(tmp_tab_cur%rowcount);

-- if tmp_tab_cur%isopen then
-- var1 := 'true';
-- else
-- var1 := 'false';
-- end if;
--
-- dbms_output.put_line(var1);
exit when tmp_tab_cur%notfound;
--dbms_output.put_line(var1);
-- convert date values to include century
if to_number(substr(tmp_tab_rec.orig_eff_dte,5,2)) >= 50
then
v_orig_eff_dte := substr(tmp_tab_rec.orig_eff_dte,1,4) || '19' || substr(tmp_tab_rec.orig_eff_dte,5,2);
else
v_orig_eff_dte := substr(tmp_tab_rec.orig_eff_dte,1,4) || '20' || substr(tmp_tab_rec.orig_eff_dte,5,2);
end if;

if to_number(substr(tmp_tab_rec.ren_ptd_dte,5,2)) >= 50
then
v_ren_ptd_dte := substr(tmp_tab_rec.ren_ptd_dte,1,4) || '19' || substr(tmp_tab_rec.ren_ptd_dte,5,2);
else
v_ren_ptd_dte := substr(tmp_tab_rec.ren_ptd_dte,1,4) || '20' || substr(tmp_tab_rec.ren_ptd_dte,5,2);
end if;

if to_number(substr(tmp_tab_rec.nxt_prem_due,5,2)) >= 50
then
v_nxt_prem_due := substr(tmp_tab_rec.nxt_prem_due,1,4) || '19' || substr(tmp_tab_rec.nxt_prem_due,5,2);
else
v_nxt_prem_due := substr(tmp_tab_rec.nxt_prem_due,1,4) || '20' || substr(tmp_tab_rec.nxt_prem_due,5,2);
end if;

if to_number(substr(tmp_tab_rec.eff_inact_dte,5,2)) >= 50
then
v_eff_inact_dte := substr(tmp_tab_rec.eff_inact_dte,1,4) || '19' || substr(tmp_tab_rec.eff_inact_dte,5,2);
else
v_eff_inact_dte := substr(tmp_tab_rec.eff_inact_dte,1,4) || '20' || substr(tmp_tab_rec.eff_inact_dte,5,2);
end if;

if to_number(substr(tmp_tab_rec.can_refund_dte,5,2)) >= 50
then
v_can_refund_dte := substr(tmp_tab_rec.can_refund_dte,1,4) || '19' || substr(tmp_tab_rec.can_refund_dte,5,2);
else
v_can_refund_dte := substr(tmp_tab_rec.can_refund_dte,1,4) || '20' || substr(tmp_tab_rec.can_refund_dte,5,2);
end if;

if to_number(substr(tmp_tab_rec.mo_amor_insd_date,5,2)) >= 50
then
v_mo_amor_insd_date := substr(tmp_tab_rec.mo_amor_insd_date,1,4) || '19' || substr(tmp_tab_rec.mo_amor_insd_date,5,2);
else
v_mo_amor_insd_date := substr(tmp_tab_rec.mo_amor_insd_date,1,4) || '20' || substr(tmp_tab_rec.mo_amor_insd_date,5,2);
end if;
-- convert numeric values to required format
v_suspense_balance := substr(tmp_tab_rec.suspense_balance,1,9) || '.' || substr(tmp_tab_rec.suspense_balance,10,2);
v_plan_covrg := substr(tmp_tab_rec.plan_covrg,1,3) || '.' || substr(tmp_tab_rec.plan_covrg,4,2);
v_ren_prem_amt := substr(tmp_tab_rec.ren_prem_amt,1,5) || '.' || substr(tmp_tab_rec.ren_prem_amt,6,2);
v_prem_amt := substr(tmp_tab_rec.prem_amt,1,5) || '.' || substr(tmp_tab_rec.prem_amt,6,2);
v_can_refund_amt := substr(tmp_tab_rec.can_refund_amt,1,5) || '.' || substr(tmp_tab_rec.can_refund_amt,6,2);
v_loan_amt := tmp_tab_rec.loan_amt;
v_orig_insd_amt := tmp_tab_rec.orig_insd_amt;
v_curr_insd_amt := tmp_tab_rec.curr_insd_amt;
v_appr_val := tmp_tab_rec.appr_val;
v_sls_pr := tmp_tab_rec.sls_pr;
v_mo_amor_nxt_insd := tmp_tab_rec.mo_amor_nxt_insd;
v_mo_amor_bill_insd := tmp_tab_rec.mo_amor_bill_insd;

input_count := input_count + 1;

if tmp_tab_rec.db_ind = 'D'
then
delete from cl_loan
where certNumber = tmp_tab_rec.cert
and masterPolicyNumber = substr(tmp_tab_rec.mast_pol,2,10);

if SQL%FOUND
then

delete from xfr_loans
where rowid = tmp_tab_rec.rowid;

loan_del_cnt := loan_del_cnt + 1;
else
log_file_buffer := 'Matching record not found for Certnumber : ' || tmp_tab_rec.cert || ' Masterpolicynumber : ' || substr(tmp_tab_rec.mast_pol,2,10);
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
loan_mis_cnt := loan_mis_cnt + 1;
end if;
else
select count(1)
into loan_rec_cnt
from cl_loan
where certnumber = tmp_tab_rec.cert
and masterpolicynumber = substr(tmp_tab_rec.mast_pol,2,10);

if loan_rec_cnt > 0
then
update cl_loan
set ORGNUMBER = tmp_tab_rec.owner_org_key,
LENDERLOANNUMBER = tmp_tab_rec.lend_loan,
ORIGINALEFFECTIVEDATE = decode(substr(replace(v_orig_eff_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_orig_eff_dte,'mmddyyyy')),
LOANAMOUNT = to_number(v_loan_amt),
RENEWALPRINTEDDATE = decode(substr(replace(v_ren_ptd_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_ren_ptd_dte,'mmddyyyy')),
ORIGINALINSUREDAMOUNT = to_number(v_orig_insd_amt),
CURRENTINSUREDAMOUNT = to_number(v_curr_insd_amt),
NEXTPREMIUMDUEDATE = decode(substr(replace(v_nxt_prem_due,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_nxt_prem_due,'mmddyyyy')),
RENEWALPREMIUMAMOUNT = to_number(v_ren_prem_amt),
PLANCOVERAGEPERCENT = round(to_number(v_plan_covrg),0),
INACTIVEREASON = tmp_tab_rec.reason_inact,
EFFECTIVEINACTIVEDATE = decode(substr(replace(v_eff_inact_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_eff_inact_dte,'mmddyyyy')),
PREMIUMAMOUNT = to_number(v_prem_amt),
POOLPOLICYNUMBER = substr(tmp_tab_rec.pool_policy_num,2,10),
REFUNDINDICATOR = decode(tmp_tab_rec.refund_ind,null,' ',tmp_tab_rec.refund_ind),
SUSPENSEAMOUNT = to_number(v_suspense_balance),
LEVELORAMORTIZED = tmp_tab_rec.level_or_amort,
RATETYPE = tmp_tab_rec.rate_type,
BORROWERLASTNAME = tmp_tab_rec.bor_last,
BORROWERFIRSTNAME = decode(tmp_tab_rec.bor_first,'',' ',tmp_tab_rec.bor_first),
BORROWERMIDDLENAME = tmp_tab_rec.bor_mid_in,
BORROWERSUFFIX = tmp_tab_rec.bor_suffix,
BORROWERSOCIALSECURITYNUMBER = tmp_tab_rec.nbr_bor_ssn,
COBORROWERLASTNAME = tmp_tab_rec.cbor_last,
COBORROWERFIRSTNAME = tmp_tab_rec.cbor_first,
COBORROWERMIDDLENAME = tmp_tab_rec.cbor_mid_in,
COBORROWERSUFFIX = tmp_tab_rec.cbor_suffix,
COBORROWERSOCIALSECURITYNUMBER = tmp_tab_rec.nbr_cbor_ssn,
APPRAISALVALUE = to_number(v_appr_val),
RATENUMBER = tmp_tab_rec.imp_rate_key,
INACTIVEREASONDESCRIPTION = tmp_tab_rec.reason_inact_desc_cd,
PROPERTYUSE = tmp_tab_rec.owner_occ,
POOLXREFNUMBER = tmp_tab_rec.xref_cert,
PURPOSEOFLOAN = tmp_tab_rec.loan_purpose,
PURCHASEPRICE = to_number(v_sls_pr),
REFUNDDATE = decode(substr(replace(v_can_refund_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_can_refund_dte,'mmddyyyy')),
REFUNDAMOUNT = to_number(v_can_refund_amt),
MONTHLYAMORTNEXTINSURED = to_number(v_mo_amor_nxt_insd),
MONTHLYAMORTINSURED = to_number(v_mo_amor_bill_insd),
MONTHLYAMORTINSUREDDATE = decode(substr(replace(v_mo_amor_insd_date,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_mo_amor_insd_date,'mmddyyyy')),
CREATIONDATE = sysdate,
MAINTUSERID = 'SYSTEM',
MAINTDATE = sysdate,
MAINTTEXT = null,
RENEWALINDICATOR = tmp_tab_rec.renew_ind
where certNumber = tmp_tab_rec.cert
and masterPolicyNumber = substr(tmp_tab_rec.mast_pol,2,10);

if sql%found
then

delete from xfr_loans
where rowid = tmp_tab_rec.rowid;

end if;

loan_upd_cnt := loan_upd_cnt + 1;
else
insert into cl_loan (
CERTNUMBER,
MASTERPOLICYNUMBER,
ORGNUMBER,
LENDERLOANNUMBER,
ORIGINALEFFECTIVEDATE,
LOANAMOUNT,
RENEWALPRINTEDDATE,
ORIGINALINSUREDAMOUNT,
CURRENTINSUREDAMOUNT,
NEXTPREMIUMDUEDATE,
RENEWALPREMIUMAMOUNT,
PLANCOVERAGEPERCENT,
INACTIVEREASON,
EFFECTIVEINACTIVEDATE,
PREMIUMAMOUNT,
POOLPOLICYNUMBER,
REFUNDINDICATOR,
SUSPENSEAMOUNT,
LEVELORAMORTIZED,
RATETYPE,
BORROWERLASTNAME,
BORROWERFIRSTNAME,
BORROWERMIDDLENAME,
BORROWERSUFFIX,
BORROWERSOCIALSECURITYNUMBER,
COBORROWERLASTNAME,
COBORROWERFIRSTNAME,
COBORROWERMIDDLENAME,
COBORROWERSUFFIX,
COBORROWERSOCIALSECURITYNUMBER,
APPRAISALVALUE,
RATENUMBER,
INACTIVEREASONDESCRIPTION,
PROPERTYUSE,
POOLXREFNUMBER,
PURPOSEOFLOAN,
PURCHASEPRICE,
REFUNDDATE,
REFUNDAMOUNT,
MONTHLYAMORTNEXTINSURED,
MONTHLYAMORTINSURED,
MONTHLYAMORTINSUREDDATE,
CREATIONDATE,
MAINTUSERID,
MAINTDATE,
MAINTTEXT,
RENEWALINDICATOR )
values (
tmp_tab_rec.cert,
substr(tmp_tab_rec.mast_pol,2,10),
tmp_tab_rec.owner_org_key,
tmp_tab_rec.lend_loan,
decode(substr(replace(v_orig_eff_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_orig_eff_dte,'mmddyyyy')),
to_number(v_loan_amt),
decode(substr(replace(v_ren_ptd_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_ren_ptd_dte,'mmddyyyy')),
to_number(v_orig_insd_amt),
to_number(v_curr_insd_amt),
decode(substr(replace(v_nxt_prem_due,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_nxt_prem_due,'mmddyyyy')),
to_number(v_ren_prem_amt),
round(to_number(v_plan_covrg),0),
tmp_tab_rec.reason_inact,
decode(substr(replace(v_eff_inact_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_eff_inact_dte,'mmddyyyy')),
to_number(v_prem_amt),
substr(tmp_tab_rec.pool_policy_num,2,10),
decode(tmp_tab_rec.refund_ind,null,' ',tmp_tab_rec.refund_ind),
to_number(v_suspense_balance),
tmp_tab_rec.level_or_amort,
tmp_tab_rec.rate_type,
tmp_tab_rec.bor_last,
decode(tmp_tab_rec.bor_first,'',' ',tmp_tab_rec.bor_first),
tmp_tab_rec.bor_mid_in,
tmp_tab_rec.bor_suffix,
tmp_tab_rec.nbr_bor_ssn,
tmp_tab_rec.cbor_last,
tmp_tab_rec.cbor_first,
tmp_tab_rec.cbor_mid_in,
tmp_tab_rec.cbor_suffix,
tmp_tab_rec.nbr_cbor_ssn,
to_number(v_appr_val),
tmp_tab_rec.imp_rate_key,
tmp_tab_rec.reason_inact_desc_cd,
tmp_tab_rec.owner_occ,
tmp_tab_rec.xref_cert,
tmp_tab_rec.loan_purpose,
to_number(v_sls_pr),
decode(substr(replace(v_can_refund_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_can_refund_dte,'mmddyyyy')),
to_number(v_can_refund_amt),
to_number(v_mo_amor_nxt_insd),
to_number(v_mo_amor_bill_insd),
decode(substr(replace(v_mo_amor_insd_date,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_mo_amor_insd_date,'mmddyyyy')),
sysdate,
'SYSTEM',
sysdate,
null,
tmp_tab_rec.renew_ind );

delete from xfr_loans
where rowid = tmp_tab_rec.rowid;

loan_add_cnt := loan_add_cnt + 1;
end if;
end if;

commit;

exception
when others then
loan_fail_cnt := loan_fail_cnt + 1;
sql_code := SQLCODE;
sql_msg := SQLERRM;
log_file_buffer := 'Error Code : ' || sql_code || '. Error Message : ' || sql_msg || ' Certnumber : ' || tmp_tab_rec.cert || ' Masterpolicynumber : ' || substr(tmp_tab_rec.mast_pol,2,10);
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
end;
end loop;

close tmp_tab_cur;
log_file_buffer := 'Records read : ' || input_count;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records inserted : ' || loan_add_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records deleted : ' || loan_del_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records changed : ' || loan_upd_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records not found : ' || loan_mis_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records failed : ' || loan_fail_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
UTL_FILE.FCLOSE(log_file_handle);
--Exception block
exception
when UTL_FILE.INVALID_OPERATION then
sql_code := sqlcode;
sql_msg := sqlerrm;
dbms_output.put_line('Invalid operation performed.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when UTL_FILE.INVALID_FILEHANDLE then
sql_code := sqlcode;
sql_msg := sqlerrm;
dbms_output.put_line('Invalid file handle.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when UTL_FILE.INVALID_PATH then
sql_code := sqlcode;
sql_msg := sqlerrm;
dbms_output.put_line('Invalid file path.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when UTL_FILE.INVALID_MODE then
sql_code := sqlcode;
sql_msg := sqlerrm;
dbms_output.put_line('Invalid file open mode.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when UTL_FILE.WRITE_ERROR then
sql_code := sqlcode;
sql_msg := sqlerrm;
UTL_FILE.FCLOSE(log_file_handle);
dbms_output.put_line('File write error.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when others then
sql_code := sqlcode;
sql_msg := sqlerrm;
UTL_FILE.FCLOSE(log_file_handle);
dbms_output.put_line('Unknown error.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);

end loan_upload;
Re: ORA-01002: Fetch Out of Sequence [message #39599 is a reply to message #39592] Thu, 25 July 2002 09:09 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Usually caused by a commit inside the loop.

 >oerr ora 1002
01002, 00000, "fetch out of sequence"
// *Cause: This error means that a fetch has been attempted from a cursor
//         which is no longer valid.  Note that a PL/SQL cursor loop
//         implicitly does fetches, and thus may also cause this error.
//         There are a number of possible causes for this error, including:
//         1) Fetching from a cursor after the last row has been retrieved
//            and the ORA-1403 error returned.
//         2) If the cursor has been opened with the FOR UPDATE clause,
//            fetching after a COMMIT has been issued will return the error.
//         3) Rebinding any placeholders in the SQL statement, then issuing
//            a fetch before reexecuting the statement.
// *Action: 1) Do not issue a fetch statement after the last row has been
//             retrieved - there are no more rows to fetch.
//          2) Do not issue a COMMIT inside a fetch loop for a cursor
//             that has been opened FOR UPDATE.
//          3) Reexecute the statement after rebinding, then attempt to
//             fetch again.
Re: ORA-01002: Fetch Out of Sequence [message #39635 is a reply to message #39599] Tue, 30 July 2002 06:10 Go to previous messageGo to next message
Keshav Tripathy
Messages: 30
Registered: November 2001
Member
Dear Andrew

Happy to see your reply. But we are not using Cursor for loop. Below is the code. Yes Commit is inside the loop but why that should create problem. The causes which you have given is not falling in our case. Please give me your feedback on the following piece of code.
Thanks and regards.
Keshav
procedure loan_upload (v_file_dir varchar2) as
--****************************************************************************************
-- Stored Procedure : loan_upload
-- System : Certilink
-- Description : This stored procedure will load data from the temporary table in to
-- the CL_LOAN table.
-- Maintenance Log :
-- Programmer Date Description
-- ---------- ----------- -----------------
-- Satyam 04-Mar-2002 Created procedure
--****************************************************************************************
-- record processing variables
v_orig_eff_dte varchar2(8);
v_ren_ptd_dte varchar2(8);
v_nxt_prem_due varchar2(8);
v_eff_inact_dte varchar2(8);
v_can_refund_dte varchar2(8);
v_mo_amor_insd_date varchar2(8);
v_suspense_balance varchar2(12);
v_plan_covrg varchar2(6);
v_ren_prem_amt varchar2(8);
v_prem_amt varchar2(8);
v_can_refund_amt varchar2(8);
v_loan_amt varchar2(9);
v_orig_insd_amt varchar2(9);
v_curr_insd_amt varchar2(9);
v_appr_val varchar2(9);
v_sls_pr varchar2(9);
v_mo_amor_nxt_insd varchar2(9);
v_mo_amor_bill_insd varchar2(9);
-- cursor definition
cursor tmp_tab_cur is
select DB_IND,
MAST_POL,
CERT,
LEND_LOAN,
BOR_LAST,
BOR_FIRST,
BOR_MID_IN,
BOR_SUFFIX,
NBR_BOR_SSN,
CBOR_LAST,
CBOR_FIRST,
CBOR_MID_IN,
CBOR_SUFFIX,
NBR_CBOR_SSN,
PROP_ADDR_1,
PROP_ADDR_2,
PROP_CTY_ST,
PROP_ZIP,
PROP_ST,
ORIG_EFF_DTE,
LOAN_AMT,
REN_PTD_DTE,
ORIG_INSD_AMT,
CURR_INSD_AMT,
NXT_PREM_DUE,
REN_PREM_AMT,
PLAN_COVRG,
REASON_INACT,
EFF_INACT_DTE,
PREM_AMT,
POOL_POLICY_NUM,
REFUND_IND,
SUSPENSE_BALANCE,
LEVEL_OR_AMORT,
RATE_TYPE,
OWNER_ORG_KEY,
APPR_VAL,
IMP_RATE_KEY,
REASON_INACT_DESC_CD,
CAN_REFUND_DTE,
CAN_REFUND_AMT,
OWNER_OCC,
XREF_CERT,
SLS_PR,
LOAN_PURPOSE,
MO_AMOR_NXT_INSD,
MO_AMOR_BILL_INSD,
MO_AMOR_INSD_DATE,
RENEW_IND,
rowid
from xfr_Loans; --where cert='09601085297';
tmp_tab_rec tmp_tab_cur%rowtype;
-- Log file variables
log_file_handle UTL_FILE.FILE_TYPE;
log_file_name varchar2(50) := 'loan_upload_log.log';
log_file_buffer varchar2(250) := ' ';
-- Misc variables
stored_procedure_name varchar2(30) := 'upload_oracle.loan_upload';
write_mode varchar2(1) := 'w';
current_date_time varchar2(25) := ' ';
input_count pls_integer := 0;
sql_code number := 0;
sql_msg varchar2(150) := ' ';
loan_add_cnt pls_integer := 0;
loan_del_cnt pls_integer := 0;
loan_upd_cnt pls_integer := 0;
loan_rec_cnt pls_integer := 0;
loan_mis_cnt pls_integer := 0;
loan_fail_cnt pls_integer := 0;
--var1 char(5);
begin
-- open files
log_file_handle := UTL_FILE.FOPEN(v_file_dir, log_file_name, write_mode);
-- write start time to log file
select to_char(sysdate, 'MM/DD/YYYY HH:MI:SS')
into current_date_time
from dual;
log_file_buffer := 'Procedure ' || stored_procedure_name || ' started at ' || current_date_time;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
-- open cursor and process the records
open tmp_tab_cur;

loop
begin

fetch tmp_tab_cur into tmp_tab_rec;
-- dbms_output.put_line(tmp_tab_cur%rowcount);

-- if tmp_tab_cur%isopen then
-- var1 := 'true';
-- else
-- var1 := 'false';
-- end if;
--
-- dbms_output.put_line(var1);
exit when tmp_tab_cur%notfound;
--dbms_output.put_line(var1);
-- convert date values to include century
if to_number(substr(tmp_tab_rec.orig_eff_dte,5,2)) >= 50
then
v_orig_eff_dte := substr(tmp_tab_rec.orig_eff_dte,1,4) || '19' || substr(tmp_tab_rec.orig_eff_dte,5,2);
else
v_orig_eff_dte := substr(tmp_tab_rec.orig_eff_dte,1,4) || '20' || substr(tmp_tab_rec.orig_eff_dte,5,2);
end if;

if to_number(substr(tmp_tab_rec.ren_ptd_dte,5,2)) >= 50
then
v_ren_ptd_dte := substr(tmp_tab_rec.ren_ptd_dte,1,4) || '19' || substr(tmp_tab_rec.ren_ptd_dte,5,2);
else
v_ren_ptd_dte := substr(tmp_tab_rec.ren_ptd_dte,1,4) || '20' || substr(tmp_tab_rec.ren_ptd_dte,5,2);
end if;

if to_number(substr(tmp_tab_rec.nxt_prem_due,5,2)) >= 50
then
v_nxt_prem_due := substr(tmp_tab_rec.nxt_prem_due,1,4) || '19' || substr(tmp_tab_rec.nxt_prem_due,5,2);
else
v_nxt_prem_due := substr(tmp_tab_rec.nxt_prem_due,1,4) || '20' || substr(tmp_tab_rec.nxt_prem_due,5,2);
end if;

if to_number(substr(tmp_tab_rec.eff_inact_dte,5,2)) >= 50
then
v_eff_inact_dte := substr(tmp_tab_rec.eff_inact_dte,1,4) || '19' || substr(tmp_tab_rec.eff_inact_dte,5,2);
else
v_eff_inact_dte := substr(tmp_tab_rec.eff_inact_dte,1,4) || '20' || substr(tmp_tab_rec.eff_inact_dte,5,2);
end if;

if to_number(substr(tmp_tab_rec.can_refund_dte,5,2)) >= 50
then
v_can_refund_dte := substr(tmp_tab_rec.can_refund_dte,1,4) || '19' || substr(tmp_tab_rec.can_refund_dte,5,2);
else
v_can_refund_dte := substr(tmp_tab_rec.can_refund_dte,1,4) || '20' || substr(tmp_tab_rec.can_refund_dte,5,2);
end if;

if to_number(substr(tmp_tab_rec.mo_amor_insd_date,5,2)) >= 50
then
v_mo_amor_insd_date := substr(tmp_tab_rec.mo_amor_insd_date,1,4) || '19' || substr(tmp_tab_rec.mo_amor_insd_date,5,2);
else
v_mo_amor_insd_date := substr(tmp_tab_rec.mo_amor_insd_date,1,4) || '20' || substr(tmp_tab_rec.mo_amor_insd_date,5,2);
end if;
-- convert numeric values to required format
v_suspense_balance := substr(tmp_tab_rec.suspense_balance,1,9) || '.' || substr(tmp_tab_rec.suspense_balance,10,2);
v_plan_covrg := substr(tmp_tab_rec.plan_covrg,1,3) || '.' || substr(tmp_tab_rec.plan_covrg,4,2);
v_ren_prem_amt := substr(tmp_tab_rec.ren_prem_amt,1,5) || '.' || substr(tmp_tab_rec.ren_prem_amt,6,2);
v_prem_amt := substr(tmp_tab_rec.prem_amt,1,5) || '.' || substr(tmp_tab_rec.prem_amt,6,2);
v_can_refund_amt := substr(tmp_tab_rec.can_refund_amt,1,5) || '.' || substr(tmp_tab_rec.can_refund_amt,6,2);
v_loan_amt := tmp_tab_rec.loan_amt;
v_orig_insd_amt := tmp_tab_rec.orig_insd_amt;
v_curr_insd_amt := tmp_tab_rec.curr_insd_amt;
v_appr_val := tmp_tab_rec.appr_val;
v_sls_pr := tmp_tab_rec.sls_pr;
v_mo_amor_nxt_insd := tmp_tab_rec.mo_amor_nxt_insd;
v_mo_amor_bill_insd := tmp_tab_rec.mo_amor_bill_insd;

input_count := input_count + 1;

if tmp_tab_rec.db_ind = 'D'
then
delete from cl_loan
where certNumber = tmp_tab_rec.cert
and masterPolicyNumber = substr(tmp_tab_rec.mast_pol,2,10);

if SQL%FOUND
then

delete from xfr_loans
where rowid = tmp_tab_rec.rowid;

loan_del_cnt := loan_del_cnt + 1;
else
log_file_buffer := 'Matching record not found for Certnumber : ' || tmp_tab_rec.cert || ' Masterpolicynumber : ' || substr(tmp_tab_rec.mast_pol,2,10);
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
loan_mis_cnt := loan_mis_cnt + 1;
end if;
else
select count(1)
into loan_rec_cnt
from cl_loan
where certnumber = tmp_tab_rec.cert
and masterpolicynumber = substr(tmp_tab_rec.mast_pol,2,10);

if loan_rec_cnt > 0
then
update cl_loan
set ORGNUMBER = tmp_tab_rec.owner_org_key,
LENDERLOANNUMBER = tmp_tab_rec.lend_loan,
ORIGINALEFFECTIVEDATE = decode(substr(replace(v_orig_eff_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_orig_eff_dte,'mmddyyyy')),
LOANAMOUNT = to_number(v_loan_amt),
RENEWALPRINTEDDATE = decode(substr(replace(v_ren_ptd_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_ren_ptd_dte,'mmddyyyy')),
ORIGINALINSUREDAMOUNT = to_number(v_orig_insd_amt),
CURRENTINSUREDAMOUNT = to_number(v_curr_insd_amt),
NEXTPREMIUMDUEDATE = decode(substr(replace(v_nxt_prem_due,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_nxt_prem_due,'mmddyyyy')),
RENEWALPREMIUMAMOUNT = to_number(v_ren_prem_amt),
PLANCOVERAGEPERCENT = round(to_number(v_plan_covrg),0),
INACTIVEREASON = tmp_tab_rec.reason_inact,
EFFECTIVEINACTIVEDATE = decode(substr(replace(v_eff_inact_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_eff_inact_dte,'mmddyyyy')),
PREMIUMAMOUNT = to_number(v_prem_amt),
POOLPOLICYNUMBER = substr(tmp_tab_rec.pool_policy_num,2,10),
REFUNDINDICATOR = decode(tmp_tab_rec.refund_ind,null,' ',tmp_tab_rec.refund_ind),
SUSPENSEAMOUNT = to_number(v_suspense_balance),
LEVELORAMORTIZED = tmp_tab_rec.level_or_amort,
RATETYPE = tmp_tab_rec.rate_type,
BORROWERLASTNAME = tmp_tab_rec.bor_last,
BORROWERFIRSTNAME = decode(tmp_tab_rec.bor_first,'',' ',tmp_tab_rec.bor_first),
BORROWERMIDDLENAME = tmp_tab_rec.bor_mid_in,
BORROWERSUFFIX = tmp_tab_rec.bor_suffix,
BORROWERSOCIALSECURITYNUMBER = tmp_tab_rec.nbr_bor_ssn,
COBORROWERLASTNAME = tmp_tab_rec.cbor_last,
COBORROWERFIRSTNAME = tmp_tab_rec.cbor_first,
COBORROWERMIDDLENAME = tmp_tab_rec.cbor_mid_in,
COBORROWERSUFFIX = tmp_tab_rec.cbor_suffix,
COBORROWERSOCIALSECURITYNUMBER = tmp_tab_rec.nbr_cbor_ssn,
APPRAISALVALUE = to_number(v_appr_val),
RATENUMBER = tmp_tab_rec.imp_rate_key,
INACTIVEREASONDESCRIPTION = tmp_tab_rec.reason_inact_desc_cd,
PROPERTYUSE = tmp_tab_rec.owner_occ,
POOLXREFNUMBER = tmp_tab_rec.xref_cert,
PURPOSEOFLOAN = tmp_tab_rec.loan_purpose,
PURCHASEPRICE = to_number(v_sls_pr),
REFUNDDATE = decode(substr(replace(v_can_refund_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_can_refund_dte,'mmddyyyy')),
REFUNDAMOUNT = to_number(v_can_refund_amt),
MONTHLYAMORTNEXTINSURED = to_number(v_mo_amor_nxt_insd),
MONTHLYAMORTINSURED = to_number(v_mo_amor_bill_insd),
MONTHLYAMORTINSUREDDATE = decode(substr(replace(v_mo_amor_insd_date,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_mo_amor_insd_date,'mmddyyyy')),
CREATIONDATE = sysdate,
MAINTUSERID = 'SYSTEM',
MAINTDATE = sysdate,
MAINTTEXT = null,
RENEWALINDICATOR = tmp_tab_rec.renew_ind
where certNumber = tmp_tab_rec.cert
and masterPolicyNumber = substr(tmp_tab_rec.mast_pol,2,10);

if sql%found
then

delete from xfr_loans
where rowid = tmp_tab_rec.rowid;

end if;

loan_upd_cnt := loan_upd_cnt + 1;
else
insert into cl_loan (
CERTNUMBER,
MASTERPOLICYNUMBER,
ORGNUMBER,
LENDERLOANNUMBER,
ORIGINALEFFECTIVEDATE,
LOANAMOUNT,
RENEWALPRINTEDDATE,
ORIGINALINSUREDAMOUNT,
CURRENTINSUREDAMOUNT,
NEXTPREMIUMDUEDATE,
RENEWALPREMIUMAMOUNT,
PLANCOVERAGEPERCENT,
INACTIVEREASON,
EFFECTIVEINACTIVEDATE,
PREMIUMAMOUNT,
POOLPOLICYNUMBER,
REFUNDINDICATOR,
SUSPENSEAMOUNT,
LEVELORAMORTIZED,
RATETYPE,
BORROWERLASTNAME,
BORROWERFIRSTNAME,
BORROWERMIDDLENAME,
BORROWERSUFFIX,
BORROWERSOCIALSECURITYNUMBER,
COBORROWERLASTNAME,
COBORROWERFIRSTNAME,
COBORROWERMIDDLENAME,
COBORROWERSUFFIX,
COBORROWERSOCIALSECURITYNUMBER,
APPRAISALVALUE,
RATENUMBER,
INACTIVEREASONDESCRIPTION,
PROPERTYUSE,
POOLXREFNUMBER,
PURPOSEOFLOAN,
PURCHASEPRICE,
REFUNDDATE,
REFUNDAMOUNT,
MONTHLYAMORTNEXTINSURED,
MONTHLYAMORTINSURED,
MONTHLYAMORTINSUREDDATE,
CREATIONDATE,
MAINTUSERID,
MAINTDATE,
MAINTTEXT,
RENEWALINDICATOR )
values (
tmp_tab_rec.cert,
substr(tmp_tab_rec.mast_pol,2,10),
tmp_tab_rec.owner_org_key,
tmp_tab_rec.lend_loan,
decode(substr(replace(v_orig_eff_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_orig_eff_dte,'mmddyyyy')),
to_number(v_loan_amt),
decode(substr(replace(v_ren_ptd_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_ren_ptd_dte,'mmddyyyy')),
to_number(v_orig_insd_amt),
to_number(v_curr_insd_amt),
decode(substr(replace(v_nxt_prem_due,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_nxt_prem_due,'mmddyyyy')),
to_number(v_ren_prem_amt),
round(to_number(v_plan_covrg),0),
tmp_tab_rec.reason_inact,
decode(substr(replace(v_eff_inact_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_eff_inact_dte,'mmddyyyy')),
to_number(v_prem_amt),
substr(tmp_tab_rec.pool_policy_num,2,10),
decode(tmp_tab_rec.refund_ind,null,' ',tmp_tab_rec.refund_ind),
to_number(v_suspense_balance),
tmp_tab_rec.level_or_amort,
tmp_tab_rec.rate_type,
tmp_tab_rec.bor_last,
decode(tmp_tab_rec.bor_first,'',' ',tmp_tab_rec.bor_first),
tmp_tab_rec.bor_mid_in,
tmp_tab_rec.bor_suffix,
tmp_tab_rec.nbr_bor_ssn,
tmp_tab_rec.cbor_last,
tmp_tab_rec.cbor_first,
tmp_tab_rec.cbor_mid_in,
tmp_tab_rec.cbor_suffix,
tmp_tab_rec.nbr_cbor_ssn,
to_number(v_appr_val),
tmp_tab_rec.imp_rate_key,
tmp_tab_rec.reason_inact_desc_cd,
tmp_tab_rec.owner_occ,
tmp_tab_rec.xref_cert,
tmp_tab_rec.loan_purpose,
to_number(v_sls_pr),
decode(substr(replace(v_can_refund_dte,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_can_refund_dte,'mmddyyyy')),
to_number(v_can_refund_amt),
to_number(v_mo_amor_nxt_insd),
to_number(v_mo_amor_bill_insd),
decode(substr(replace(v_mo_amor_insd_date,' ',''),1,2),'00',to_date('01-jan-1900','dd-mon-yyyy'),to_date(v_mo_amor_insd_date,'mmddyyyy')),
sysdate,
'SYSTEM',
sysdate,
null,
tmp_tab_rec.renew_ind );

delete from xfr_loans
where rowid = tmp_tab_rec.rowid;

loan_add_cnt := loan_add_cnt + 1;
end if;
end if;

commit;

exception
when others then
loan_fail_cnt := loan_fail_cnt + 1;
sql_code := SQLCODE;
sql_msg := SQLERRM;
log_file_buffer := 'Error Code : ' || sql_code || '. Error Message : ' || sql_msg || ' Certnumber : ' || tmp_tab_rec.cert || ' Masterpolicynumber : ' || substr(tmp_tab_rec.mast_pol,2,10);
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
end;
end loop;

close tmp_tab_cur;
log_file_buffer := 'Records read : ' || input_count;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records inserted : ' || loan_add_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records deleted : ' || loan_del_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records changed : ' || loan_upd_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records not found : ' || loan_mis_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
log_file_buffer := 'Records failed : ' || loan_fail_cnt;
UTL_FILE.PUT_LINE(log_file_handle, log_file_buffer);
UTL_FILE.FCLOSE(log_file_handle);
--Exception block
exception
when UTL_FILE.INVALID_OPERATION then
sql_code := sqlcode;
sql_msg := sqlerrm;
dbms_output.put_line('Invalid operation performed.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when UTL_FILE.INVALID_FILEHANDLE then
sql_code := sqlcode;
sql_msg := sqlerrm;
dbms_output.put_line('Invalid file handle.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when UTL_FILE.INVALID_PATH then
sql_code := sqlcode;
sql_msg := sqlerrm;
dbms_output.put_line('Invalid file path.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when UTL_FILE.INVALID_MODE then
sql_code := sqlcode;
sql_msg := sqlerrm;
dbms_output.put_line('Invalid file open mode.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when UTL_FILE.WRITE_ERROR then
sql_code := sqlcode;
sql_msg := sqlerrm;
UTL_FILE.FCLOSE(log_file_handle);
dbms_output.put_line('File write error.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);
when others then
sql_code := sqlcode;
sql_msg := sqlerrm;
UTL_FILE.FCLOSE(log_file_handle);
dbms_output.put_line('Unknown error.');
dbms_output.put_line('Error code : ' || sql_code || ' Error Message : ' || sql_msg);

end loan_upload;
Re: ORA-01002: Fetch Out of Sequence [message #39997 is a reply to message #39599] Tue, 03 September 2002 01:19 Go to previous message
Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore
Senior Member
when using proC use
EXEC SQL WHENEVER NOT FOUND DO BREAK before the for loop
/***************
....
....

EXEC SQL WHENEVER NOT FOUND DO BREAK;
for(;;)
{
EXEC SQL FETCH ...;

....
...
} /* end forr loop */
Previous Topic: How to sum a field in 2 (or more) different records
Next Topic: inserting names with single quotations at SQL prompt
Goto Forum:
  


Current Time: Fri Apr 26 07:21:16 CDT 2024