How to Return Multiple Rows from the Stored Procedure? (Oracle Pl/Sql)

Return multiple rows from procedure , select statement PL/SQL

As mentioned by @Muhammad Muazzam, since the query is returning multiple rows you need either a collection to hold the records at a single go or you can loop through the select and hold the records in the variables you declared. I show you how you can do it using a RECORD.

create or replace procedure payx (pid   number) 
is
   --Created a record by bundling all your single defined variables  
     type xx is RECORD
     (
         vardistrict                   parking.district_id%type,
         vid                           parking.parcel_id%type,
         vqed                          parking.parcel_id%type,
         varrel                        varchar2 (20);

     );

     type var is table of xx index by pls_integer;

     var1 var;       

begin
     select target.district_id,
            target.parcel_id,
            query.parcel_id,
            sdo_geom.relate (target.geom,
                             'determine',
                             query.geom,
                             0.05
                            ) relationship
       bulk collect into var1
       from parking target,
            parking query
      where query.parcel_id = pid
        and sdo_relate (target.geom,
                        query.geom,
                        'mask=TOUCH'
                       ) = 'TRUE' ;

      for i in 1..var1.count
      loop                       
       dbms_output.put_line (   var1 (i).vardistrict
                            || var1 (i).vid
                            || var1 (i).vqed
                            || var1 (i).varrel);

      end loop;
end payx;

Return multiple rows in a plqsl stored procedure in output parameters

The stored procedure only gives the parameters marked as out in the declaration. What you are effectively doing is opening the cursor, looping through it, assigning to these parameters each time but only when the stored procedure has ended are these parameters passed out.

What you'd want to do is to just put the cursor as a paremeter:

create or replace PROCEDURE ANAGRAFICA_GET (
    PASSED_CURSOR OUT SYS_REFCURSOR,
) AS
BEGIN
        OPEN PASSED_CURSOR FOR
            SELECT X, Y, Z FROM EXAMPLE_TABLE;
END

How to return multiple rows from oracle stored procedure from multiple cursors?

My suggestion is going to be insert the rows from your cursor into a temporary table. Then join the temporary table with your existing table for the filter criteria you mention. Psuedocode:

create or replace function my_func
return sysrefcursor
is
    cursor cursor_one is 
        SELECT * FROM table_one ; 

    cursor cursor_two is 
        SELECT * FROM table_one ; 
    BEGIN    

     FOR current_row in cursor_one
      loop 

          -- do some modification on each row and insert into temporary table

      end loop; 



     FOR current_row in cursor_two
      loop 

          -- do some modification on each row and insert into temporary table

      end loop; 


    -- results from cursor 1 and 2 exist in temporary table

    open out_cursor for
     select t.* from
      my_temp_table t
      join
      my_other_table tt
      on (t.col1 = tt.col1) -- or whatever columns are appropriate
      where t.col2 = 'some criteria' -- or whatever filter criteria you like.

    return out_cursor;

    END;  

Oracle Stored Procedure: Returning a Multiple Rows

I found a good way to do it from:
https://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=6551171813078805685

create or replace package types 
as 
    type cursorType is ref cursor; 
end; 
/
create or replace function sp_ListEmp return types.cursortype 
as 
    l_cursor    types.cursorType; 
begin 
    open l_cursor for select ename, empno from emp order by ename;
    return l_cursor; 
end; 
/
create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
open p_cursor for select ename, empno from emp order by ename;
end;
/

Thank you to all who answered.

Save and return multiple rows within function pl/sql oracle

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;

How to return multiple rows with multiple columns using cursor in pl/sql procedure?

You may use a single REFCURSOR out parameter instead of multiple out parameters.

CREATE OR REPLACE PROCEDURE display_users (
    pi_date1          IN DATE,
    pi_date2          IN DATE,
    po_userdisp_cur   OUT SYS_REFCURSOR 
)
    IS
BEGIN
    OPEN po_userdisp_cur FOR SELECT u.enrollno,
                                    u.error_code,
                                    u.enroll_date
                             FROM user_table u
                             WHERE u.enroll_date BETWEEN pi_date1 AND pi_date2;
END;

This can be easily used in java to fetch the records as shown in this link:

Using oracle ref cursors in java

---

Related Topics