- 3,701,858 Users
- 2,239,501 Discussions
- 7,835,584 Comments
temp table not returning data in WITH
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.