-
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)
-
-
Show me result, please
select * from v$nls_parameters
Best wishes!
Dmitri
-
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)
-
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
-
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
-
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
-
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
-
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...
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|