CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqlprocedure
Jemru
Jemru

Reputation: 2119

Oracle Stored Procedure: Returning a Multiple Rows

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:

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;

Upvotes: 0

Views: 13777

Answers (2)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

Your SELECT select 5 columns into 1 variable. You have to either
A) create object in your select:

SELECT org_rspnsble_prsns_type("appId", "orgId", "domainId", "leadName", "personId")
BULK COLLECT INTO p_arr
...

or B) create the type as record:

CREATE TYPE org_rspnsble_prsns_type 
AS RECORD (...

Upvotes: 1

Jemru
Jemru

Reputation: 2119

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.

Upvotes: 2

Related Questions