temp table not returning data in WITH [message #682419] |
Thu, 22 October 2020 21:04 |
|
radrodeo9
Messages: 5 Registered: October 2020
|
Junior Member |
|
|
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.
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 glob@l 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. I tried this one initially then took the xml route.
-- select get_cnt from dual ; -- this returns data.
[Updated on: Thu, 22 October 2020 23:56] by Moderator Report message to a moderator
|
|
|
Re: temp table not returning data in WITH [message #682428 is a reply to message #682419] |
Fri, 23 October 2020 03:28 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Are you sure that you can use autonomous transactions with subquery factoring? It fails in the simplest example I can invent:orclz>
orclz> with
2 procedure p1
3 as
4 pragma autonomous transaction
5 begin
6 null;
7 end;
8 select * from dual;
9 /
procedure p1
*
ERROR at line 2:
ORA-00905: missing keyword
orclz> ed
Wrote file afiedt.buf
1 with
2 procedure p1
3 as
4 --pragma autonomous transaction
5 begin
6 null;
7 end;
8* select * from dual
9 /
D
-
X
orclz>
|
|
|
|
|
|
Re: temp table not returning data in WITH [message #682435 is a reply to message #682434] |
Fri, 23 October 2020 15:22 |
|
radrodeo9
Messages: 5 Registered: October 2020
|
Junior Member |
|
|
hi, thanks for your time and response. provided below is what i am trying to do (create table and then run SQL). i expect to see the data when run the SQL/provided below.
. if the temp table is empty to start with in a session, it is NOT inserting any data. i was like, is there a way to make the <plsql_declaration> and the other to run in serial order and make sure all parts of the WITH clause run irrespective of their usage in select.
I tried ordered hint by running the function in a inline view , then joined it back to the temp table no luck there as well.
hope i am making sense.
create global temporary table t_tab (val varchar2(20)) on commit preserve rows;
with procedure load_gtt
is pragma autonomous_transaction;
begin
execute immediate q'[insert into t_tab values ('test')]';
commit;
end;
function get_cnt return number as pragma autonomous_transaction;
v_cnt number;
begin
load_gtt;
select count(*)
into v_cnt
from t_tab;
return v_cnt;
end;
select a.*, get_cnt from t_tab a
/
[Updated on: Fri, 23 October 2020 15:26] Report message to a moderator
|
|
|
Re: temp table not returning data in WITH [message #682436 is a reply to message #682435] |
Fri, 23 October 2020 15:32 |
|
Alien
Messages: 292 Registered: June 1999
|
Senior Member |
|
|
Hi,
well, your query doesn't make too much sense to me, but it was fun to see if it would work.
The reason that you need to 'jump-start' with a record in the GTT, is because Oracle will skip the function call once it realises that there is no data in the temp-table.
Your query asks: give me every row from the temp-table. And for each row show me the result from get_cnt.
When there are no rows in the temp-table, Oracle will skip the next step, since there will be no rows in the result.
Maybe there is a way of casting the function result into a table and joining it with the temp-table, that will return your results. But as you can see from my test, I doubt if the results will make any sense.
I think you will get better answers, when you show us what you try to achieve, with samples of the data and the results of the query.
Regards,
Arian
|
|
|
|
Re: temp table not returning data in WITH [message #682439 is a reply to message #682436] |
Fri, 23 October 2020 16:01 |
|
radrodeo9
Messages: 5 Registered: October 2020
|
Junior Member |
|
|
thank you so much again.
so , please check the comments
t = table ; t_tab = temp table
create global temporary table t_tab (val varchar2(20)) on commit preserve rows; -- this a nice table and the source changed it to a temp and when asked how to get that data.
they said run this sp and after it run a select (same select that you used to run earlier).
<before>
select * from t ;
<after>
call sp ('ip', 'ip'); -- this SP they are loading the temp table.
select * from t_tab ;
as there will be applications like reporting ones that can run only selects, trying to do it in one select.
[Updated on: Fri, 23 October 2020 16:01] Report message to a moderator
|
|
|
Re: temp table not returning data in WITH [message #682468 is a reply to message #682439] |
Sat, 24 October 2020 23:37 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Maybe I miss something obvious, but why do you not simply state the data in the subquery instead of inserting them to a temporary table?
with temp_data as (
--query providing same data as loaded in sp ('ip', 'ip')
select val
from ...
)
select val,
count(*) over () get_cnt -- if you explicitly want number of all rows in each row
from temp_data;
For avoiding typing the whole potentially complex query (is this the reason for calling stored procedure?), you may create a view:
create or replace view t_view as
--query providing same data as loaded in sp ('ip', 'ip')
/
select <view_columns>
from t_view;
|
|
|
|
Re: temp table not returning data in WITH [message #682500 is a reply to message #682487] |
Sun, 25 October 2020 22:29 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
radrodeo9 wrote on Sun, 25 October 2020 16:35aaah the SP loads to a GTT and we cant call SP and then select in a reporting/third party application. can do only selects.
the data is visible only in the session
Yeah, that's the correct approach: do not use that stored procedure at all, specify output data (currently inserted to temp table by stored procedure) directly in select query.
Create a view if the query is too complex or you do not want to state it in the report query.
|
|
|