Oracle-L: RE: ORA-1410 Silliness

From: Koivu, Lisa <Lisa.Koivu_at_efairfield.com>
Date: Thu, 02 Jan 2003 07:49:22 -0800
Message-ID: <F001.005257EC.20030102074922@fatcity.com>

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-----
Sent: Thursday, January 02, 2003 8:31 AM To: Multiple recipients of list ORACLE-L

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-----
Sent: Tuesday, December 31, 2002 12:49 PM To: Multiple recipients of list ORACLE-L

You 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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Koivu, Lisa
INET: Lisa.Koivu_at_efairfield.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jan 02 2003 - 09:49:22 CST