Return Associative Array from Oracle Function

Question

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.

What is the receiving part. php oci8 does that by default. Also you can use sys_refcursor or a user type like CREATE OR REPLACE TYPE CUST_OBJ AS OBJECT (/* list of variables eg. myid number, myval varchar2(255) etc. */); CREATE OR REPLACE TYPE OBJ_TBL IS TABLE OF CUST_OBJ;. Then using the oci, make the custom collection of OBJ_TBL, bind it, run query and fetch result. — Tala
– Tala, Commented Sep 5, 2015 at 17:28
What does the function do? What are some sample inputs to the function and your expected outputs? Have you tried returning SYS.ODCINUMBERLIST (and decaring D_DATE to be that type)? What does the SPLIT2 function do? — MT0
– MT0, Commented Sep 6, 2015 at 0:06
split2 is another function that return associative array in varchar2 — Muhammad Sarmad Mahmood Malik
– Muhammad Sarmad Mahmood Malik, Commented Sep 6, 2015 at 7:29
What does your function do? What goes in and what do you expect to come out? — MT0
– MT0, Commented Sep 6, 2015 at 14:28

MT0 · Accepted Answer · 2015-09-06 19:04:49Z

3

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 |

answered Sep 6, 2015 at 19:04

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Muhammad Sarmad Mahmood Malik Over a year ago

My own function work well as I got syntax from your answer as: select * from table( get_days('3:4','01-Sep-2015', '30-DEC-2015'))

Muhammad Sarmad Mahmood Malik Over a year ago

Also want to know I'm getting null values as have to extend array. How to get rid it.

MT0 Over a year ago

Ask a new question and give the function you've written, the input you are putting in to it and the expected output - you should get someone to help if you give all that information in the new question. Do not edit this question as it will destroy any value to people with similar issues and is unlikely to be seen by many people as it is an old (and answered) question.

Muhammad Sarmad Mahmood Malik Over a year ago

Having question limit.

Collectives™ on Stack Overflow

Return Associative Array from Oracle Function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related