Discussions
Categories
- 5.3K All Categories
- 15 Introduce Yourself!
- 457 Community Feedback - NEW! (No Product Questions)
- 108 General Community Platform Concerns/Kudos/Feedback
- 83 Community Platform Bug Reports
- 87 How Do I Use the Community?
- 65 Where is the...? (Community Platform Locations)
- 18 Ideas and Suggestions for the Community Platform
- 4.8K Certification Community
- 4.7K Certification Community Discussions
- 22 Oracle Certified Master Profiles
- 31 Oracle Database 12c Administrator Certified Master Profiles
- 83 Visual Builder Cloud Service
temp table not returning data in WITH

User_4TYGX
Member Posts: 5 Green Ribbon
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.