Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql code
pl/sql code [message #231463] Tue, 17 April 2007 03:34 Go to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Following code is compiling properly but it gives the following error during run time:
ERROR at line 3:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at "JANA.PR_ALL_DATES_LOAD", line 32
ORA-06512: at line 2.
Can any one help me in figuring this out?

CREATE OR REPLACE procedure pr_all_dates_load
load_date date;
begin
load_date:=sysdate;
loop
load_date := load_date+1;
insert into all_dates (date_key,day_no_in_calender_week,day,FULL_DATE_DSCR,DAY_OF_WEEK,
DAY_NO_IN_CALENDER_MONTH,DAY_NO_IN_CALENDER_YEAR)
values (
all_dates_seq.nextval,
Decode(To_Char(load_date,'D'),'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7),
load_date,
load_date,
Decode(To_Char(load_date,'D'),'1','MON','2','TUE','3','WED','4','THU','5','FRI','6','SAT','7','SUN'),
TO_CHAR(load_date,'dd'),
To_char(load_date,'DDD')
);
If load_date=to_Date('04/18/2007','mm/dd/yyyy') Then
Exit;
End If;
End Loop;
commit;
end;
/

[Updated on: Tue, 17 April 2007 03:37]

Report message to a moderator

Re: pl/sql code [message #231467 is a reply to message #231463] Tue, 17 April 2007 03:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't see a line 32, so did you maybe leave out anything?
Re: pl/sql code [message #231501 is a reply to message #231467] Tue, 17 April 2007 05:35 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Thanks for your reply.i took of the unwanted variable declaration part.here is the original code that caused the error :
CREATE OR REPLACE procedure pr_all_dates_load
as
v_DATE_KEY NUMBER;
v_DAY_NO_IN_CALENDER_WEEK NUMBER;
v_DAY DATE;
v_FULL_DATE_DSCR VARCHAR2(15);
v_DAY_OF_WEEK VARCHAR2(8);
v_DAY_NO_IN_CALENDER_MONTH NUMBER;
v_DAY_NO_IN_CALENDER_YEAR NUMBER;
v_WEEK_NO NUMBER;
v_WEEK_ST_DAY NUMBER;
v_WEEK_ED_DAY DATE;
v_WEEK_DSCR VARCHAR2(30);
v_MONTH_NO NUMBER;
v_MONTH_DSCR VARCHAR2(30);
v_YEAR_MONTH_YYYY_MM VARCHAR2(8);
v_QUARTER_NO NUMBER;
v_QUARTER_DSCR VARCHAR2(30);
v_YEAR_QUARTER_YYYY_QQ VARCHAR2(8);
v_HALF_YEAR_NO NUMBER;
v_HALF_YEAR_DSCR VARCHAR2(30);
v_YEAR_DSCR VARCHAR2(30);
v_HOLIDAY_FLG VARCHAR2(1);
v_WEEKDAY_FLG NUMBER(1);
v_EVENT_TYPE VARCHAR2(5);
v_MAJOR_EVENT VARCHAR2(60);
load_date date;
begin
dbms_output.put_line('hi');
load_date:=sysdate;
loop
load_date := load_date+1;
insert into all_dates (date_key,day_no_in_calender_week,day,FULL_DATE_DSCR,DAY_OF_WEEK,
DAY_NO_IN_CALENDER_MONTH,DAY_NO_IN_CALENDER_YEAR)
values (
all_dates_seq.nextval,
Decode(To_Char(load_date,'D'),'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7),
load_date,
load_date,
Decode(To_Char(load_date,'D'),'1','MON','2','TUE','3','WED','4','THU','5','FRI','6','SAT','7','SUN'),
TO_CHAR(load_date,'dd'),
To_char(load_date,'DDD')
);
If load_date=to_Date('04/18/2007','mm/dd/yyyy') Then
Exit;
End If;
End Loop;
commit;
end;
/
Re: pl/sql code [message #231508 is a reply to message #231501] Tue, 17 April 2007 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one is line 32?

Regards
Michel
Re: pl/sql code [message #231511 is a reply to message #231508] Tue, 17 April 2007 06:28 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
please find the code along with line numbers.Your help in this regard will be appreciated.
1CREATE OR REPLACE procedure pr_all_dates_load
2 as
3 v_DATE_KEY NUMBER;
4 v_DAY_NO_IN_CALENDER_WEEK NUMBER;
5 v_DAY DATE;
6 v_FULL_DATE_DSCR VARCHAR2(15);
7 v_DAY_OF_WEEK VARCHAR2(8);
8 v_DAY_NO_IN_CALENDER_MONTH NUMBER;
9 v_DAY_NO_IN_CALENDER_YEAR NUMBER;
10 v_WEEK_NO NUMBER;
11 v_WEEK_ST_DAY NUMBER;
12 v_WEEK_ED_DAY DATE;
13 v_WEEK_DSCR VARCHAR2(30);
14 v_MONTH_NO NUMBER;
15 v_MONTH_DSCR VARCHAR2(30);
16 v_YEAR_MONTH_YYYY_MM VARCHAR2(8);
17 v_QUARTER_NO NUMBER;
18 v_QUARTER_DSCR VARCHAR2(30);
19 v_YEAR_QUARTER_YYYY_QQ VARCHAR2(8);
20 v_HALF_YEAR_NO NUMBER;
21 v_HALF_YEAR_DSCR VARCHAR2(30);
22 v_YEAR_DSCR VARCHAR2(30);
23 v_HOLIDAY_FLG VARCHAR2(1);
24 v_WEEKDAY_FLG NUMBER(1);
25 v_EVENT_TYPE VARCHAR2(5);
26 v_MAJOR_EVENT VARCHAR2(60);
27 load_date date;
28 begin
29 dbms_output.put_line('hi');
30 load_date:=sysdate;
31 loop
32 load_date := load_date+1;
33 insert into all_dates (date_key,day_no_in_calender_week,day,FULL_DATE_DSCR,DAY_OF_WEEK,
34 DAY_NO_IN_CALENDER_MONTH,DAY_NO_IN_CALENDER_YEAR)
35 values (
36 all_dates_seq.nextval,
37 Decode(To_Char(load_date,'D'),'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7),
38 load_date,
39 load_date,
40 Decode(To_Char(load_date,'D'),'1','MON','2','TUE','3','WED','4','THU','5','FRI','6','SAT','7','SUN'),
41 TO_CHAR(load_date,'dd'),
42 To_char(load_date,'DDD')
43 );
44 If load_date=to_Date('04/18/2007','mm/dd/yyyy') Then
45 Exit;
46 End If;
47 End Loop;
48 commit;
49 end;
/
Re: pl/sql code [message #231521 is a reply to message #231511] Tue, 17 April 2007 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clear that you never reached the exit condition and so loop to after the maximum date.

Unless you execute your script exactly at 00:00:00, it is the expected behaviour.

Regards
Michel
Re: pl/sql code [message #231546 is a reply to message #231521] Tue, 17 April 2007 08:00 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
hi michel,
I used for loop instead of my old logic.But iam getting the following error at the for statement :
PLS-00382: expression is of wrong type
CREATE OR REPLACE procedure pr_all_dates_load
as
v_DATE_KEY NUMBER;
v_DAY_NO_IN_CALENDER_WEEK NUMBER;
v_DAY DATE;
v_FULL_DATE_DSCR VARCHAR2(15);
v_DAY_OF_WEEK VARCHAR2(8);
v_DAY_NO_IN_CALENDER_MONTH NUMBER;
v_DAY_NO_IN_CALENDER_YEAR NUMBER;
v_WEEK_NO NUMBER;
v_WEEK_ST_DAY NUMBER;
v_WEEK_ED_DAY DATE;
v_WEEK_DSCR VARCHAR2(30);
v_MONTH_NO NUMBER;
v_MONTH_DSCR VARCHAR2(30);
v_YEAR_MONTH_YYYY_MM VARCHAR2(8);
v_QUARTER_NO NUMBER;
v_QUARTER_DSCR VARCHAR2(30);
v_YEAR_QUARTER_YYYY_QQ VARCHAR2(8);
v_HALF_YEAR_NO NUMBER;
v_HALF_YEAR_DSCR VARCHAR2(30);
v_YEAR_DSCR VARCHAR2(30);
v_HOLIDAY_FLG VARCHAR2(1);
v_WEEKDAY_FLG NUMBER(1);
v_EVENT_TYPE VARCHAR2(5);
v_MAJOR_EVENT VARCHAR2(60);
load_date date;
final_date date;
begin
final_date:=sysdate+1;
for load_date in sysdate..final_date loop
insert into all_dates (date_key,day_no_in_calender_week,day,FULL_DATE_DSCR,DAY_OF_WEEK,
DAY_NO_IN_CALENDER_MONTH,DAY_NO_IN_CALENDER_YEAR)
values (
all_dates_seq.nextval,
Decode(To_Char(load_date,'D'),'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7),
load_date,
load_date,
Decode(To_Char(load_date,'D'),'1','MONDAY','2','TUESDAY','3','WEDNESDAY','4','THURSDAY','5','FRIDAY','6','SATURDAY','7','SUNDAY'),
TO_CHAR(load_date,'dd'),
To_char(load_date,'DDD')
);
End Loop;
commit;
end;
/
Re: pl/sql code [message #231548 is a reply to message #231546] Tue, 17 April 2007 08:04 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
FOR loops can only use numbers as values.
Re: pl/sql code [message #231639 is a reply to message #231548] Tue, 17 April 2007 15:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
assign trunc(sysdate) to load_date in your initial code.
Re: pl/sql code [message #231822 is a reply to message #231639] Wed, 18 April 2007 09:14 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Iam getting the error '(S223) Expecting: statement_terminator BEGIN CASE DECLARE END IDENTIFIER IF LOOP' when i compile the following code :
CREATE OR REPLACE procedure pr_all_dates_load
as
v_DATE_KEY NUMBER;
v_DAY_NO_IN_CALENDER_WEEK NUMBER;
v_DAY DATE;
v_FULL_DATE_DSCR VARCHAR2(15);
v_DAY_OF_WEEK VARCHAR2(8);
v_DAY_NO_IN_CALENDER_MONTH NUMBER;
v_DAY_NO_IN_CALENDER_YEAR NUMBER;
v_WEEK_NO NUMBER;
v_WEEK_ST_DAY NUMBER;
v_WEEK_ED_DAY DATE;
v_WEEK_DSCR VARCHAR2(30);
v_MONTH_NO NUMBER;
v_MONTH_DSCR VARCHAR2(30);
v_YEAR_MONTH_YYYY_MM VARCHAR2(8);
v_QUARTER_NO NUMBER;
v_QUARTER_DSCR VARCHAR2(30);
v_YEAR_QUARTER_YYYY_QQ VARCHAR2(8);
v_HALF_YEAR_NO NUMBER;
v_HALF_YEAR_DSCR VARCHAR2(30);
v_YEAR_DSCR VARCHAR2(30);
v_HOLIDAY_FLG VARCHAR2(1);
v_WEEKDAY_FLG NUMBER(1);
v_EVENT_TYPE VARCHAR2(5);
v_MAJOR_EVENT VARCHAR2(60);
load_date date;
begin
load_date:=sysdate;
loop
dbms_output.put_line('hi');
load_date := load_date+1;
insert into all_dates (date_key,day_no_in_calender_week,day,FULL_DATE_DSCR,DAY_OF_WEEK,
DAY_NO_IN_CALENDER_MONTH,DAY_NO_IN_CALENDER_YEAR,WEEK_NO,WEEK_ST_DAY,WEEK_ED_DAY,WEEK_DSCR,
MONTH_NO,MONTH_DSCR,YEAR_MONTH_YYYY_MM,QUARTER_NO,QUARTER_DSCR)
values (
all_dates_seq.nextval,
Decode(To_Char(load_date,'D'),'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7),
trunc(load_date),
to_char(load_date,'fmDD Month YYYY'),
Decode(To_Char(load_date,'D'),'1','MON','2','TUE','3','WED','4','THU','5','FRI','6','SAT','7','SUN'),
TO_CHAR(load_date,'dd'),
To_char(load_date,'DDD'),
to_char(load_date,'IW'),
Decode(To_Char(load_date,'D'),'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1),
Decode(To_Char(load_date,'D'),'1',load_date,'2',trunc(load_date)-1,'3',trunc(load_date)-2,'4',trunc(load_date)-3,'5',trunc(load_date) -4,'6',trunc(load_date)-5,'7',trunc(load_date)-6),
to_char(load_date,'fmYYYY "W" IW'),
Decode(To_Char(load_date,'MM'),'01',1,'02',2,'03',3,'04',4,'05',5,'06',6,'07',7),
to_char(load_date,'fmMONTH'),
to_char(load_date,'YYYYMM'),
to_char(load_date,'Q'),
to_char(load_date,'fmQspth "QUARTER")
);
If load_date>to_Date('05/18/2007','mm/dd/yyyy') Then
Exit;
End If;
End Loop;
commit;
end;
/
can any one help me out?
Re: pl/sql code [message #231824 is a reply to message #231463] Wed, 18 April 2007 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
No, not when you refuse to use "code tags" as described in the #1 STICKY post
Re: pl/sql code [message #231825 is a reply to message #231822] Wed, 18 April 2007 09:19 Go to previous message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
sorry,i missed a comma and hence the problem.
Previous Topic: challenging question can any one can solve it and help me
Next Topic: Grouping problem by year
Goto Forum:
  


Current Time: Sun Dec 11 03:55:32 CST 2016

Total time taken to generate the page: 0.09684 seconds