database - Save and return multiple rows within function pl/sql oracle

Question

declare
    type t_trayIds is table of number(38,0) index by binary_integer;
    v_trayIdsTable t_trayIds;
create or replace function F_getTrayIdByDiameter(v_diameterInCm tray.diameterincm%TYPE)   
return t_trayIds
as 
    v_trayIdsTable t_trayIds := null;
begin
    select t.trayid into v_trayIds from tray t 
    where t.diameterincm = v_diameterincm;
    return v_trayIdsTable;
end;

So what I want is, to ask for all Tray IDs with a specific Diameter and store them in an Array or Table. In Java I used ArrayList. I want to return the Table in the end to pass the result onto another function. The above code doesn't seem to work. SQL Developer gives me a syntax error at the word create. Can someone help?

question from:https://stackoverflow.com/questions/65865240/save-and-return-multiple-rows-within-function-pl-sql-oracle

Answer 1

Your code fails because you are mixing a declare section that must be followed by a begin section, with a "create or replace function" that is a standalone statement to create objects;

If you want to declare a PL/SQL table type and make it public, you must put it in a package specification, so it can be visible by any function (I also declare here the function F_getTrayIdByDiameter, to make it visible):

    CREATE OR REPLACE package utils is
        type t_trayIds is table of number(38,0) index by binary_integer;
        function F_getTrayIdByDiameter(v_diameterInCm tray.diameterincm%TYPE) return t_trayIds;
    end utils;
    /

besides, you can't use SELECT INTO syntax, because

select col into var

can be used only for single row, not for lists; in PL/SQL, if you want to manage multiple rows, you have to use a cursor; so, if you want to create your PL/SQL table, you can fetch your cursor and build your list (PL/SQL table); so, your package body can be,

CREATE OR REPLACE package body utils is
function F_getTrayIdByDiameter(v_diameterInCm tray.diameterincm%TYPE) return t_trayIds is 
    v_trayIdsTable t_trayIds;
    i number := 0;
    cursor c is 
    select t.trayid from tray t 
    where t.diameterincm = v_diameterincm;
begin
    for my_rec in c loop
        v_trayIdsTable(i) := my_rec.trayid;
        i := i + 1;
    end loop;
        
    return v_trayIdsTable;
end;
end utils;
/

Then, you can use your list in another function, or in an anonymous block, just for example:

declare
  my_result utils.t_trayIds;
begin
  my_result := utils.F_GETTRAYIDBYDIAMETER(20);
  dbms_output.put_line(my_result(0));
end;