Using SYS_REFCURSOR values in stored procedures in Oracle Database 19c

Question

I am in the process of revising a collection of old ETL scripts. The scripts make frequent use of table CREATE/DROP for holding arrays for use in filters and joins. Currently the script runs 1.5 hours+, so I'm also looking for performance gains, where possible. The data are generally 1-10 million rows, sometimes on unmaterialized views, sometimes tables.

My approach so far has been to try to consolidate individual queries into stored procedures within a package, both for organization and to facilitate code review by keeping things in logical chunks and pointing to different procedures using SYS_REFCURSOR.

The below example mirrors the logic of the queries I am working with. I am not expecting a working code answer from this. The relevant error occurs during compilation: PL/SQL: ORA-00942: table or view does not exist, which I understand. I'm referencing the name of a procedure, not a table, in this mock-up.

My question is in two parts:

1. Is my approach here even sensible? If not, what is the best practice way to deal with temporarily required tables of values to be referenced by stored procedures like this?

2. In general, how does one use/reference the SYS_REFCURSOR table returned by a procedure like this? I couldn't work out how to use/access the results of toy procedures that is SYS_REFCURSOR as the OUT.

CREATE OR REPLACE PACKAGE "DAILY_ETL" AS
  PROCEDURE GET_TMP_A (TMP_A OUT SYS_REFCURSOR);
END DAILY_ETL;
/

CREATE OR REPLACE PACKAGE BODY "DAILY_ETL" AS

  PROCEDURE GET_TMP_A (TMP_A OUT SYS_REFCURSOR) AS
    BEGIN
      OPEN TMP_A FOR
        SELECT *
        FROM (
            WITH
              NESTED_TMP_A AS
                (SELECT 
                  THING_A
                FROM RAW_DATA
                ),
              NESTED_TMP_B AS
                (
                SELECT 
                  THING_B
                FROM THING_A
                WHERE BUSINESS_RULES_APPLIED
                )
            SELECT DISTINCT 
              * AS THING_I_WANT_FOR_TMP_A 
            FROM RAW_TABLE
                INNER JOIN NESTED_TMP_B ON 
                  ID = ID
            UNION
            SELECT DISTINCT
              THING_I_WANT_FOR_TMP_A
            FROM NESTED_TMP_A
            WHERE BUSINESS_RULES_APPLIED);
    END GET_TMP_A;

  PROCEDURE GET_TMP_B (TMP_B OUT SYS_REFCURSOR) AS
    BEGIN
      OPEN TMP_B FOR
      SELECT DISTINCT 
        COLUMN_A,
        COLUMN_B
      FROM ROOT_TABLE
      INNER JOIN (GET_TMP_A) AS TMP_A ON
        ROOT_TABLE.ID = TMP_A.ID;
    END GET_TMP_B;
END DAILY_ETL;
/