Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dept (catno) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5;
5 rows affected

CREATE TABLE posts (catno) AS
SELECT LEVEL FROM DUAL WHERE LEVEL != 3 CONNECT BY LEVEL <= 4;
3 rows affected

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;
/
DECLARE
  v_cursor SYS_REFCURSOR;
  v_catno DEPT.CATNO%TYPE;
BEGIN
  empty_cats(v_cursor);
  LOOP
    FETCH v_cursor INTO v_catno;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_catno);
  END LOOP;
  CLOSE v_cursor;
END;
/
1 rows affected

dbms_output:
3
5

DROP PROCEDURE empty_cats;
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;
/
-- Not supported in DB<>FIDDLE
EXECUTE empty_cats;
ORA-00900: invalid SQL statement

DROP PROCEDURE empty_cats;
CREATE TYPE dept_obj AS OBJECT(
  catno NUMBER
);
CREATE TYPE dept_obj_table AS TABLE OF dept_obj;
CREATE FUNCTION empty_cats
RETURN dept_obj_table AS
  v_depts dept_obj_table;
BEGIN
  SELECT dept_obj(catno)
  BULK COLLECT INTO v_depts
  FROM   dept
  WHERE  CatNO NOT IN (SELECT CatNO FROM posts);
  
  RETURN v_depts;
END;
/
SELECT * FROM USER_ERRORS;
SELECT *
FROM   TABLE(empty_cats())


CATNO


3

5


DROP FUNCTION empty_cats;
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;
/
SELECT * FROM empty_cats()


CATNO


3

5