Home » SQL & PL/SQL » SQL & PL/SQL » Declations unknown (SQL Developer 4.0.3.16)
icon10.gif  Declations unknown [message #656491] Fri, 07 October 2016 15:16 Go to next message
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 #656492 is a reply to message #656491] Fri, 07 October 2016 15:48 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Never do in PL/SQL that which can be done in plain SQL.
Re: Declations unknown [message #656493 is a reply to message #656492] Fri, 07 October 2016 16:06 Go to previous messageGo to next message
Donfox09
Messages: 4
Registered: October 2016
Junior Member
Thank you. Will do.
Re: Declations unknown [message #656494 is a reply to message #656493] Fri, 07 October 2016 17:45 Go to previous messageGo to next message
BlackSwan
Messages: 25858
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 #656495 is a reply to message #656494] Fri, 07 October 2016 18:11 Go to previous messageGo to next message
Donfox09
Messages: 4
Registered: October 2016
Junior Member
I figured it out as well. I was not including the CREATE OR REPLACE command.
Re: Declations unknown [message #656497 is a reply to message #656495] Fri, 07 October 2016 18:51 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
Donfox09 wrote on Fri, 07 October 2016 16:11
I figured it out as well. I was not including the CREATE OR REPLACE command.
YOU figured it out! Smile
I showed you the error of your ways.
Re: Declations unknown [message #656500 is a reply to message #656497] Fri, 07 October 2016 21:34 Go to previous message
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.
Previous Topic: Displaying DESC_TAB parms
Next Topic: If exists clause in oracle
Goto Forum:
  


Current Time: Fri Feb 23 10:54:03 CST 2018

Total time taken to generate the page: 0.01733 seconds