Lisa,
This is probably not true ...
Oracle doesn't, has never been able to pinpoint exact line number (in
cases such as these) especially with pl/sql packages. I believe the problems can
be found by executing following query ...
select line, type, source
from user_source
where name = <your package_name)
and line between 1960 and 1980
order by line, type
/
Usually in such cases (as you have described) Oracle will point to
the line which contains the actual INSERT word, because for Oracle that is the
LINE-OF-CODE that it is executing. Line numbers from text editors are almost
always wrong.
Am I off the mark? I know the Vodka was really good, but I did drink lot
of coffee too. Please correct me if I am wrong ...
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 31, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-1410 SillinessYou asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of.SQL> desc vegas_mart
Name Null? Type
----------------------------- -------- ------------------
ID NOT NULL NUMBER(38)
CONTRACT_NUMBER NOT NULL NUMBER(9)
LOAD_JOB_ID NOT NULL NUMBER(12)
JULIAN_RUN_DATE NOT NULL NUMBER(12)
FAC_CODE VARCHAR2(1)
OWNER_LAST_NAME VARCHAR2(20)
OWNER_FIRST_NAME VARCHAR2(15)
OWNER_ADDRESS1 VARCHAR2(25)
OWNER_ADDRESS2 VARCHAR2(25)
OWNER_CITY VARCHAR2(18)
OWNER_STATE VARCHAR2(2)
OWNER_ZIP VARCHAR2(9)
NET_PURCHASE_PRICE NUMBER(11,2)
ORIGINAL_DOWN_PAYMENT NUMBER(11,2)
TOTAL_DOWN_PAYMENT NUMBER(11,2)
CR_BAL NUMBER(11,2)
INTEREST_RATE NUMBER(5,2)
FIRST_PAYMENT_DATE DATE
QUALIFICATION_CODE VARCHAR2(1)
PAYMENT_AMOUNT NUMBER(9,2)
PAYMENT_FREQUENCY VARCHAR2(1)
AGING_10_TO_30_DAYS_DUE NUMBER(9,2)
AGING_31_TO_60_DAYS_DUE NUMBER(9,2)
AGING_61_TO_90_DAYS_DUE NUMBER(9,2)
ASSIGNED_LOAN_ADMIN_REP VARCHAR2(2)
DATE_OF_SALE DATE
STATUS_OF_ACCOUNT VARCHAR2(1)
CONTRACT_TYPE VARCHAR2(1)
WAS_PENDER VARCHAR2(1)
CREDIT_LIFE_ON_CONTRACT VARCHAR2(1)
DOCUMENT_STATUS_CODE VARCHAR2(1)
FIXED_WEEK_SALE VARCHAR2(1)
UDI_SALE VARCHAR2(1)
PHASE_NUMBER VARCHAR2(6)
FAIRSHARE_PLUS_MEMBER VARCHAR2(1)
POINTS_OWNED NUMBER(7)
DEED_DATE DATE
PRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1)
RESERVATION_CODE VARCHAR2(4)
INTERNATIONAL_CODE VARCHAR2(1)
UNIT_PHASE_COMPLETION_CODE VARCHAR2(1)
AGING_0_TO_90_DAYS_DUE NUMBER(9,2)
AGING_91_TO_120_DAYS_DUE NUMBER(9,2)
AGING_121_TO_150_DAYS_DUE NUMBER(9,2)
AGING_151_OVER NUMBER(9,2)
LOT_LOCATION VARCHAR2(12)
PAYMENTS_MADE NUMBER(3)
SUPPRESSION_CODE VARCHAR2(1)
ACCRUED_INTEREST_BAL NUMBER(9,2)
PAC_FREEZE_CODE VARCHAR2(1)
CREDIT_CARD_FREEZE_CODE VARCHAR2(1)
ASSOCIATION_NUMBER VARCHAR2(4)
RFS_ASSIGNMENT_DATE DATE
OVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1)
RESERVATION_PENDING VARCHAR2(1)
CREDIT_REPORTING_CODE VARCHAR2(2)
CANCEL_DEFERMENT_REASON_CODE VARCHAR2(2)
EQUITY_IN NUMBER(9,2)
DATE_CODED_7 DATE
ADJUSTABLE_RATE_MORTGAGE VARCHAR2(1)
NUMBER_OF_PAYS_LEFT NUMBER(4)
DEFERRED_INTEREST NUMBER(9,2)
DEFERRED_PRINCIPAL NUMBER(9,2)
CURRENT_YEAR_DEFERMENTS NUMBER(5)
CURRENT_YEAR_TOTAL_DEFERMENTS NUMBER(5)
LAST_PAYMENT_DATE DATE
NEXT_PAYMENT_DATE DATE
PAC_DUE_DATE DATE
EFT_ROUTING_NUMBER VARCHAR2(8)
EFT_ACCOUNT_NUMBER VARCHAR2(30)
EFT_MANUAL_NUMBER VARCHAR2(30)
BANK VARCHAR2(35)
STATUS_CHANGE_DATE DATE
ASSIGNED_LOAN_REP VARCHAR2(2)
CREDIT_CARD_PAC_ACCOUNT VARCHAR2(20)
PRINCIPAL_BALANCE NUMBER(11,2)
CR_DISCOUNT_BALANCE NUMBER(11,2)
CREDIT_LIFE_PREM_BAL NUMBER(11,2)
RFP_PAC_CODE VARCHAR2(1)
RFP_DRAFT_CODE VARCHAR2(1)
RFP_ROUTE_NUMBER VARCHAR2(8)
RFP_EFT_NUMBER VARCHAR2(30)
RFP_MANUAL_NUMBER VARCHAR2(30)
RFP_BANK_NAME VARCHAR2(35)
FPPA_PAC_CODE VARCHAR2(1)
FPPA_DRAFT_CODE VARCHAR2(1)
FPPA_ROUTE_NUMBER VARCHAR2(8)
FPPA_EFT_NUMBER VARCHAR2(30)
FPPA_MANUAL_NUMBER VARCHAR2(30)
FPPA_BANK_NAME VARCHAR2(35)
TS_BAL_DUE_RECOGNIZED NUMBER(9)
TS_LATE_FEE_RECOGNIZED NUMBER(9,2)
TS_YTD_MAINT_FEE_COLL NUMBER(9,2)
TS_MAINT_FEE_AMOUNT NUMBER(9,2)
PO_BIRTH_DATE DATE
TS_LOCATION VARCHAR2(12)
CR_DATE_REC_IN_DEEDING DATE
CR_REFUND VARCHAR2(11)
CR_CREDIT_LIFE_TYPE VARCHAR2(1)
CR_QUALIFICATION_DATE DATE
CR_EQT_IN_FROM_CONT_NO1 VARCHAR2(9)
CR_EQT_IN_FROM_CONT_NO2 VARCHAR2(9)
CR_HC_AMT1 NUMBER(9)
CR_HC_AMT2 NUMBER(9)
CR_HC_POST1 NUMBER(9)
CR_HC_POST2 NUMBER(9)
CR_HC_DATE1 DATE
CR_HC_DATE2 DATE
CR_DATE_IN_LR DATE
CR_TRADE_ALLOW NUMBER(9)
CR_TITLE_INS_CHARGED NUMBER(11)
CR_TITLE_INS_COLLECTED NUMBER(11)
CR_FILING_FEE_CHARGED NUMBER(11)
CR_FILING_FEE_COLLECTED NUMBER(11)
CR_ACCRUED_INT_BAL_RSV NUMBER(11)
CR_LATE_FEE_BAL_RSV NUMBER(11)
AREA_CODE VARCHAR2(3)
PHONE_NUMBER VARCHAR2(7)
PAID_OFF VARCHAR2(1)
EDIT_DATE_TIME DATE
BIANNUAL_FLAG VARCHAR2(10)
FICO_SCORE NUMBER(3)
SOCIAL_SECURITY_NUMBER NUMBER(9)SQL>Here's the code. Line 1970 is cr_hc_amt2.-- Insert the record into the reject table.
INSERT INTO vegas_mart_reject
SELECT
id,
load_date,
load_job_id_v,
contract_number,
fac_code,
owner_last_name,
owner_first_name,
owner_address1,
owner_address2,
owner_city,
owner_state,
owner_zip,
net_purchase_price,
original_down_payment,
total_down_payment,
cr_bal,
interest_rate,
first_payment_date,
qualification_code,
payment_amount,
payment_frequency,
aging_10_to_30_days_due,
aging_31_to_60_days_due,
aging_61_to_90_days_due,
assigned_loan_admin_rep,
date_of_sale,
status_of_account,
contract_type,
was_pender,
credit_life_on_contract,
document_status_code,
fixed_week_sale,
udi_sale,
phase_number,
fairshare_plus_member,
points_owned,
deed_date,
pre_auth_draft_account,
reservation_code,
international_code,
unit_phase_completion_code,
aging_0_to_90_days_due,
aging_91_to_120_days_due,
aging_121_to_150_days_due,
aging_151_over,
lot_location,
payments_made,
suppression_code,
accrued_interest_bal,
pac_freeze_code,
credit_card_freeze_code,
association_number,
rfs_assignment_date,
override_maint_fee_balance,
reservation_pending,
credit_reporting_code,
cancel_deferment_reason_code,
equity_in,
date_coded_7,
adjustable_rate_mortgage,
number_of_pays_left,
deferred_interest,
deferred_principal,
current_year_deferments,
current_year_total_deferments,
last_payment_date,
next_payment_date,
pac_due_date,
eft_routing_number,
eft_account_number,
eft_manual_number,
bank,
status_change_date,
assigned_loan_rep,
credit_card_pac_account,
principal_balance,
cr_discount_balance,
credit_life_prem_bal,
rfp_pac_code,
rfp_draft_code,
rfp_route_number,
rfp_eft_number,
rfp_manual_number,
rfp_bank_name,
fppa_pac_code,
fppa_draft_code,
fppa_route_number,
fppa_eft_number,
fppa_manual_number,
fppa_bank_name,
ts_bal_due_recognized,
ts_late_fee_recognized,
ts_ytd_maint_fee_coll,
ts_maint_fee_amount,
po_birth_date,
ssn,
ts_location,
cr_date_rec_in_deeding,
area_code,
phone_number,
cr_refund,
cr_credit_life_type,
cr_qualification_date,
cr_eqt_in_from_cont_no1,
cr_eqt_in_from_cont_no2,
cr_hc_amt1,
cr_hc_amt2, <-- Line 1970
cr_hc_post1,
cr_hc_post2,
cr_hc_date1,
cr_hc_date2,
cr_date_in_lr,
cr_trade_allow,
cr_title_ins_charged,
cr_title_ins_collected,
cr_filing_fee_charged,
cr_filing_fee_collected,
cr_accrued_int_bal_rsv,
cr_late_fee_bal_rsv,
fico_score
FROM vegas_raw
WHERE id = vegas_raw_rec.id;Makes no sense at all. And if you read this far, don't say I didn't warn you.
********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
