One Test on the Different Errors of Oracle Global Temporary Tables vs. Private Temporary Tables

This Blog shows one different behaviour of Oracle Global Temporary Table (GTT since Oracle 8i) vs. Private Temporary Table (PTT since Oracle 18c).

Note: Tested in Oracle 19.19.

1. GTT Test

We will make two GTT tests, one without "order by", one with "order by". Both throw the same ORA-01002.

Test-1 Without Order By

drop table gtt_tab;

create global temporary table gtt_tab(x number) on commit delete rows;

-- Implicit cursor for loops array fetch 100 rows at a time by default in 10g
insert into gtt_tab(x) select level from dual connect by level <= 103;  
    
set serveroutput on;

begin
  execute immediate '
    begin
      for c in (select x from gtt_tab) loop
        commit;
        dbms_output.put_line(c.x);  -- error in 101-th row
      end loop;
    end;';
end;
/

--The output shows ORA-01002 error:

100
begin
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 3

Test-2 With Order By

drop table gtt_tab;

create global temporary table gtt_tab(x number) on commit delete rows;

-- Implicit cursor for loops array fetch 100 rows at a time by default in 10g
insert into gtt_tab(x) select level from dual connect by level <= 103;  
    
set serveroutput on;

begin
  execute immediate '
    begin
      for c in (select x from gtt_tab order by x) loop
        commit;
        dbms_output.put_line(c.x);   -- error in 101-th row
      end loop;
    end;';
end;
/

--The output shows the same ORA-01002 error:

100
begin
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 3

2. PTT Test

We will make two PTT tests, one without "order by", one with "order by".
Without "order by", it throws ORA-08103.
With "order by", PL/SQL is successfully completed.

Test-1 Without Order By

drop table ora$ptt_tab;

create private temporary table ora$ptt_tab (x number) on commit drop definition;

-- Implicit cursor for loops array fetch 100 rows at a time by default in 10g
insert into ora$ptt_tab(x) select level from dual connect by level <= 103; 

set serveroutput on;

begin
  execute immediate '
    begin
      for c in (select x from ora$ptt_tab) loop
        commit;
        dbms_output.put_line(c.x);  -- error in 101-th row
      end loop;
    end;';
end;
/

--The output shows ORA-08103 error:

100
begin
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 3

Test-2 With Order By

drop table ora$ptt_tab;

create private temporary table ora$ptt_tab (x number) on commit drop definition;

-- Implicit cursor for loops array fetch 100 rows at a time by default in 10g
insert into ora$ptt_tab(x) select level from dual connect by level <= 103; 

set serveroutput on;

begin
  execute immediate '
    begin
      for c in (select x from ora$ptt_tab order by x) loop
        commit;
        dbms_output.put_line(c.x);
      end loop;
    end;';
end;
/

--The output shows successfully completed:

103

PL/SQL procedure successfully completed.

The behaviour can be varied following different Oracle releases.
(see Oracle MOS:
     Post Database Upgrade to 19c, Fetch from Cursor Based on Global Temporary Table Raises ORA-01002 (Doc ID 2890026.1))