CODEWITHSUNDEEP
oracle-database
Muhammad Sarmad Mahmood Malik
Muhammad Sarmad Mahmood Malik

Reputation: 437

Return Associative Array from Oracle Function

I would like to know what data type should be in function return when a return value is an associative array in the following code:

create or replace FUNCTION GET_DAYS 
(
  DAY_IDS IN VARCHAR2 ,
  FromDate IN Date,
  Todate IN Date
) RETURN split_tbl /*SYS.ODCINUMBERLIST*/ AS 
BEGIN
DECLARE
D_LIST split_tbl;
TYPE weekdays IS TABLE OF number INDEX BY PLS_INTEGER;
D_Date split_tbl:=split_tbl();
j number:=1;
m number:=1;
K NUMBER:=1;
weeks number:=1;
t_weeks number:=0;
u number;
wday char(3);
f_date date:=fromdate;
BEGIN

D_LIST := SPLIT2(DAY_IDS);

WHILE j <= D_LIST.count loop
 u:=nvl(d_list(j),0);

select count(*) into m from days where (day_id)=u;
if m=1 then
select day into wday from days where (day_id)=u;

  t_weeks:=(next_day(To_date(todate,'DD-Mon-RRRR'),wday)-next_day(To_date(f_date,'DD-Mon-RRRR'),wday))/7;
   while (next_day(To_date(todate,'DD-Mon-RRRR'),wday)-next_day(To_date(f_date,'DD-Mon-RRRR'),wday))/7 >=1 
 loop
d_date.extend(nvl(t_weeks,0));
 D_DATE(K):=to_char(weeks);
f_date:=f_date+7;
 weeks:=weeks+1;
 K:=K+1;
 end loop; 

  end if; 
    j:=j+1;
   END loop;
K:=0;
/*
while k<=d_date.count loop
d_date.extend(2000);

if(d_date(K)=null) then

d_date.delete(K);
end if;
end loop;
*/
  RETURN D_Date;
  END;
END GET_DAYS;

I am already using an associative array D_List that has been created of varchar2 but I need it in number.

Upvotes: 1

Views: 1368

Answers (1)

MT0
MT0

Reputation: 167962

I asked several times "What does your function do?" and the only answer was:

I just want to return array that containing weeks 1,2,3

So here is a function that returns an array containing 1,2,3

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE FUNCTION get_days
RETURN SYS.ODCINUMBERLIST
AS
  days SYS.ODCINUMBERLIST;
BEGIN
  days := SYS.ODCINUMBERLIST( 1, 2, 3 );
  RETURN days;
END;
/

Query 1:

SELECT *
FROM   TABLE( get_days )

Results:

| COLUMN_VALUE |
|--------------|
|            1 |
|            2 |
|            3 |

Upvotes: 3

Related Questions