0

How do I return the table of my procedure whenever I call it?

-- this code will find all the empty categories.

create or replace procedure empty_cats(emptyCat OUT DEPT%rowtype) as
begin
  select * into emptyCat from DEPT where CatNO not in (select CatNO from posts);
end;

I'm using oracle 11 XE & Application Express 4.0.2.00.09

Christian
  • 53
  • 1
  • 8
  • 1
    You need to use ref cursor. There are plenty of examples available. For one you can refer - https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets – Pankaj May 14 '22 at 13:40
  • Please provide a version of your Oracle database and describe why a simple view is not sufficient for this task – astentx May 14 '22 at 15:36
  • Does this answer your question? [How to return multiple rows from the stored procedure? (Oracle PL/SQL)](https://stackoverflow.com/questions/101033/how-to-return-multiple-rows-from-the-stored-procedure-oracle-pl-sql) – Matthew McPeak May 14 '22 at 15:36

1 Answers1

2

Option 1: A Cursor

CREATE PROCEDURE empty_cats(
  o_cursor OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN o_cursor FOR
    SELECT *
    FROM   dept
    WHERE  CatNO NOT IN (SELECT CatNO FROM posts);
END;
/

Option 2: DBMS_SQL.RETURN_RESULT (From Oracle 12.1)

CREATE PROCEDURE empty_cats
AS
  v_cursor SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR
    SELECT *
    FROM   dept
    WHERE  CatNO NOT IN (SELECT CatNO FROM posts);
  DBMS_SQL.RETURN_RESULT(v_cursor);
END;
/

Option 3: A Collection

Create an object and a collection type:

CREATE TYPE dept_obj AS OBJECT(
  col1 NUMBER,
  col2 VARCHAR2(20),
  col3 DATE
);

CREATE TYPE dept_obj_table AS TABLE OF dept_obj;

Then:

CREATE PROCEDURE empty_cats(
  o_depts OUT dept_obj_table
) AS
BEGIN
  SELECT dept_obj(col1, col2, col3)
  BULK COLLECT INTO o_depts
  FROM   dept
  WHERE  CatNO NOT IN (SELECT CatNO FROM posts);
END;
/

or

CREATE FUNCTION empty_cats
RETURN dept_obj_table AS
  v_depts dept_obj_table;
BEGIN
  SELECT dept_obj(col1, col2, col3)
  BULK COLLECT INTO v_depts
  FROM   dept
  WHERE  CatNO NOT IN (SELECT CatNO FROM posts);
  
  RETURN v_depts;
END;
/

Option 4: SQL_MACRO(TABLE) (From Oracle 19.7)

CREATE FUNCTION empty_cats
  RETURN VARCHAR2 SQL_MACRO(TABLE) IS
BEGIN 
  RETURN q'{SELECT * FROM dept WHERE  CatNO NOT IN (SELECT CatNO FROM posts)}'; 
END;
/

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Option 1 only return Procedure created. Option 2 isn't working it give this error Error at line 8: PLS-00302: component 'RETURN_RESULT' must be declared – Christian May 14 '22 at 18:57
  • @Christian Of course running the `CREATE PROCEDURE` won't return anything; you need to call the procedure once you have created it. `DBMS_SQL.RETURN_RESULT` was introduced in Oracle 12r1; if you are on an earlier version then it will not be supported (but you did not tag any particular version in the question and Oracle is up to version 21c now so anything before Oracle 12 is quite old). – MT0 May 14 '22 at 19:20
  • @MTO how do I call the PROCEDURE? – Christian May 14 '22 at 19:22
  • @MTO I've updated my question to include the version. – Christian May 14 '22 at 19:24
  • 1
    And for 19.7 and above - `SQL_MACRO(table)`. +1 for DBMS_SQL.RETURN_RESULT as it's quite common behavior for other DBMS – astentx May 14 '22 at 19:46
  • @Christian Added a DB<>FIDDLE with examples – MT0 May 14 '22 at 20:23