Insert n'insère aucune ligne alors que le select en ramène

**McM** · 27/04/2012, 14h34

Bonjour,

J'ai un truc hallucinant que je n'arrive pas à comprendre et à corriger.
J'ai une moulinette à passer. Je fais le SELECT qui me ramène plus de 3000 lignes. Je rajoute un INSERT, et là, 0 rows inserted !!

J'ai testé sous sql+, pareil.
J'ai testé en plsql avec un curseur et un insert values, et là, oracle insère bien...

Je vais continuer à analyser, mais avez vous déjà eu ça ou une piste à me donner ?

**punkoff** · 27/04/2012, 14h36

problème de commit ?

**lola06** · 27/04/2012, 14h38

Il faudrait aussi montrer vos différents essais pour voir si tout est bon.

**McM** · 27/04/2012, 14h41

Bein non, si le select me ramène des lignes.. l'insert passé devrait en insérer.
Et puis je teste sous Toad et sous sql+, les 2 me ramène X lignes dans le select et n'en insèrent pas.

Un create table XXX as select crée bien une table avec X lignes.
Puis un insert select * from XXX insère bien les X lignes...

**lola06** · 27/04/2012, 14h49

C'était pour savoir si tu prenais les syntaxes exactes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
INSERT INTO MaTable
SELECT ...

P.S : je connaissais pas cette syntaxe pour le CREATE, me coucherais moins bête

**punkoff** · 27/04/2012, 14h51

Bah il va falloir développer votre problème car là c'est juste imbitable.

D'un point de vu extèrieur relisez-vous, car vous le présentez ainsi :
- je fait un select tout se passe bien.
- je fait un insert du select => ca ne marche pas (et aucune erreur)

=> problème de commit (toad par défaut n'est pas en auto-commit en passant)

**McM** · 27/04/2012, 14h58

Le select ramène au moins une ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> rollback;
 
Rollback complete.
 
SQL> SELECT 31 soc, z.article, 314, TRUNC(SYSDATE), z.mont
  2  FROM (
  3    SELECT article, MONTANT_TARIFX( 35, a.article, 350, SYSDATE) mont
  4    FROM  FARTICLE a
  5    WHERE a.ste = 31
  6    AND EXISTS ( SELECT 1 FROM FART_CODE_PRIX p WHERE p.ste = 31 
  7          AND p.cat_code_prix = 'GENE_3132' 
  8          AND p.code_prix IN (SELECT gencod FROM wmc_fe1400)
  9          AND p.article = a.article)
 10    ) z
 11  WHERE z.mont IS NOT NULL
 12  AND ROWNUM = 1;
 
       SOC ARTICLE                314 TRUNC(SYS       MONT
---------- --------------- ---------- --------- ----------
        31 9600102672             314 27-APR-12       2.35
 
SQL>

L'insert n'insère rien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> INSERT INTO ftarif(STE, ARTICLE, TARIF, DATE_DEB, MONTANT)
  2  SELECT 31 soc, z.article, 314, TRUNC(SYSDATE), z.mont
  3  FROM (
  4    SELECT article, MONTANT_TARIFX( 35, a.article, 350, SYSDATE) mont
  5    FROM  FARTICLE a
  6    WHERE a.ste = 31
  7    AND EXISTS ( SELECT 1 FROM FART_CODE_PRIX p WHERE p.ste = 31 
  8          AND p.cat_code_prix = 'GENE_3132' 
  9          AND p.code_prix IN (SELECT gencod FROM wmc_fe1400)
 10          AND p.article = a.article)
 11    ) z
 12  WHERE z.mont IS NOT NULL;
 
0 rows created.

L'insère avec un montant null plante pour une contrainte

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> INSERT INTO ftarif(STE, ARTICLE, TARIF, DATE_DEB, MONTANT)
  2  SELECT 31 soc, z.article, 314, TRUNC(SYSDATE), z.mont
  3  FROM (
  4    SELECT article, MONTANT_TARIFX( 35, a.article, 350, SYSDATE) mont
  5    FROM  FARTICLE a
  6    WHERE a.ste = 31
  7    AND EXISTS ( SELECT 1 FROM FART_CODE_PRIX p WHERE p.ste = 31 
  8          AND p.cat_code_prix = 'GENE_3132' 
  9          AND p.code_prix IN (SELECT gencod FROM wmc_fe1400)
 10          AND p.article = a.article)
 11    ) z
 12  WHERE z.mont IS NULL;
 
INSERT INTO ftarif(STE, ARTICLE, TARIF, DATE_DEB, MONTANT)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("WMC"."FTARIF"."MONTANT")

Le create table marche

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> CREATE TABLE wmc_fe1400_a AS
  2  SELECT 31 soc, z.article, 314 tarif, TRUNC(SYSDATE) datedeb, z.mont
  3  FROM (
  4    SELECT article, MONTANT_TARIFX( 35, a.article, 350, SYSDATE) mont
  5    FROM  FARTICLE a
  6    WHERE a.ste = 31
  7    AND EXISTS ( SELECT 1 FROM FART_CODE_PRIX p WHERE p.ste = 31 
  8          AND p.cat_code_prix = 'GENE_3132' 
  9          AND p.code_prix IN (SELECT gencod FROM wmc_fe1400)
 10          AND p.article = a.article)
 11    ) z
 12  WHERE z.mont IS NOT NULL;
 
Table created.
 
SQL> INSERT INTO ftarif(STE, ARTICLE, TARIF, DATE_DEB, MONTANT)
  2  SELECT soc, article, tarif, datedeb, mont
  3  FROM wmc_fe1400_a;
 
2023 rows created.

Le plsql marche

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SQL> set serveroutput on
SQL> rollback;
 
Rollback complete.
 
SQL> BEGIN
  2  FOR r IN (
  3    SELECT 31 soc, z.article, 314 tarif, TRUNC(SYSDATE) datedeb, z.mont
  4    FROM (
  5      SELECT article, MONTANT_TARIFX( 35, a.article, 350, SYSDATE) mont
  6      FROM  FARTICLE a
  7      WHERE a.ste = 31
  8      AND EXISTS ( SELECT 1 FROM FART_CODE_PRIX p WHERE p.ste = 31 
  9                  AND p.cat_code_prix = 'GENE_3132' 
 10                  AND p.code_prix IN (SELECT gencod FROM wmc_fe1400)
 11                  AND p.article = a.article)
 12      ) z
 13    WHERE z.mont IS NOT NULL
 14    AND ROWNUM = 1
 15      )
 16  LOOP
 17   INSERT INTO ftarif(STE, ARTICLE, TARIF, DATE_DEB, MONTANT)
 18   VALUES(r.soc, r.article, r.tarif, r.datedeb, r.mont);
 19    DBMS_OUTPUT.put_line(r.article ||':'|| SQL%ROWCOUNT);
 20  END LOOP;
 21  END;
 22  /
9600102672:1
 
PL/SQL procedure successfully completed.

**McM** · 27/04/2012, 15h04

Avant que quelqu'un demande, je n'ai qu'un seul trigger sur la table Ftarif

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
before insert or update on ftarif
for each row
begin
 :new.user_modif := user;
 :new.date_modif := sysdate;
end;

**Pomalaix** · 27/04/2012, 15h12

C'est sûr que c'est louche !
Avez-vous regardé si le plan d'exécution est le même entre le SELECT et le INSERT/SELECT ?

Et au fait, de quelle version (exacte) s'agit-il ?

**McM** · 27/04/2012, 15h13

version 9.2.0.7
Visiblement c'est la fonction qui ramène NULL dans le cas de l'insert direct.

SQL> Select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for Linux IA64: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

**McM** · 27/04/2012, 15h16

Explain plans identiques

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Plan
SELECT STATEMENT  CHOOSECost: 6 K  Bytes: 64 K  Cardinality: 2 K  						
	9 NESTED LOOPS SEMI  Cost: 6 K  Bytes: 64 K  Cardinality: 2 K  					
		6 TABLE ACCESS BY INDEX ROWID WMC.FARTICLE Cost: 1 K  Bytes: 36 K  Cardinality: 2 K  				
			5 INDEX RANGE SCAN UNIQUE WMC.FARTICLE_PK Cost: 155  Cardinality: 2 K  			
				4 NESTED LOOPS  Cost: 6  Bytes: 49  Cardinality: 1  		
					2 TABLE ACCESS BY INDEX ROWID WMC.FART_CODE_PRIX Cost: 4  Bytes: 37  Cardinality: 1  	
						1 INDEX UNIQUE SCAN UNIQUE WMC.FART_CODE_PRIX_PK Cost: 3  Cardinality: 1  
					3 TABLE ACCESS FULL WMC.WMC_FE1400 Cost: 3  Bytes: 12  Cardinality: 1  	
		8 TABLE ACCESS BY INDEX ROWID WMC.FARTICLE Cost: 3  Bytes: 5 M  Cardinality: 365 K  				
			7 INDEX RANGE SCAN NON-UNIQUE WMC.FARTICLE_ARTICLE Cost: 1  Cardinality: 3

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Plan
INSERT STATEMENT  CHOOSECost: 6 K  Bytes: 64 K  Cardinality: 2 K  						
	9 NESTED LOOPS SEMI  Cost: 6 K  Bytes: 64 K  Cardinality: 2 K  					
		6 TABLE ACCESS BY INDEX ROWID WMC.FARTICLE Cost: 1 K  Bytes: 36 K  Cardinality: 2 K  				
			5 INDEX RANGE SCAN UNIQUE WMC.FARTICLE_PK Cost: 155  Cardinality: 2 K  			
				4 NESTED LOOPS  Cost: 6  Bytes: 49  Cardinality: 1  		
					2 TABLE ACCESS BY INDEX ROWID WMC.FART_CODE_PRIX Cost: 4  Bytes: 37  Cardinality: 1  	
						1 INDEX UNIQUE SCAN UNIQUE WMC.FART_CODE_PRIX_PK Cost: 3  Cardinality: 1  
					3 TABLE ACCESS FULL WMC.WMC_FE1400 Cost: 3  Bytes: 12  Cardinality: 1  	
		8 TABLE ACCESS BY INDEX ROWID WMC.FARTICLE Cost: 3  Bytes: 5 M  Cardinality: 365 K  				
			7 INDEX RANGE SCAN NON-UNIQUE WMC.FARTICLE_ARTICLE Cost: 1  Cardinality: 3

**McM** · 27/04/2012, 15h20

C'est bon.. laissez tomber, la fonction est avec un when others NULL... et il y a une mutating table !

ORA-04091: table WMC.FTARIF is mutating, trigger/function may not see it
ORA-06512: at "WMC.MONTANT_TARIFX", line 5

**McM** · 27/04/2012, 15h24

C'est quand même bizarre... la mutating sur une fonction..
Je passe le code de la fonction dans le select, l'insert fonctionne..

**Mohamed.Houri** · 27/04/2012, 15h43

Envoyé par McM

C'est quand même bizarre... la mutating sur une fonction..
Je passe le code de la fonction dans le select, l'insert fonctionne..

problème de read_consistency

Comme la documentation Oracle l’indique clairement :

• When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement. (10g)
• When invoked from an INSERT, UPDATE, or DELETE statement, the subprogram cannot query or modify any database tables modified by that statement (11g)

Je devines donc que votre fonction MONTANT_TARIFX fait un select sur la table ftarif et c’est pourquoi vous êtes confronté à cette erreur de mutating table. Utilisez directement le code de la function dans l’insert/select(comme vous l'avez déjà fait) ou bien le hint /*+ materialize */ qui créera une table temporaire contenant le résultat de la fonction avant d’entamer l’insert

Insert n'insère aucune ligne alors que le select en ramène

SQL Oracle

Discussions similaires

Partager

Partager