GTT et PS (Globale Temporary Table et Stored Procedure))

Version imprimable

03/01/2014, 19h14
Rhodo33

GTT et PS (Globale Temporary Table et Stored Procedure))

Bonjour,

J'ai créé une GTT avec l'option "On Commit preserve rows".
Je remplis cette GTT par une SP et cette GTT remplit sa fonction en gardant en mémoire les data le temps de la SP.

Mais une fois sortie de la SP, cette GTT est vide, malgré le fait que ma session n'est pas achevée et malgré l'option "On Commit preserve rows".

Peut-on garder cette GTT avec ses lignes une fois sortie de la SP ?
(Sinon je crée une table normale...)

Hello

Comment execute-tu ta SP? Avec quel client?

Currieux quand même:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
 
CREATE GLOBAL TEMPORARY TABLE tst 
(
 OWNER                                              VARCHAR2(30),
 OBJECT_NAME                                        VARCHAR2(128),
 SUBOBJECT_NAME                                     VARCHAR2(30),
 OBJECT_ID                                          NUMBER,
 DATA_OBJECT_ID                                     NUMBER,
 OBJECT_TYPE                                        VARCHAR2(19),
 CREATED                                            DATE,
 LAST_DDL_TIME                                      DATE,
 TIMESTAMP                                          VARCHAR2(19),
 STATUS                                             VARCHAR2(7),
 TEMPORARY                                          VARCHAR2(1),
 GENERATED                                          VARCHAR2(1),
 SECONDARY                                          VARCHAR2(1),
 NAMESPACE                                          NUMBER,
 EDITION_NAME                                       VARCHAR2(30)
) ON COMMIT PRESERVE ROWS;
 
create or replace procedure testload as
begin
 
insert into tst select * from dba_objects;
commit;
end;
/
 
 
Select count (*) from tst;
 
SQL> select count (*) from tst;
 
  COUNT(*)
----------
         0
 
SQL> exec testload;
 
PL/SQL procedure successfully completed.
 
SQL> select count (*) from tst;
 
 
  COUNT(*)
----------
     75214

Jko

Je travaille sous SQL Developer.
J'ai créer ma GTT ainsi :

Code:

1
2
3
4
 
CREATE GLOBAL TEMPORARY TABLE "xxxx"."PG_TEMP_TOTO" 
   (	"ID_ST" NUMBER(3,0)
   ) ON COMMIT PRESERVE ROWS ;

Voici ma SP

Code:

1
2
3
4
5
6
7
 
CREATE OR REPLACE PROCEDURE PG_PS_TOTO AS 
BEGIN
  execute immediate 'truncate table "LTEC"."PG_TEMP_TOTO"';
  insert into "LTEC"."PG_TEMP_TOTO"  VALUES (13);
  COMMIT;
END PG_PS_TOTO;

J'ai l'impression que deux GTT existent sous le même nom, hors et dans la SP !8O

03/01/2014, 22h06
FSiebert

Bonjour,

Pourquoi ce ""xxxx"."PG_TEMP_TOTO"" ? Les "xxxx" correspondent bien à "LTEC" ?
03/01/2014, 22h09
Rhodo33

oui

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
SQL> CREATE GLOBAL TEMPORARY TABLE PG_TEMP_TOTO
   (    ID_ST NUMBER(3,0)
   ) ON COMMIT PRESERVE ROWS ;
  2    3  
Table created.
 
SQL> CREATE OR REPLACE PROCEDURE PG_PS_TOTO AS 
BEGIN
  execute immediate 'truncate table PG_TEMP_TOTO';
  INSERT INTO PG_TEMP_TOTO VALUES (13);
  COMMIT;
END PG_PS_TOTO;
/
  2    3    4    5    6    7  
Procedure created.
 
SQL> exec PG_PS_TOTO;
 
PL/SQL procedure successfully completed.
 
SQL> select count (*) from PG_TEMP_TOTO;
 
  COUNT(*)
----------
         1
 
SQL>

Cela fonctionne en 11.2.0.3 x64 sous linux.

Vous êtes en quelle version?

jko

04/01/2014, 15h35
Rhodo33

SQL Developer est en version 4.0.0.13 et Oracle en 11.

Je résume (un peu vite car je n'ai pas les connaissances pour vérifier ce que je dis) : sous SQL Developer j'ai deux tables temporaires qui ont le même nom l'un est directement visible et l'autre n'existe que sous SP.
Si je remplis la GTT sous une requête normale j'ai un contenu. Sous SP : un autre !
D'un autre côté je n'ai créé la table qu'une fois et elle est globale.
04/01/2014, 16h13
jkofr

La SP est sous quel utilisateur?

Un autre ou LTEC?

Ps: il est impossible d'avoir deux tables avec le même nom.

Jko
04/01/2014, 17h17
Rhodo33
LTEC.

J'ai l'impression que SQL Developer crée plusieurs sessions. D'ailleurs chaque SP m'indique (par exemple) :
Code:

1 2 3 4 5 Connexion à la base de données ltec. Hello! Bye! Processus fermé. Déconnexion de la base de données ltec.
:whistle:

Que ce passerait-il si deux utilisateurs sous le noms de LTEC travaille en même temps sur une GTT ?

SQL Developer peut-il créer plusieurs sessions ?

(J'ouvre un autre thread, car j'ai l'impression que les Materialized views sont une solution de contournement pour ce que je dois faire.)
04/01/2014, 17h46
pachot

Bonjour,
Il faudrait donner des infos plus précises.
Une procédure stockée ne peut pas se connecter ou se deconnecter. Elle ne peut s'exécuter que dans une session déjà établie.
Cordialement,
Franck.
04/01/2014, 17h48
jkofr

Vouis,
Tout cela me semble confus!
Jko
04/01/2014, 18h34
Rhodo33
Je veux bien être plus précis.
1. Si je lance mes requêtes SQL ma GTT répond normalement ;
2. Idem si je lance ma SP.
Mais les contenus des GTT peuvent diffèrer. Pour arriver à cela :
1. Sous SQL créer la GTT, puis la remplir
2. Sous SP la vider, puis la remplir différement.
3. Revenir sous SQL : on a toujours le contenu de l'étape n°1 et non celui de l'étape 2.
Avez-vous SQL Developer ?
04/01/2014, 18h54
jkofr

Hello, voici ce que je comprends:

Session 1: avec sqldevelopeur créer la GTT, puis la remplir
Session 2: avec SP la vider, puis la remplir différemment.
Retour Session 1:on a toujours le contenu de l'étape n°1 et non celui de l'étape 2.

Et bien c'est normal!
Oui j'ai SQLDeveloper 4
Je suis curieux d'avoir la démarche step by step...
Merci

jko
04/01/2014, 19h26
Rhodo33

"Normal" : Aïe ! j'aurais dû m'en douter.

"Step by step" : pas sûr que je puisse aider. En quoi cela consiste-t-il ?
04/01/2014, 19h47
jkofr

Heu

Etape par etape...

Jko
04/01/2014, 20h21
Rhodo33

Hum ! Ce doit être ça.:ccool:
Peut-on voir ça sous SQL Developer ?
04/01/2014, 20h22
jkofr

Je t'écoute...
jko
04/01/2014, 20h43
Rhodo33

Pardon ! Je voulais dire : "comment voit-on cela sous SqlDeveloper ?"

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
 
-- Suppression de la table
drop table sh.PG_TEMP_TOTO;
 
-- Création de la table
CREATE GLOBAL TEMPORARY TABLE sh.PG_TEMP_TOTO
   (    ID_ST NUMBER(3,0)
   ) ON COMMIT PRESERVE ROWS ;
 
 
-- Création de la SP
CREATE OR REPLACE PROCEDURE SH.PG_PS_TOTO AS 
lval  number;
BEGIN
  select id_st into lval from sh.PG_TEMP_TOTO;
  dbms_output.put_line ('1-Valeur=:'||lVal);
  execute immediate 'truncate table SH.PG_TEMP_TOTO';
  INSERT INTO SH.PG_TEMP_TOTO VALUES (13);
  COMMIT;
  select id_st into lval from sh.PG_TEMP_TOTO;
  dbms_output.put_line ('2-Valeur=:'||lVal);
END PG_PS_TOTO;
/
 
-- Insertion ligne en SQL
INSERT INTO SH.PG_TEMP_TOTO VALUES (14);
1 rows inserted.
 
-- Controle ligne en SQL
select * from SH.PG_TEMP_TOTO;
     ID_ST
----------
        14 
 
-- Exécution SP
set serveroutput on
exec SH.PG_PS_TOTO;
anonymous block completed
1-Valeur=:14		<-- Valeur insérée en SQL avant troncate dans SP
2-Valeur=:13		<-- Valeur après insert dans SP
 
-- Controle ligne  après SP en SQL
select * from SH.PG_TEMP_TOTO;
     ID_ST
----------
        13

Pas de problèmes...

Par contre avant de relancer tout le script il faut faire au niveau SQL
truncate table SH.PG_TEMP_TOTO;

Sinon erreur:

Code:

1
2
3
4
5
6
7
8
 
SQL Error: ORA-14452: tentative de création, modification ou suppression d'un index sur une table temporaire déjà en cours d'utilisation
14452. 00000 -  "attempt to create, alter or drop an index on temporary table already in use"
*Cause:    An attempt was made to create, alter or drop an index on temporary
           table which is already in use.
*Action:   All the sessions using the session-specific temporary table have
           to truncate table and all the transactions using transaction
           specific temporary table have to end their transactions.

Ce qui est curieux car nous sommes dans la même session...
Je crois que le problème est le truncate dans la SP.

Indépendamment, je n'ai pas le comportement que vous décrivez...

jko

05/01/2014, 09h48
Rhodo33

Merci pour cet essai.

Mais que cela soit chez moi ou chez vous il y a un problème que je juge gênant pour le produit final que j'envisageais.

D'un côté je ne lance pas la SP de la même façon que vous, car je crée (involontairement) une nouvelle session.
D'un autre, le truncate qui n'est pas accepté n'est pas meilleur (je pense qu'il faut que cela soit le même user, même session ou etc. qui fasse le remplissage et le truncate.

Je vais clore ce thread.
A bientôt.