Home » SQL & PL/SQL » SQL & PL/SQL » temp table not returning data in WITH (18.10)
temp table not returning data in WITH [message #682419] Thu, 22 October 2020 21:04 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8427
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 #682429 is a reply to message #682428] Fri, 23 October 2020 05:06 Go to previous messageGo to next message
radrodeo9
Messages: 5
Registered: October 2020
Junior Member
yeah. please see below, should be "PRAGMA AUTONOMOUS_TRANSACTION; ", missing "underscore" and "semi colon"
[code]
with
procedure p1
AS PRAGMA AUTONOMOUS_TRANSACTION;
begin
null;
end;
select * from dual;
/
[code/]
Re: temp table not returning data in WITH [message #682430 is a reply to message #682429] Fri, 23 October 2020 05:17 Go to previous messageGo to next message
John Watson
Messages: 8427
Registered: January 2010
Location: Global Village
Senior Member
Ah, silly of me. Ta.
Re: temp table not returning data in WITH [message #682434 is a reply to message #682430] Fri, 23 October 2020 13:48 Go to previous messageGo to next message
Alien
Messages: 282
Registered: June 1999
Senior Member
SQL> create global temporary table t_tab (val varchar2(20)) on commit preserve rows;

Table created.

SQL> 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
/ 

no rows selected

SQL> /

no rows selected

SQL> insert into t_tab values ('start');

1 row created.

SQL> 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
/

VAL                     GET_CNT
-------------------- ----------
start                         1

SQL> /

VAL                     GET_CNT
-------------------- ----------
start                         2
test                          3

SQL>
Regards,

Arian
Re: temp table not returning data in WITH [message #682435 is a reply to message #682434] Fri, 23 October 2020 15:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Alien
Messages: 282
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 #682437 is a reply to message #682436] Fri, 23 October 2020 15:37 Go to previous messageGo to next message
Alien
Messages: 282
Registered: June 1999
Senior Member
P.S. I think your problem is, that you are forgetting Oracle's data-consistency. I suggest you write out the data sequence in your query, and see if it can yield the result that you want.

Regards,

Arian
Re: temp table not returning data in WITH [message #682439 is a reply to message #682436] Fri, 23 October 2020 16:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1895
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 #682487 is a reply to message #682468] Sun, 25 October 2020 10:35 Go to previous messageGo to next message
radrodeo9
Messages: 5
Registered: October 2020
Junior Member
aaah 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
Re: temp table not returning data in WITH [message #682500 is a reply to message #682487] Sun, 25 October 2020 22:29 Go to previous message
flyboy
Messages: 1895
Registered: November 2006
Senior Member
radrodeo9 wrote on Sun, 25 October 2020 16:35
aaah 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.
Previous Topic: Get Distinct Data
Next Topic: SQL to retrieve lines based on a static value
Goto Forum:
  


Current Time: Sun Nov 29 19:36:16 CST 2020