return table from oracle procedure

Question

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

Answer 1

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