OraFAQ Forum: SQL & PL/SQL » Very urgent date problem in oracle 8i

Home » SQL & PL/SQL » SQL & PL/SQL » Very urgent date problem in oracle 8i

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Very urgent date problem in oracle 8i [message #18505]

Sat, 02 February 2002 00:18

Emine Deniz
Messages: 2
Registered: January 2002

Junior Member

Hello,

Please could you help me? which is related to yesterday' s question
after calculations between two date I get an error ora -06502 error...
because of date but I can't understand why how can I declare
a11,b11,c11,gun,ay,yil ?

Thank you very much.

PROCEDURE get_all_at_onces IS

TYPE p_sicil_table_type IS table of p1.p_sicil%TYPE
INDEX BY BINARY_INTEGER;
TYPE p_adi_table_type IS table of p1.p_adi%TYPE
INDEX BY BINARY_INTEGER;
TYPE p_soyadi_table_type IS table of p1.p_soyadi%TYPE
INDEX BY BINARY_INTEGER;
TYPE p_tay_bastar_table_type IS table of p3.p_tay_bastar%TYPE
INDEX BY BINARY_INTEGER;
TYPE p_ay_tar_table_type IS table of p3.p_ay_tar%TYPE
INDEX BY BINARY_INTEGER;

p_sicil_tab p_sicil_table_type;
p_adi_tab p_adi_table_type;
p_soyadi_tab p_soyadi_table_type;
p_tay_bastar_tab p_tay_bastar_table_type;
p_ay_tar_tab p_ay_tar_table_type;
a number;
b number;
c number;
a1 number;
b1 number;
c1 number;
i number;
a11 number:=0;
b11 number:=0;
c11 number:=0;
gun number:=0;
ay number:= 0;
yil number:=0;

CURSOR sicil IS SELECT
DISTINCT(a.p_sicil),a.p_adi,a.p_soyadi,b.p_tay_bastar,
nvl(b.p_ay_tar,sysdate)
from p1 a,p3 b
where a.p_sicil=b.p_sicil and b.p_sicil='112';
begin
open sicil;
FETCH sicil BULK COLLECT INTO
p_sicil_tab,p_adi_tab,p_soyadi_tab,p_tay_bastar_tab,p_ay_tar_tab;

for i in 1.. p_tay_bastar_tab.last loop
a:= to_number(to_char(p_tay_bastar_tab(i),'dd'));
b:= to_number(to_char(p_tay_bastar_tab(i),'mm'));
c:= to_number(to_char(p_tay_bastar_tab(i),'yyyy'));
a1:= to_number(to_char(p_ay_tar_tab(i),'dd'));
b1:= to_number(to_char(p_ay_tar_tab(i),'mm'));
c1:= to_number(to_char(p_ay_tar_tab(i),'yyyy'));

IF (a1>a and b1>b and c1>=c) THEN
a11:=a1-a;
b11:=b1-b;
c11:=c1-c;
ELSIF (a1<a and b1>b and c1>=c) THEN
a11:=(30+a1)-a;
b11:=(b1-1)-b;
c11:= c1-c;
ELSIF (a1<a and b1<b and c1>=c ) THEN
a11:=(30+a1)-a;
b11:=((b1-1)+12)-b;
c11:=(c1-1)-c;
ELSIF (a1>a and b1<b and c1>=c) THEN
a11:=a1-a;
b11:=(b1+12)-b;
c11:=(c1-1)-c;
ELSIF (a1<a and b1=b and c1>=c) THEN
a11:=(30+a1)-a;
b11:=((b1-1)+12)-b;
c11:=(c1-1)-c;
ELSIF (a1=a and b1=b and c1>=c) THEN
a11:=a1-a;
b11:=b1-b;
c11:=c1-c;
ELSIF (a1=a and b1<b and c1>=c) THEN
a11:=a1-a;
b11:=(b1+12)-b;
c11:=(c1-1)-c;
ELSIF (a1=a and b1>b and c1>=c) THEN
a11:=a1-a;
b11:=b1-b;
c11:=c1-c;
ELSIF (a1>a and b1=b and c1>=c) THEN
a11:=a1-a;
b11:=b1-b;
c11:=c1-c;
END IF;
end loop;
gun :=gun+a11;
ay :=ay +b11;
yil :=yil+c11;
INSERT INTO p27(p_sicil,adi,soyadi,topgun,topay,topyil)
VALUES (p_sicil_tab(i),p_adi_tab(i),
p_soyadi_tab(i),gun,ay,yil);
commit;
CLOSE sicil;
END;

Report message to a moderator

Re: Very urgent date problem in oracle 8i [message #18510 is a reply to message #18505]

Sat, 02 February 2002 05:53

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

i found one error in first look..

you are referencing loop index after closing for loop

start loop gain
for i in 1.. p_tay_bastar_tab.last loop
INSERT INTO p27(p_sicil,adi,soyadi,topgun,topay,topyil)
VALUES (p_sicil_tab(i),p_adi_tab(i),
p_soyadi_tab(i),gun,ay,yil);
end loop

Report message to a moderator

Previous Topic:	Reg-version
Next Topic:	sql function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 06:26:11 CDT 2024