Oracle Stored Procedure: Returning a Multiple Rows

Question

In my stored procedure it needs to get inputs and return multiple rows to a front-end application.

However the stored Procedure produces an errors:

• SQL Statement ignored
• not enough values

How to resolve?

---

Object type

CREATE TYPE org_rspnsble_prsns_type 
    AS OBJECT (
    "appId" varchar2, 
    "orgId" varchar2, 
    "domainId" varchar2, 
    "leadName" varchar2, 
    "personId" number
);

Table type

CREATE TYPE org_rspnsble_prsns_table 
AS TABLE OF org_rspnsble_prsns_type;

Stored Procedure

CREATE OR REPLACE PROCEDURE GetNames( appIdInput IN varchar2, orgIdInput IN varchar2, p_arr OUT org_rspnsble_prsns_table )
AS
BEGIN
    SELECT "appId", "orgId", "domainId", "leadName", "personId"
    BULK COLLECT INTO p_arr
    FROM (
        select "appId", "orgId", "domainId", "leadName", "personId"
        from tableA
    UNION
        select "appId", "orgId", "domainId", "leadName", "personId"
        from tableB
    )
    WHERE "appId" = appIdInput
    AND "orgId" = orgIdInput;
END;

Answer 1

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.