CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlcursor
RicardoE
RicardoE

Reputation: 1725

PLSQL Procedure to return result set without a refcursor

So Im going trough an oracle database course and I have a homework where I have to create a procedure and "return" (I guess return) the resultset without using a refcursor, but all the examples I find make use of it.

So lets say I have this:

CREATE OR REPLACE PROCEDURE get_all_inventory() AS
BEGIN
  SELECT * FROM Inventory;
END;
/

How do I make the procedure return the resultset without using refcursor? is this even possible?

Thanks.

EDIT: If someone know a way of returning the result set in json that will be just awesome!

Upvotes: 0

Views: 1993

Answers (1)

Vance
Vance

Reputation: 897

Aside from using JSON, you can also use collections as a return value. You have to create a package first for your procedure. Here's a sample code:

  create OR REPLACE package get_all_inventory_package is
  type arrayofrec is table of Inventory%rowtype index by pls_integer;
  procedure get_all_inventory (o_return_variable OUT arrayofrec);
  end;
  /
  create OR REPLACE package BODY get_all_inventory_package is
  procedure get_all_inventory (o_return_variable OUT arrayofrec)is
  return_variable arrayofrec;
  begin
  select * bulk collect into o_return_variable from Inventory;
  end;
  END;
  /

  declare
  v_receiver get_all_inventory_package.arrayofrec;
  begin
  get_all_inventory_package.get_all_inventory(v_receiver);
  for a in 1..v_receiver.count loop
  dbms_output.put_line(v_receiver(a).Inventory_column);
  end loop;
  end;

Upvotes: 1

Related Questions