Appel d'un curseur sur une table pas encore créée

Version imprimable

30/12/2010, 16h58
coco_mtl

Appel d'un curseur sur une table pas encore créée
Bonjour,

le problème est le suivant : j'ai un script PL/SQL qui permet d'alimenter une table depuis une autre. Dans ce script, une procédure crée une table temporaire et l'alimente avec les données sources. Ensuite je dois parcourir cette table pour alimenter la cible (boucle LOOP avec des INSERT). La table temporaire est détruite à la fin du script.

Au lancement du script donc, la table temporaire n'existe pas. Et donc le curseur qui pointe dessus pose problème et j'ai l'erreur TABLE INEXISTANTE au lancement.

J'ai d'abord déclaré le curseur dans le DECLARE. Puis aussi après la procédure avec la syntaxe suivante pour ruser un peu mais le problème est identique :
Code:

1 2 3 FOR record IN (SELECT * FROM TMP_MA_TABLE) LOOP INSERT ... ; END LOOP;
Si je force la création de la table temporaire au début du script c'est pareil aussi. :(

Merci d'avance de votre aide si vous avez une idée !
30/12/2010, 18h22
skuatamad
Tu utilises les tables temporaires comme dans SqlServer mais c'est différent sur Oracle, cf asktom sur les tables temporaires.

Citation:

the main exception being that they are 'statically' defined. You create
them once per database, not once per stored procedure in the database.

Il existe sûrement d'autres entrées autour de ce sujet sur asktom.
Sinon concernan l'insert, le curseur semble inutile, il vaut mieux juste un insert :
Code:

1 2 insert into MA_TABLE select * from TMP_MA_TABLE
Une autre entrée où l'utilisation est plutôt comme une interface :
http://asktom.oracle.com/pls/apex/f?...15826034070548
30/12/2010, 20h59
pachot

Bonjour,
Pour créer une procédure qui utilise une table qui n'existe pas encore, il faut faire du SQL dynamique.
Mais pour une table temporaire, sous Oracle, il ne faut pas la créer à chaque fois, mais créer une Global Temporary Table.
Cordialement,
Franck.
31/12/2010, 09h17
coco_mtl
Citation:
Envoyé par skuatamad

Tu utilises les tables temporaires comme dans SqlServer mais c'est différent sur Oracle, cf asktom sur les tables temporaires.

Il existe sûrement d'autres entrées autour de ce sujet sur asktom.
Sinon concernan l'insert, le curseur semble inutile, il vaut mieux juste un insert :

Code:

1 2 insert into MA_TABLE select * from TMP_MA_TABLE

Une autre entrée où l'utilisation est plutôt comme une interface :
http://asktom.oracle.com/pls/apex/f?...15826034070548
En fait je me suis mal exprimé : j'utilise une table temporaire mais pas au sens TEMPORARY TABLE. C'est une table classique en fait.

Sinon mon curseur me sert à parcourir les lignes une à une. J'en ai besoin car en cas de rejet d'insertion sur une ligne je récupère certaine infos (ROWID...).
31/12/2010, 10h18
coco_mtl
Citation:

Envoyé par pachot

Bonjour,
Pour créer une procédure qui utilise une table qui n'existe pas encore, il faut faire du SQL dynamique.

Oui je parviens bien à générer la requête d'insertion mais le problème c'est faire une LOOP pour parcourir chaque enregistrement. Pour l'instant j'ai le code suivant mais je vois pas comment faire une boucle :
Code:

1 2 3 4 5 6 7 BEGIN DWH.CREATE_TMP ('TMP_TOTO', 'SELECT * FROM ...'); REQ := 'INSERT INTO TOTO (ch1, ch2) (SELECT ch1, ch2 FROM TMP_TOTO)'; EXECUTE IMMEDIATE REQ; END;

Il y a pas mal de chose dans la doc notemment si tu veux BULK LOADER : Native Dynamic SQL
voici un exemple :

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
SQL> create table MA_TABLE (a number, b varchar2(10))
  2  /
 
Table created.
 
SQL> declare
  2    l_select varchar2 (100);
  3    l_insert varchar2 (100);
  4    l_cursor sys_refcursor;
  5    l_rec  MA_TABLE%rowtype;
  6  begin
  7    execute immediate 'create table TMP_MA_TABLE (a number, b varchar2(10))';
  8    execute immediate 'insert into TMP_MA_TABLE (a, b) values (1,''toto'')';
  9    commit;
 10    l_select := 'select a,b from TMP_MA_TABLE';
 11    open l_cursor for l_select;
 12      loop fetch l_cursor into l_rec;
 13        exit when l_cursor%notfound;
 14  
 15        l_insert := 'insert into MA_TABLE (a, b) values (:1, :2)';
 16        execute immediate l_insert using l_rec.a, l_rec.b;
 17      end loop;
 18    close l_cursor;
 19    commit;
 20    execute immediate 'drop table TMP_MA_TABLE';
 21  end;
 22  /
 
PL/SQL procedure successfully completed.
 
SQL> select * from ma_table
  2  /
 
         A B
---------- ----------
         1 toto
 
SQL> drop table ma_table
  2  /
 
Table dropped.

Par contre je ne vois tout de même pas l'intérêt de supprimer et recréer la table, autant la conserver.

31/12/2010, 14h36
coco_mtl

Citation:

Envoyé par skuatamad

Il y a pas mal de chose dans la doc notemment si tu veux BULK LOADER : Native Dynamic SQL
voici un exemple :

Par contre je ne vois tout de même pas l'intérêt de supprimer et recréer la table, autant la conserver.

Merci ça marche effectivement très bien comme ça !

Quant à la suppression moi non plus je ne vois pas l'intérêt mais je me plie aux normes de devs :?

Citation:

Envoyé par skuatamad

Il y a pas mal de chose dans la doc notemment si tu veux BULK LOADER : Native Dynamic SQL
voici un exemple :

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
SQL> create table MA_TABLE (a number, b varchar2(10))
  2  /
 
Table created.
 
SQL> declare
  2    l_select varchar2 (100);
  3    l_insert varchar2 (100);
  4    l_cursor sys_refcursor;
  5    l_rec  MA_TABLE%rowtype;
  6  begin
  7    execute immediate 'create table TMP_MA_TABLE (a number, b varchar2(10))';
  8    execute immediate 'insert into TMP_MA_TABLE (a, b) values (1,''toto'')';
  9    commit;
 10    l_select := 'select a,b from TMP_MA_TABLE';
 11    open l_cursor for l_select;
 12      loop fetch l_cursor into l_rec;
 13        exit when l_cursor%notfound;
 14  
 15        l_insert := 'insert into MA_TABLE (a, b) values (:1, :2)';
 16        execute immediate l_insert using l_rec.a, l_rec.b;
 17      end loop;
 18    close l_cursor;
 19    commit;
 20    execute immediate 'drop table TMP_MA_TABLE';
 21  end;
 22  /
 
PL/SQL procedure successfully completed.
 
SQL> select * from ma_table
  2  /
 
         A B
---------- ----------
         1 toto
 
SQL> drop table ma_table
  2  /
 
Table dropped.

Je relance le sujet pour une question additionnelle : je dois, en cas de rejet lors de l'insertion de la ligne dans MA_TABLE, lancer une procédure spécifique avec le ROWID de la ligne en question.
Le problème c'est que je parcours le curseur de type MA_TABLE%rowtype. Donc quand je lance la procédure avec en paramètre l_rec.ROWID il ne le connait pas évidemment.
Merci de votre aide.

Utilisez un curseur modèle pour déclarer le rowid

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> declare
  2    cursor crs_modele Is
  3      Select rowid, t.*
  4        From ma_table t;
  5    --
  6    SubType rMa_Table Is crs_modele%RowType;
  7    l_var   rMa_Table;
  8  Begin
  9    Null;
 10  End;
 11  /
 
PL/SQL procedure successfully completed
 
SQL>

03/01/2011, 17h17
coco_mtl

Citation:

Envoyé par mnitu

Utilisez un curseur modèle pour déclarer le rowid

:ccool:
Merci beaucoup !