Oracle-L: RE: ORA-1410 Silliness

From: Koivu, Lisa <Lisa.Koivu_at_efairfield.com>
Date: Thu, 02 Jan 2003 08:24:52 -0800
Message-ID: <F001.005258AE.20030102082452@fatcity.com>

Not at this point. I believe when the pl/sql tables get too big it blows up with ora-4031 or one of the common memory errors, I've seen it happen before. I may have only a stupid windows machine, but I have so much RAM... kudos to the brilliant people who didn't listen to me and decided what they were going to give me for hardware.

But I digress. I will try it with bringing down the commit interval (which controls the size of the tables) for more giggles.

And Waleed - I shudder to think of how long this procedure would take if I wasn't using bulk inserts. It would be forever. I could run this procedure many times over and still be waiting for the conventional insert to complete. At this point I'll live with the ora-1410 before I go that route.

Thanks again for your input, both of you

-----Original Message-----
Sent: Thursday, January 02, 2003 10:30 AM To: Multiple recipients of list ORACLE-L

total speculation since I'm not a PL/SQL expert -- could the amount of data you are manipulating in the stored procedure somehow blow out available memory?

"Koivu, Lisa" <Lisa.Koivu_at_efairfield.com> wrote:
> Amount of data, definately. This table grows by ~2.5GB weekly. Have
> you
> ever seen data volume begin to cause problems?
>
> Thanks Rachel
>
> -----Original Message-----
> Sent: Tuesday, December 31, 2002 5:05 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Okay this means that the statement itself, which is being pointed to
> by
> the error "line", is not the real culprit. Unfortunately, I don't
> know
> of a good debugger for PL/SQL that lets you step through line by
> line.
> let me rephrase that, I know of no freeware one :)
>
> since you say this has only recently started happening, and since the
> sql runs on its own, what else has changed since the problem started?
> Amount of data? Other apps on the box taking away memory?
>
> Rachel
>
> --- "Koivu, Lisa" <Lisa.Koivu_at_efairfield.com> wrote:
> > Hi Rachel,
> >
> > Just tried it and it works. Thanks for your suggestion.
> >
> > Lisa
> >
> > -----Original Message-----
> > Sent: Tuesday, December 31, 2002 2:19 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > dumb question -- did you extract the insert statement and run it in
> > sqlplus? Does it run there or does it go boom as well?
> >
> >
> > --- "Koivu, Lisa" <Lisa.Koivu_at_efairfield.com> wrote:
> > > 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,
>
=== message truncated ===

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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).

-- 
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 - 10:24:52 CST