want to return set of dates from a procedure

Question

I have written a procedure like following

CREATE OR REPLACE PROCEDURE LeaveDates (STDATE    IN OUT DATE,
                                        ENDDATE   IN OUT DATE)
AS
    start_date   DATE := STDATE;       --to_date('01-JAN-2016','DD-MON-YYYY');
    end_date     DATE := ENDDATE;      --to_date('05-JAN-2016','DD-MON-YYYY');

    TYPE dates IS VARRAY (30) OF VARCHAR2 (50);

    alldates     dates;
BEGIN
    LOOP
        IF start_date <= end_date
        THEN
            DBMS_OUTPUT.put_line (start_date);
            start_date := start_date + 1;
        ELSE
            EXIT;
        END IF;
    END LOOP;
END LeaveDates;

It prints what I need. How do I return the dates which are printed from above procedure.

can i do something like this. But it only prints one time, second print statement not printing anything,

create or replace PROCEDURE LeaveDates2 
(
  STDATE IN OUT DATE 
, ENDDATE IN OUT DATE 
, alldate OUT SYS_REFCURSOR 
) AS 

start_date date := STDATE ;--to_date('01-JAN-2016','DD-MON-YYYY');
end_date   date := ENDDATE;--to_date('05-JAN-2016','DD-MON-YYYY');
i number:=1;
TYPE dates IS VARRAY(30) OF varchar2(50);
alldates dates; 
Begin
alldates := dates();
alldates.extend(30);
loop
if (start_date <= end_date) then
dbms_output.put_line(start_date);
dbms_output.put_line(alldates(i));
alldates(i):=start_date;
   i:=i+1;
   start_date := start_date +1;
   dbms_output.put_line(alldates(i));
else
   exit;
end if;
end loop;

END LeaveDates2;

Barbaros Özhan · Answer 1 · 2018-05-30T11:47:55.647

0

Just add two assignments

STDATE  := start_date;
ENDDATE := end_date;

to the end :

create or replace PROCEDURE LeaveDates 
(
  STDATE IN OUT DATE 
, ENDDATE IN OUT DATE 
) AS 

start_date date := STDATE;
end_date   date := ENDDATE;
TYPE dates IS VARRAY(30) OF varchar2(50);
alldates dates;
Begin
loop
if start_date <= end_date then
dbms_output.put_line(start_date);
   start_date := start_date +1;
else
   exit; 
end if;
end loop;
 STDATE  := start_date;
 ENDDATE := end_date;
END LeaveDates;

And Call as the following :

declare
  v_stdate  date := to_date('01-JAN-2016','DD-MON-YYYY');
  v_enddate date := to_date('05-JAN-2016','DD-MON-YYYY');
begin
  leavedates(stdate => v_stdate, enddate => v_enddate);
end;

edited May 30 '18 at 11:47

answered May 30 '18 at 11:41

Barbaros Özhan

59,113
10
31
55

if i make both as in out i will 2 sets of dates.. but i want only one set. and i will call this procedure from java. – Shilpa M B May 31 '18 at 04:46
If i make those as out it will give only 2 dates. but i want set of dates. Then i need make an array type as OUT parameter right. – Shilpa M B May 31 '18 at 04:58

score 0 · Accepted Answer · answered May 30 '18 at 13:40

0

You need an OUT parameter for your procedure and you need to define your type outside the procedure and initialise it inside the procedure:

CREATE TYPE DateArray IS VARRAY(30) OF DATE;

CREATE PROCEDURE LeaveDates 
(
  I_STDATE  IN  DATE,
  I_ENDDATE IN  DATE,
  O_DATES   OUT DateArray
)
AS 
  n INTEGER := LEAST( I_ENDDATE - I_STDATE, 29 );
BEGIN
  O_DATES := DateArray();
  O_DATES.EXTEND( n + 1 );
  FOR i IN 0 .. n LOOP
    O_DATES(i+1) := I_STDATE + i;
  END LOOP;
END LeaveDates;

answered May 30 '18 at 13:40

MT0

143,790
11
59
117

but i will call this from a java class. where shoukd i write this code "CREATE TYPE DateArray IS VARRAY(30) OF DATE;" – Shilpa M B May 31 '18 at 04:43
Create the type in the database. Create the procedure in the database. This is [an example](https://stackoverflow.com/a/37161584/1509264) of passing a collection to a procedure using Java - just adapt it to change the `IN` parameter to an `OUT` parameter (I cannot immediately find an example using dates or out parameters of a stored procedure to give a more apt solution). – MT0 May 31 '18 at 08:14

want to return set of dates from a procedure

2 Answers2