Stumped using bind variables

[bmayfield]

We are Delphi 7 and DOA 4.0.4. We get the following error message:

Project WCCTEMPLATE. exe raised exception class EOracleError with message 'ORA-01403: no data found ORA-06512: at line 2'. Process stopped. Use Step or Run to continue.

We havn't got a clue where to look. Here is the SQL statement and the Delphi code it bombs on:

begin
select first_report_of_injuries.ssn
,first_report_of_injuries.dt_inj
--,first_report_of_injuries.rcvd_date
,sysdate
,first_report_of_injuries.mtc_status
,first_report_of_injuries.fri_content
,first_report_of_injuries.empl_emplr_fein
,first_report_of_injuries.emplr_name
,first_report_of_injuries.emplr_addr_1
,first_report_of_injuries.emplr_addr_2
,first_report_of_injuries.emplr_city
,first_report_of_injuries.emplr_state
,first_report_of_injuries.emplr_postal
,first_report_of_injuries.filing_source
,first_report_of_injuries.insd_name
,first_report_of_injuries.insd_loc_nbr
,first_report_of_injuries.sic_code
,first_report_of_injuries.insurer_insurer_fein
,first_report_of_injuries.insurer_name
,first_report_of_injuries.clm_adm_addr_1
,first_report_of_injuries.clm_adm_addr_2
,first_report_of_injuries.clm_adm_city
,first_report_of_injuries.clm_adm_state
,first_report_of_injuries.clm_adm_postal
,first_report_of_injuries.pol_policy_number
,first_report_of_injuries.pol_eff
,first_report_of_injuries.pol_exp
,first_report_of_injuries.tpa_fein
,first_report_of_injuries.tpa_name
,first_report_of_injuries.self_insd_ind
,first_report_of_injuries.clm_adm_clm_nbr
,first_report_of_injuries.insd_rpt_nbr
,first_report_of_injuries.juris
,first_report_of_injuries.ee_l_name
,first_report_of_injuries.ee_f_name
,first_report_of_injuries.ee_mi
,first_report_of_injuries.ee_addr1
,first_report_of_injuries.ee_addr2
,first_report_of_injuries.ee_city
,first_report_of_injuries.ee_state
,first_report_of_injuries.ee_postal
,first_report_of_injuries.ee_phone
,first_report_of_injuries.ee_dt_birth
,first_report_of_injuries.dt_hire
,first_report_of_injuries.full_wages_l_day
,first_report_of_injuries.sal_cont_ind
,first_report_of_injuries.nbr_dys_wkd
,first_report_of_injuries.gender_cd
,first_report_of_injuries.nbr_deps
,first_report_of_injuries.marital_cd
,first_report_of_injuries.wage
,first_report_of_injuries.wage_period
,first_report_of_injuries.occup_descr
,first_report_of_injuries.occup_cde
,first_report_of_injuries.class_cd
,first_report_of_injuries.emplymnt_status
,first_report_of_injuries.time_inj
,first_report_of_injuries.dt_last_dy_wkd
,first_report_of_injuries.postal_inj_site
,first_report_of_injuries.emplr_premis_ind
,first_report_of_injuries.dt_rep_emplr
,first_report_of_injuries.dt_dis_bgn
,first_report_of_injuries.dt_rtw
,first_report_of_injuries.ee_dt_death
,first_report_of_injuries.nature_inj_cd
,first_report_of_injuries.part_body_inj_cd
,first_report_of_injuries.acc_desc_txt
,first_report_of_injuries.cause_inj_cd
,first_report_of_injuries.init_treat_cd
,first_report_of_injuries.dt_rep_clm_adm
,first_report_of_injuries.sroi_chg_source
,first_report_of_injuries.sroi_last_mtc
,first_report_of_injuries.filing_source_type
into :bindSsn
,:bindDoi
,:bindDateReceived
,:bindMtc
,:bindFullPartial
,:bindEmplrFein
,:bindEmplrName
,:bindEmplrAddr1
,:bindEmplrAddr2
,:bindEmplrCity
,:bindEmplrState
,:bindEmplrPostal
,:bindFilingSrc
,:bindInsuredName
,:bindInsuredLoc
,:bindSicCode
,:bindcarrierfein
,:bindcarriername
,:bindcaaddr1
,:bindcaaddr2
,:bindcacity
,:bindcastate
,:bindcapostal
,:bindpolnum
,:bindpoleff
,:bindpolexp
,:bindtpafein
,:bindtpaname
,:bindselfind
,:bindcaclmnum
,:bindinsdrpt
,:bindjuris
,:bindLastName
,:bindFirstName
,:bindMi
,:bindAddr1
,:bindAddr2
,:bindCity
,:bindState
,:bindPostal
,:bindPhone
,:bindDtBirth
,:bindDtHire
,:bindFullWages
,:bindSalCont
,:bindNbrDaysWkd
,:bindGender
,:bindNbrDeps
,:bindMarital
,:bindWage
,:bindWagePeriod
,:bindOccupDesc
,:bindOccupCode
,:bindClassCd
,:bindEmpStatus
,:bindTimeInj
,:bindDtLastWkd
,:bindPostalInjSite
,:bindEmpPremise
,:bindDtRepEmp
,:bindDtDisBgn
,:bindDtRtw
,:bindDtDeath
,:bindNature
,:bindPart
,:bindAccDesc
,:bindCause
,:bindInitTrea
,:bindDtRepClmAdm
,:bindSroiChgSrc
,:bindSroiChgDt
,:bindFilingSrcType
from first_report_of_injuries, claims
where first_report_of_injuries.fri_number = claims.fri_fri_number
and claims.agency_claim_number = :dbeacn;
end;

procedure TFRIA0033frm.btnGetExistingInfoClick(Sender: TObject);
begin
FRIA0033dm.oqGetFroiInfo.Close;
FRIA0033dm.oqGetFroiInfo.SetVariable('DBEACN', StrToInt(dbeAcn.Text));

try
FRIA0033dm.oqGetFroiInfo.Execute;
except
on E:EDatabaseError do
begin
ShowMessage(E.Message);
ShowMessage('Query produced no results, re-enter');
Exit;
end
end;

dbeSsn.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDSSN');
dbeDoi.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDOI');
// dbeDateReceived.Field.Value := sysdate;
dblucbMtc.Field.Value := '02';
dbcbFullPartial.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDFULLPARTIAL');
// dbeFirstRoiFlag.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDROIFLAG');
dbeEmployerFein.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPLRFEIN');
dbeEmployerName.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPLRNAME');
dbeEmployerAddr1.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPLRADDR1');
dbeEmployerAddr2.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPLRADDR2');
dbeEmployerCity.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPLRCITY');
dblucbEmplrState.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPLRSTATE');
dbeEmployerPostal.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPLRPOSTAL');
dblucbFilingSource.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDFILINGSRC');
dbeInsuredName.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDINSUREDNAME');
dbeInsuredLocNbr.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDINSUREDLOC');
dblucbSicCode.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDSICCODE');

dbeCarrierFein.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCARRIERFEIN');
dbeCarrierName.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCARRIERNAME');
dbeClmAdmAddr1.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCAADDR1');
dbeClmAdmAddr2.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCAADDR2');
dbeClmAdmCity.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCACITY');
dblucbClmAdmState.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCASTATE');
dbeClmAdmPostal.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCAPOSTAL');
dbeCarrierPolNbr.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDPOLNUM');
dbePolEffDt.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDPOLEFF');

// null date check, do not want to display null date as 12/30/1899
if dbePolEffDt.Field.Value = '12/30/1899' then
dbePolEffDt.Field.Clear;
dbePolExpDt.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDPOLEXP');
if dbePolExpDt.Field.Value = '12/30/1899' then
dbePolExpDt.Field.Clear;
dbeTpaFein.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDTPAFEIN');
dbeTpaName.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDTPANAME');
dbcbSelfInsuredInd.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDSELFIND');
dbeClmAdmClmNbr.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCACLMNUM');
dbeInsdRptNbr.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDINSDRPT');
dbeJuris.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDJURIS');
dbeLastName.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDLASTNAME');
dbeFirstName.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDFIRSTNAME');
dbeEmployeeAddr1.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDADDR1');
dbeEmployeeAddr2.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDADDR2');
dbeMiddleInitial.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDMI');
dbeEmployeeCity.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCITY');
dblucbEmplrState.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDSTATE');
dbeEmployeePostal.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDPOSTAL');
dbeEmployeePhone.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDPHONE');
dbeEmployeeDob.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDTBIRTH');
if dbeEmployeeDob.Field.Value = '12/30/1899' then
dbeEmployeeDob.Field.Clear;
dbeEmployeeDateHired.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDTHIRE');
if dbeEmployeeDateHired.Field.Value = '12/30/1899' then
dbeEmployeeDateHired.Field.Clear;
dbcbFullPay.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDFULLWAGES');
dbcbSalCont.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDSALCONT');
dbeNbrOfDaysWkd.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDNBRDAYSWKD');
dblucbGender.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDGENDER');
dbeEmployeeNbrDep.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDNBRDEPS');
dblucbMaritalStatus.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDMARITAL');
dbeWeeklyWage.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDWAGE');
dblucbWagePeriod.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDWAGEPERIOD');
dbeEmployeeOccupJob.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDOCCUPDESC');
dblucbOccupCode.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDOCCUPCODE');
dbeClassCode.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCLASSCD');
dblucbEmploymentStatus.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPSTATUS');
dbeTimeOfInj.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDTIMEINJ');
dbeLastWrkDt.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDTLASTWKD');
if dbeLastWrkDt.Field.Value = '12/30/1899' then
dbeLastWrkDt.Field.Clear;
dbeInjuryZip.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDPOSTALINJSITE');
dbcbEmployerPremises.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDEMPPREMISE');
dbeDtEmplrNotified.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDTREPEMP');
if dbeDtEmplrNotified.Field.Value = '12/30/1899' then
dbeDtEmplrNotified.Field.Clear;
dbeDateDisBegan.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDTDISBGN');
if dbeDateDisBegan.Field.Value = '12/30/1899' then
dbeDateDisBegan.Field.Clear;
dbeDateRtw.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDTRTW');
if dbeDateRtw.Field.Value = '12/30/1899' then
dbeDateRtw.Field.Clear;
dbeDateOfDeath.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDTDEATH');
if dbeDateOfDeath.Field.Value = '12/30/1899' then
dbeDateOfDeath.Field.Clear;
dblucbNatureCode.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDNATURE');
dblucbPartBodyCode.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDPART');
dbmInjuryDesc.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BIND');
dblucbCauseCode.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDCAUSE');
dblucbInitialTreatmentCd.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDINITTRE');
dbeDateAdmNotified.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDDTREPCLMADM');
if dbeDateAdmNotified.Field.Value = '12/30/1899' then
dbeDateAdmNotified.Field.Clear;
dbeSroiChgSrc.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDSROICHGSRC');
dbeSroiChgDate.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDSROICHGDT');
if dbeSroiChgDate.Field.Value = '12/30/1899' then
dbeSroiChgDate.Field.Clear;
dbcbFilingSrcType.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('BINDFININGSRCTYPE');

onAll.BtnClick(OracleNavigator.nbEdit);
stbStatusLine.Panels[1].Text := 'Now in Edit mode';

end;

We don't know if it is the if statement before the bolded statement or the statment in bold. We are hoping null data is passed in bind variables whether the type of the bind variable is string, interger or date. We are assuming we do not need to convert types from GetVariable.

Regards,
Bruce

 

[Marco Kalter]

If you can use the Oracle Monitor or enable the Debug property of the queries/datasets involved, you can see exactly which statement leads to the ORA-01403 error.