DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: ORA-06502 PL/SQL numeric or value error

  1. #1
    Join Date
    Feb 2002
    Posts
    6
    Please help. I am getting ORA -06502 when i execute the following procedure:

    create or replace procedure splittable is
    v_symbol occ.csymbol_id%type;
    v_putcall occ.cputcallcode%type;
    v_strike occ.strike_price%type;
    v_expire occ.expire_date%type;
    v_exchange occ.exchange_code%type;
    d number;
    A varchar2(3);
    cursor occ_cursor is
    select substr(CSymbol_id,1,3),cputcallcode,strike_price,expire_date,
    exchange_code
    from occ
    where substr(csymbol_id,1,3) is not null and strike_price is not null;
    begin
    delete from occ1;
    open occ_cursor;
    Loop
    fetch occ_cursor into v_symbol,v_putcall,v_strike,
    v_expire,v_exchange;
    exit when occ_cursor%notfound;
    d:=length(v_exchange);

    for i in 1..d loop
    IF substr(v_exchange,i,1)<>'0' then
    A:=substr(v_exchange,i,1);
    if A='1' then
    A:='18';
    end if;
    if A='2' then
    A:='14';
    end if;
    if A='3' then
    A:='16';
    end if;
    if A='4' then
    A:='17';
    end if;
    if A='8' then
    A:='447';
    end if;
    insert into occ1 values(v_symbol,v_putcall,v_expire,A,v_strike);
    end if;
    end loop;


    end loop;
    close occ_cursor;
    commit;
    end;

    the table occ1 is constructed as follows:
    SQL> DESC OCC1
    Name Null? Type
    ------------------------------- -------- ----
    SYMBOL VARCHAR2(3)
    CPUTCALLCODE CHAR(1)
    EXPIRE_DATE DATE
    EXCHANGE_CODE VARCHAR2(11)
    STRIKE_PRICE NUMBER(16,5)


  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    desc occ, please.
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Show me result, please
    select * from v$nls_parameters
    Best wishes!
    Dmitri

  4. #4
    Join Date
    Feb 2002
    Posts
    6
    SQL> desc occ
    Name Null? Type
    ------------------------------- -------- ----
    RECORD_ID NUMBER(3)
    STATUS CHAR(3)
    CSTRIKE NUMBER(12,5)
    CSYMBOL_ID VARCHAR2(6)
    CPUTCALLCODE CHAR(1)
    ADJUSTOR NUMBER(10,3)
    STRIKE_PRICE NUMBER(16,5)
    EXPIRE_DATE DATE
    EXCHANGE_CODE VARCHAR2(11)
    OPRA_CODE CHAR(2)
    ONN CHAR(2)
    USYMBOL_ID VARCHAR2(6)

  5. #5
    Join Date
    Feb 2002
    Posts
    6
    date format from v$nls_parameters:

    NLS_DATE_FORMAT
    DD-MON-YY


    and

    SQL> select expire_date from occ where rownum < 5;

    EXPIRE_DA
    ---------
    22-FEB-02
    22-FEB-02
    28-MAR-02
    28-MAR-02

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    I mean do you use double character set or 1 byte?

    Originally posted by smat
    date format from v$nls_parameters:
    NLS_DATE_FORMAT
    DD-MON-YY
    Best wishes!
    Dmitri

  7. #7
    Join Date
    Feb 2002
    Posts
    6
    SQL> select * from v$nls_parameters;

    PARAMETER
    ---------------------------------------------
    VALUE
    ---------------------------------------------
    NLS_LANGUAGE
    AMERICAN

    NLS_TERRITORY
    AMERICA

    NLS_CURRENCY
    $

    NLS_ISO_CURRENCY
    AMERICA

    NLS_NUMERIC_CHARACTERS
    .,

    NLS_DATE_FORMAT
    DD-MON-YY

    NLS_DATE_LANGUAGE

    PARAMETER
    ---------------------------------------------
    VALUE
    ---------------------------------------------
    AMERICAN

    NLS_CHARACTERSET
    US7ASCII

    NLS_SORT
    BINARY

    NLS_CALENDAR
    GREGORIAN

  8. #8
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    1) try to specify columns
    insert into occ1( .... ) values(v_symbol,v_putcall,v_expire,A,v_strike);
    2) if one does not work, remove 1) from procedure and try to execute it againe.
    At least we will know is that error place or no.
    Best wishes!
    Dmitri

  9. #9
    Join Date
    Feb 2002
    Posts
    6
    ok - i tried to run the cursor by making the procedure work with fewer rows. it worked when i set the cursor to be select....from occ where rownum < 20000;
    However when I had it at rownum < 50000, I got the same ORA-06502 error.

    I think this may have something to do with the size of table occ1 or something - but don't know what parameter to alter and where...

  10. #10
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Now, catch EXCEPTION and show me all values:
    v_symbol
    v_putcall
    v_strike
    v_expire
    v_exchange
    d
    A
    i
    Best wishes!
    Dmitri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width