PHP ORACLE GLOBAL TEMPORARY TABLE

Question

----- PHP --------

<?php
    $c = oci_new_connect("system", "1234", "127.0.0.1:1521/ORCL:POOLED");
    oci_execute(oci_parse($c,"CALL pr_test()"));
    $s = oci_parse($c, 'select * from GTT_TABLEs');
    oci_execute($s);
    oci_fetch_all($s, $res);
    var_dump($res);   
?>



--- Global Temporary Table ----    
CREATE GLOBAL TEMPORARY TABLE "SYSTEM"."GTT_TABLES" 
   (    "ID" NUMBER(10,0) NOT NULL ENABLE, 
    "FIRSTNAME" VARCHAR2(191 BYTE), 
    "LASTNAME" VARCHAR2(191 BYTE), 
    "EMAIL" VARCHAR2(191 BYTE), 
    "POINTS" VARCHAR2(191 BYTE), 
    "NOTES" VARCHAR2(191 BYTE), 
    "CREATED_AT" TIMESTAMP (6), 
    "UPDATED_AT" TIMESTAMP (6), 
    "AGE" NUMBER(10,0), 
    "JOB" VARCHAR2(191 BYTE), 
    "GENDER" VARCHAR2(191 BYTE), 
    "COUNTRY" VARCHAR2(191 BYTE), 
    "SALE_DATE" VARCHAR2(191 BYTE)
   ) ON COMMIT PRESERVE ROWS ;  



--- PROCEDURE -----            
create or replace PROCEDURE PR_TEST  AS
BEGIN
DELETE GTT_TABLED;  
INSERT INTO GTT_TABLES (SELECT * FROM DATATABLES);
COMMIT;
END;

Php(7.2.1)(persistent) and oracle (12c) Global temporary table Issue.Some Times Data Cannot Get Returns Empty Table.This Issue Only In Global Temporary Table.I Tried in Laravel,Codeigniter Same Issue and I config Oracle polling Again same Issue.

Answer 1

The data in a Global Temporary Table is only visible within a session. You're using a PHP Connection Pool (ORCL:POOLED) so there's a possibility that different calls will connect to different sessions. This would certainly explain why you have an intermittent error, which only affects querying GTTs.

This is a common problem with web applications (which are stateless) and databases (which are massively stateful).

As for fixing it, maybe you don't need connection pooling. But it seems more likely that you're better off not using GTTs. There are actually very few use cases where Global Temporary Tables are worth the overhead of populating and reading them. Just query the data when you need it.