Home » SQL & PL/SQL » SQL & PL/SQL » populating the collection of date type with date values of all saturday 's of one month -- Help (oracle 10g)
populating the collection of date type with date values of all saturday 's of one month -- Help [message #579954] Mon, 18 March 2013 05:13 Go to next message
slotankar
Messages: 5
Registered: March 2013
Location: MUMBAI
Junior Member
declare
v_date date;
type t_date is table of date;
col_date t_date := t_date(SYSDATE);
i number default 1;
begin
Select trunc(sysdate,'MON') into v_date from dual;
DBMS_OUTPUT.PUT_LINE(v_date);
if to_char(v_date,'FMDAY') = 'SATURDAY' THEN
col_date(i) := v_date;
DBMS_OUTPUT.PUT_LINE(col_date(i));
else
col_date(i) := next_day(v_date,'SATURDAY');
end if;
LOOP
col_date.extend(1);
col_date(i) := next_day(v_date,'SATURDAY');
exit when col_date(i)>last_day(v_date);
end loop;
/*
while col_date(i) <= last_day(sysdate) loop
col_date(i):= next_day(v_date,'SATURDAY');
--v_date := col_date(i);
i := i +1;
end loop; */
end;
Re: populating the collection of date type with date values of all saturday 's of one month -- Help [message #579955 is a reply to message #579954] Mon, 18 March 2013 05:26 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
This seems like a very awkward way of performing what is a fairly simple task. Why not just use SQL to create your resultset, here is a starter for 10:
SELECT TRUNC(SYSDATE,'MM')-1+LEVEL my_date
     , to_char(TRUNC(SYSDATE,'MM')-1+LEVEL,'Dy') my_day
FROM dual
CONNECT BY LEVEL <= last_day(SYSDATE)-TRUNC(SYSDATE,'MM')
Re: populating the collection of date type with date values of all saturday 's of one month -- Help [message #579956 is a reply to message #579955] Mon, 18 March 2013 05:53 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
I think instead of
CONNECT BY LEVEL <= last_day(SYSDATE)-TRUNC(SYSDATE,'MM')

It has to be
CONNECT BY LEVEL <= last_day(SYSDATE)-TRUNC(SYSDATE,'MM') + 1

Otherwise, it won't give you the last day of the current month.

Regards,
Dariyoosh

[Updated on: Mon, 18 March 2013 05:54]

Report message to a moderator

Re: populating the collection of date type with date values of all saturday 's of one month -- Help [message #579958 is a reply to message #579956] Mon, 18 March 2013 06:00 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Agreed, (hence the 'Starter for 10) Wink Can't make it TOO easy.
Re: Thanks - for help (using the sql in stored procedure - Error on 'MM') [message #579983 is a reply to message #579955] Mon, 18 March 2013 11:08 Go to previous messageGo to next message
slotankar
Messages: 5
Registered: March 2013
Location: MUMBAI
Junior Member
method 1:raises error for MM or multiset when compile
create or replace
procedure list_saturday(weekday in varchar2) as

type t_date is table of date;
v_date t_date;

type the_cursor is ref cursor;
my_cursor the_cursor;


begin

open my_cursor for 'select my_date from (SELECT TRUNC(SYSDATE,'MM')-1 + LEVEL my_date, to_char(TRUNC(SYSDATE,'MM')-1 + LEVEL,'Dy') my_day,level
FROM dual
CONNECT BY LEVEL <= last_day(SYSDATE)-TRUNC(SYSDATE,'MM') + 1) where my_day= :weekday ' using weekday;

Fetch my_cursor bulk collect into v_date;
for i in 1..v_date.count
loop
dbms_output.put_line(v_date(i));
end loop;
end;

method 2:Compiles successfully but raises invalid cursor at execute;

create or replace
procedure list_saturday1(weekday in varchar2) as

type t_date is table of date;
v_date t_date;

cursor c1 is SELECT TRUNC(SYSDATE,'MM')-1 + LEVEL my_date, to_char(TRUNC(SYSDATE,'MM')-1 + LEVEL,'Dy') my_day,level
FROM dual
CONNECT BY LEVEL <= last_day(SYSDATE)-TRUNC(SYSDATE,'MM') + 1 ;

begin

Fetch c1 bulk collect into v_date;
for i in 1..v_date.count
loop
dbms_output.put_line(v_date(i));
end loop;

end;
Re: Thanks - for help (using the sql in stored procedure - Error on 'MM') [message #579984 is a reply to message #579983] Mon, 18 March 2013 11:11 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
You missed the point. There is no need to create a cursor and loop through it. You can use the basics of what we have supplied to create a sql query that will return the resultset that you are asking for. No need for PL/SQL at all.

Also, please read the forum guide, paying special attention to the section that shows you how to use code tags.

[Updated on: Mon, 18 March 2013 11:18]

Report message to a moderator

Re: Thanks - for help (using the sql in stored procedure - Error on 'MM') [message #579985 is a reply to message #579983] Mon, 18 March 2013 11:12 Go to previous messageGo to next message
BlackSwan
Messages: 22520
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Thanks - for help (using the sql in stored procedure - Error on 'MM') [message #579988 is a reply to message #579985] Mon, 18 March 2013 11:35 Go to previous message
cookiemonster
Messages: 10848
Registered: September 2008
Location: Rainy Manchester
Senior Member
Method 1 doesn't compile becuase a quote ends a string.
open my_cursor for 'select my_date from (SELECT TRUNC(SYSDATE,'MM')-1 + LEVEL my_date, to_char(TRUNC(SYSDATE,'MM')-1 + LEVEL,'Dy') my_day,level
                   ^ start of string                          ^ end of string

In the above MM isn't in the string.
To get it in the string you need to double up the quotes in the string:
 open my_cursor for 'select my_date from (SELECT TRUNC(SYSDATE,''MM'')-1 + LEVEL my_date, to_char(TRUNC(SYSDATE,''MM'')-1 + LEVEL,''Dy'') my_day,level
  FROM dual
  CONNECT BY LEVEL <= last_day(SYSDATE)-TRUNC(SYSDATE,''MM'') + 1) where my_day= :weekday ' using weekday;


However using a ref cursor for this is silly.

Method 2 errors because you haven't opened the cursor.

Even fixing those errors won't get working code though as the select returns 3 columns and v_date only has 1.
Previous Topic: delete last n sets of rows of a table with same dates in sort field
Next Topic: Record Types
Goto Forum:
  


Current Time: Tue Jul 29 06:25:15 CDT 2014

Total time taken to generate the page: 0.11274 seconds