Hi
Raj,
Thanks
for your reply. I always suspected that the line numbers were wrong but
since I couldn't say decisively why I had to rely upon what the gui's told me
(darn gui...) You proved it. That line is actually pointing to my generic
error logging proc. It's going to be a lot easier to drop and recreate the
one and only index on my error_log table than to do it on my big huge
table. Or just drop the stupid index. How often do I need it
anyway? Boy do I feel like a bonehead for not thinking of looking at
dba_source... I learned something today.
Still,
it failed immediately. But I know now to look to my error log table (which
has a purge running every day at 6am - there's the deletes) instead of focusing
on another. I hadn't even considered this table until now.
In
true elvis style: "thankyouverymuch"
Have a
great day everyone. No more silly emails from me for today, anyway.
Lisa Koivu
Oracle Datatrash
Ackministrator
Fairfield Resorts, Inc.
5259 Coconut
Creek Parkway
Ft. Lauderdale, FL, USA
33063
-----Original Message-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 02, 2003 8:31 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-1410 SillinessLisa,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, sourcefrom user_sourcewhere name = <your package_name)and line between 1960 and 1980order 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.
