temp table not returning data in WITH

User_4TYGX

I am trying to call the inline function/stored proc which populates a gtt and a sql that uses that gtt, trying to do all in one sql statement (call SP & select part that uses the gtt). i don’t get any error or do see any data. the gtt has “on commit preserve rows” , sp does commit it after loading it, i also did as part of sql ( below).

is there a way to get the proc execute prior to the sql call or any anyway to make sure all the sections of the with clause run in specific order. ver 18.10

WITH  PROCEDURE call_sp_that_load_gtt 
     AS PRAGMA AUTONOMOUS_TRANSACTION; 
     v_sql   VARCHAR2 (1000); 
     BEGIN
         v_sql := q'[ BEGIN ( 'param1', sysdate-3   ) ; END; ]' ; ---<<< this SP call loads to GTT
            EXECUTE IMMEDIATE v_sql ;
commit;
    END; 
     
     FUNCTION get_cnt  RETURN NUMBER  AS pragma autonomous_transaction ;  v_cnt   NUMBER; 
     BEGIN
                call_sp_that_load_gtt ; ---<<< this SP call loads to [email protected] temp table
        COMMIT  ;
      SELECT count(*)
          INTO v_cnt
          FROM  ;
 

        RETURN v_cnt;
     END;
 select a.* , get_cnt() from <temp table> a ; --- no data if we run this on 1st run or any run. 
--  select get_cnt from dual ; -- this returns data.