Modifying PLSQL function to return multiple rows from same column

Question

I am a beginner PLSQL user, and I have what might be a rather simple question.

I have created the following SQL Function, which returns the created date of the process whose corporate ID matches the corporate ID that I have given it. I have this connected to my JDBC, and it returns values just fine.

However, I just realized I overlooked an important issue--it's entirely possible that more than one process will have a corporate ID that matches the ID value I've inputted, and in cases like that I will need to be able to access all of the created date values where the IDs return a match.

CREATE OR REPLACE FUNCTION FUNCTION_1(
    c_id IN INT)
  RETURN INT
AS
  p_date process.date_created%TYPE;
BEGIN
  SELECT process.date_created
  FROM PROCESS
  WHERE process.corporate_id = c_id
  ORDER BY process.corporate_id;
  RETURN p_id;
END FUNCTION_1;
/

Is there a way that I can modify my function to return multiple rows from the same column, and then call that function to return some sort of array using JDBC? Or, if that isn't possible, is there a way I can return what I need using PLSQL procedures or just plain SQL combined with JDBC? I've looked through other questions here, but none seemed to be about quite what I need to know.

Thanks to anyone who can help!

You are returning scalar type. You need to return a collection/ref cursor — Lalit Kumar B
– Lalit Kumar B, Commented Nov 21, 2015 at 8:28

are · Accepted Answer · 2015-11-21 10:24:29Z

1

you need make some changes in your function. on java side it will be simple select

you need change type of your function from int to collection read about the table functions here Table Functions
user oracle table() function to convert result of your function to table it let you use your function in queries. read more about the syntax here: Table Collections: Examples

here the example how to call your function from java:

select t.column_value as process_id 
      from  table(FUNCTION_1(1)) t

--result
    PROCESS_ID
1   1
2   2


--we need create new type - table of integers
CREATE OR REPLACE TYPE t_process_ids IS TABLE OF int;

--and make changes in function
CREATE OR REPLACE FUNCTION FUNCTION_1(
    c_id IN INT)
  RETURN t_process_ids
AS
  l_ids  t_process_ids := t_process_ids();
BEGIN
  --here I populated result of select into the local variables
  SELECT process.id
  bulk collect into l_ids
  FROM PROCESS
  WHERE process.corporate_id = c_id
  ORDER BY process.corporate_id;

  --return the local var
  return l_ids;
END FUNCTION_1;

--the script that I used for testing
create table process(id int, corporate_id int, date_created date);
insert into process values(1, 1, sysdate);
insert into process values(2, 1, sysdate);
insert into process values(3, 2, sysdate);

answered Nov 21, 2015 at 10:24

are

2,6153 gold badges24 silver badges27 bronze badges

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

1 Comment

Lamprey Over a year ago

Thank you! This is detailed and really helpful, and I appreciate all the info that you linked as well. This also solved a few other issues I was having in other parts of my project.

Collectives™ on Stack Overflow

Modifying PLSQL function to return multiple rows from same column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related