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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
