Home » SQL & PL/SQL » SQL & PL/SQL » Declations unknown (SQL Developer 4.0.3.16)
Declations unknown [message #656491] |
Fri, 07 October 2016 15:16 |
|
Donfox09
Messages: 4 Registered: October 2016
|
Junior Member |
|
|
When testing a function from a much larger set of PLSQL, I get an error that a certain object must be declared.
When I check the entire code, this object is never declared. The whole code normally executes in about an hour.
Her is the error - PLS-00201: identifier 'WS_TEST_DATE' must be declared
Here is the function -
FUNCTION count_saturdays(begin_date IN date, end_date IN date) return number
is
ws_saturdays number;
ws_test_date date;
ws_check_date date;
ws_date1 varchar(8);
ws_date2 varchar(8);
ws_day_of_week varchar(20);
ws_loop_count number;
BEGIN
-- initialize counter
ws_test_date := begin_date;
ws_check_date := begin_date;
ws_loop_count := 0;
ws_saturdays := 0;
ws_day_of_week := ' ';
-- get saturday date for beginning week
-- select to_char(to_date(ws_test_date, 'dd-mon-yy'), 'Day')
select to_char(ws_test_date, 'DAY')
into ws_day_of_week
from dual;
--dbms_output.put_line('First Date = '||ws_test_date);
--dbms_output.put_line('Day of Week = '||ws_day_of_week);
if (rtrim(ws_day_of_week, ' ') = 'Saturday') then
ws_saturdays := 1;
end if;
--dbms_output.put_line('Begin Saturdays routine - begin_date = '||begin_date|| ' end_date = '||end_date);
while ws_loop_count <= 52
loop
-- get saturday date for beginning week
-- select next_day(trunc(to_date(ws_check_date,'dd-mmm-yy')), 'saturday')
-- select next_day(trunc(to_date(ws_check_date,'dd-mon-yy')), 'saturday')
select next_day(trunc(ws_check_date), 'saturday')
into ws_test_date
from dual;
ws_check_date := ws_test_date;
ws_date1 := to_char(ws_check_date,'yyyymmdd');
ws_date2 := to_char(end_date,'yyyymmdd');
-- ws_date1 := substr(to_date(ws_check_date,'yyyy-mm-dd'),1,4)||substr(to_date(ws_check_date,'yyyy-mm-dd'),6,2)||substr(to_date(ws_check_date,'yyyy- mm-dd'),9,2) ;
-- ws_date2 := substr(to_date(end_date,'yyyy-mm-dd'),1,4)||substr(to_date(end_date,'yyyy-mm-dd'),6,2)||substr(to_date(end_date,'yyyy-mm-dd'),9,2) ;
-- dbms_output.put_line('date1 = '||ws_date1);
-- dbms_output.put_line('date2 = '||ws_date2);
if (ws_date1 <= ws_date2) then
ws_saturdays := ws_saturdays + 1;
else
ws_loop_count := 52;
end if;
if ws_saturdays > 52 then
ws_saturdays := 52;
ws_loop_count := 52;
end if;
ws_loop_count := ws_loop_count + 1;
-- dbms_output.put_line('Counting Saturdays routine - ws_test_date = '||ws_test_date|| ' end_date = '||end_date);
-- dbms_output.put_line('In Saturdays counter = '||ws_saturdays);
end loop;
-- dbms_output.put_line('Saturdays counter = '||ws_saturdays);
-- dbms_output.put_line('Loop counter = '||ws_loop_count);
return ws_saturdays;
END count_saturdays;
What am I missing?
|
|
|
|
|
Re: Declations unknown [message #656494 is a reply to message #656493] |
Fri, 07 October 2016 17:45 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
It works for me.
1 CREATE OR REPLACE FUNCTION count_saturdays(begin_date IN date, end_date IN date) return number
2 is
3 ws_saturdays number;
4 ws_test_date date;
5 ws_check_date date;
6 ws_date1 varchar(8);
7 ws_date2 varchar(8);
8 ws_day_of_week varchar(20);
9 ws_loop_count number;
10 BEGIN
11 -- initialize counter
12 ws_test_date := begin_date;
13 ws_check_date := begin_date;
14 ws_loop_count := 0;
15 ws_saturdays := 0;
16 ws_day_of_week := ' ';
17 -- get saturday date for beginning week�
18 -- select to_char(to_date(ws_test_date, 'dd-mon-yy'), 'Day')
19 select to_char(ws_test_date, 'DAY')
20 into ws_day_of_week
21 from dual;
22 --dbms_output.put_line('First Date = '||ws_test_date);
23 --dbms_output.put_line('Day of Week = '||ws_day_of_week);
24 if (rtrim(ws_day_of_week, ' ') = 'Saturday') then
25 ws_saturdays := 1;
26 end if;
27 --dbms_output.put_line('Begin Saturdays routine - begin_date = '||begin_date|| ' end_date = '||end_date);
28 while ws_loop_count <= 52
29 loop
30 -- get saturday date for beginning week�
31 -- select next_day(trunc(to_date(ws_check_date,'dd-mmm-yy')), 'saturday')�
32 -- select next_day(trunc(to_date(ws_check_date,'dd-mon-yy')), 'saturday')�
33 select next_day(trunc(ws_check_date), 'saturday')
34 into ws_test_date
35 from dual;
36 ws_check_date := ws_test_date;
37 ws_date1 := to_char(ws_check_date,'yyyymmdd');
38 ws_date2 := to_char(end_date,'yyyymmdd');
39 -- ws_date1 := substr(to_date(ws_check_date,'yyyy-mm-dd'),1,4)||substr(to_date(ws_check_date,'yyyy-mm-dd'),6,2)||substr(to_date(ws_check_date,'yyyy- mm-dd'),9,2) ;
40 -- ws_date2 := substr(to_date(end_date,'yyyy-mm-dd'),1,4)||substr(to_date(end_date,'yyyy-mm-dd'),6,2)||substr(to_date(end_date,'yyyy-mm-dd'),9,2) ;
41 -- dbms_output.put_line('date1 = '||ws_date1);
42 -- dbms_output.put_line('date2 = '||ws_date2);
43 if (ws_date1 <= ws_date2) then
44 ws_saturdays := ws_saturdays + 1;
45 else
46 ws_loop_count := 52;
47 end if;
48 if ws_saturdays > 52 then
49 ws_saturdays := 52;
50 ws_loop_count := 52;
51 end if;
52 ws_loop_count := ws_loop_count + 1;
53 -- dbms_output.put_line('Counting Saturdays routine - ws_test_date = '||ws_test_date|| ' end_date = '||end_date);
54 -- dbms_output.put_line('In Saturdays counter = '||ws_saturdays);
55 end loop;
56 -- dbms_output.put_line('Saturdays counter = '||ws_saturdays);
57 -- dbms_output.put_line('Loop counter = '||ws_loop_count);
58 return ws_saturdays;
59* END count_saturdays;
60 /
Function created.
SQL>
|
|
|
|
|
Re: Declations unknown [message #656500 is a reply to message #656497] |
Fri, 07 October 2016 21:34 |
|
Donfox09
Messages: 4 Registered: October 2016
|
Junior Member |
|
|
That is correct. I figured it out BEFORE I saw your reply. I do appreciate your willingness to help although you did not point it out what was causing the problem. You just added the "CREATE OR REPLACE" command.
|
|
|
Goto Forum:
Current Time: Sun May 19 10:32:48 CDT 2024
|