0

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!

RicardoE
  • 1,665
  • 6
  • 24
  • 42
  • 1
    go through this https://docs.oracle.com/cd/E17781_01/appdev.112/e18751/procedures_plsql.htm#TDPNG60040. You will get a detail idea – Santhucool Feb 26 '16 at 04:24
  • 1
    It is possible to return as json. please refer this http://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array – Santhucool Feb 26 '16 at 04:26

1 Answers1

1

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;
Vance
  • 897
  • 5
  • 9