Return multiple rows with a function 1

[kwil38]

I have a table defined as follows:

Row_id NUMBER
Email VARCHAR2(50)
Option_Name VARCHAR2(2000)
Option_Value_1 VARCHAR2(2000)
Option_Value_2 VARCHAR2(2000)
Last_Update_Date DATE

I need to create a function that will return all rows (there could be 1 or many) for a given email address.

I tried to create an associative array, but now realize I can't do that because all of the fields returned would have to be of the same datatype (if my understanding is correct).

Is there another option or a better way to accomplish the desired result?

Thanks in advance!

 

[Dagon]

You can declare whatever data types you want on the output record. Here's a simple example.

set serveroutput on

DROP TABLE MYTABLE;

CREATE TABLE MYTABLE
(Row_id           NUMBER,
Email            VARCHAR2(50),
Option_Name      VARCHAR2(2000),
Option_Value_1   VARCHAR2(2000),
Option_Value_2   VARCHAR2(2000),
Last_Update_Date DATE
)
/

insert into mytable values (1, 'xxx@xx.x', 'XOPT 1', 'OPT 2', 'OPT 3', sysdate);

insert into mytable values (2, 'yyyy@yy.y', 'YOPT 1', 'OPT 2', 'OPT 3', sysdate);



create or replace package retdata is
  type t_rows is table of mytable%rowtype;
  l_row t_rows;
  function get_res_set (p_email in varchar2) return t_rows;
end;
/

create or replace package body retdata is
   function get_res_set (p_email in varchar2) return t_rows is
      l_row t_rows;
   begin
      SELECT * BULK COLLECT into l_row from mytable;
      return l_row;
   end;
end;
/

declare
  l_row retdata.t_rows;
begin
  l_row := retdata.get_res_set('x');
  for i in l_row.first..l_row.last loop
    dbms_output.put_line('Option Name '||i||' - '||l_row(i).option_name);
  end loop;
end;
/

 

[kwil38]

Dagon -

Thanks, that works perfect!

 

[SantaMufasa]

Thanks, that works perfect!

I totally agree, Kwil38...The replies/solutions that Dagon posts are always stellar, including the one in this thread.


I am surprised that in the past 4+ years that you have been a member of Tek-Tips, you have posted requests for help 20 times, then received help, but you have not yet noticed that in addition to a verbal "Thank you" in your closing reply, we also click the button that reads:

Thank <Helper name> for this valuable post!

...in the lower left corner of the solution-poster's reply.

s are the "currency" that we use here on Tek-Tips instead of your having to use a Debit/Credit card to actually pay for the excellent solutions that appear here.

Subsequent Tek-Tips readers that have needs similar to yours can use the

s to direct their attention to quality solutions.

So, please Kwil38, take advantage of the

Button...click it for Dagon's excellent solution, then use it also when others are similarly helpful.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kwil38]

Santa,

My apologies, I had not noticed that feature but just utilized it for Dagon and also will in future posts.

kwil38

 

[SantaMufasa]

Well done, Kwil! Thanks for your response.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”