truncate dans une procédure PL/SQL [Résolu]

[pierre.egaud]

Bonjour,

J'ai une procédure PL/SQL qui doit vider une table entière, j'ai donc voulu utilisé un truncate au lieu d'un delete (trop gourmand en temps d'exécution).

Petit problème : j'ai une erreur sur le truncate à la compilation de ma proc.

ma syntaxe est bonne pourtant.

Code :

TRUNCATE TABLE SO3

Le truncate est bien possible dans une proc PLSQL ?

Merci pour vos réponses

[remi4444]

Truncate, ce n'est pas du SQL donc ça ne se compile pas dans du PL/SQL, cependant on peut passer en indirect par:

Code :

EXECUTE IMMEDIATE 'TRUNCATE TABLE SO3'

[orafrance]

attention, ça commit la transaction

[Magnus]

Envoyé par Fred_D

attention, ça commit la transaction

La transaction en cours est commitée parce qu'il s'agit d'une instruction DDL, non ?
Dans ce cas, si l'on veut éviter ce principe, n'est-il pas possible d'encapsuler le code exécuté par EXECUTE IMMEDIATE dans une transaction autonome ?

[plaineR]

Salut Magnus,

Oui, cela committe parce que c'est une instruction DDL (=> commit implicit)

On peut tout à fait encapsuler cela dans une procédure autonome, attention néanmoins au lock.

[pierre.egaud]

merci beaucoup. Je ne savais pas

[remi4444]

Envoyé par plaineR

...
On peut tout à fait encapsuler cela dans une procédure autonome, attention néanmoins au lock.

Le truncate fonctionne en "nowait" donc il ne tolèrera pas d'accès concurent. s'il est appelé dans une procédure autonome, il se mettra donc en "concurence" avec sa procédure appelante. Si dans cette dernière il y a eu des modif sur la table, une exception sera levée...

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
SQL> CREATE procedure trunc_toto IS
  2  pragma autonomous_transaction;
  3  begin
  4  execute immediate 'truncate table toto';
  5  end;
  6  /
 
Procédure créée.
 
SQL> CREATE TABLE toto (c1 varchar(32));
 
TABLE créée.
 
SQL>  INSERT INTO toto VALUES ('toto');
 
1 ligne créée.
 
SQL> call trunc_toto();
call trunc_toto()
     *
ERREUR à la ligne 1 :
ORA-00054: resource busy AND acquire WITH NOWAIT specified
ORA-06512: at "LE_USER.TRUNC_TOTO", line 4
 
SQL>

[orafrance]

c'est bien ce qu'il dit quand il prévient de faire attention aux locks

[Magnus]

Envoyé par remi4444

...Si dans cette dernière il y a eu des modif sur la table...

Tu parles de la procédure appelante ?
Dans ton exemple, la procédure appelante ne modifie pas la structure de la table TOTO et pourtant l'invocation de la procédure génère un message d'erreur.
Je ne comprends pas ta dernière phrase, tu peux la reformuler s'il te plait ?

[remi4444]

Désolé, je me suis peut etre mal exprimé effectivement...

Je parlais simplement de modification des données, c'est a dire quelque chose qui provoque un lock.
Une modification de structure provoquerait aussi un commit implicite donc le problème ne se poserait pas.

[Magnus]

Envoyé par remi4444

Désolé, je me suis peut etre mal exprimé effectivement...

Nan nan, j'ai tendance à faire des malentendus voilà tout

J'ai donc finalement compris ton idée mais regarde ce que j'obtiens sur une base en 10gR1 :

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
10g SOC5> CREATE OR REPLACE procedure trunc_dvp IS
  2    pragma autonomous_transaction;
  3  begin
  4    execute immediate 'truncate table dvp';
  5  end;
  6  /
 
Procédure créée.
 
10g SOC5> DROP TABLE dvp;
 
TABLE supprimée.
 
10g SOC5> CREATE TABLE dvp(x number(2));
 
TABLE créée.
 
10g SOC5> call trunc_dvp;
commande inconnue début "call trunc..." - reste de la ligne ignoré.
10g SOC5> call trunc_dvp();
commande inconnue début "call trunc..." - reste de la ligne ignoré.
10g SOC5> begin
  2  trunc_dvp;
  3  end;
  4  /
 
Procédure PL/SQL terminée avec succès.
 
10g SOC5> INSERT INTO dvp VALUES (15);
 
1 ligne créée.
 
10g SOC5> begin
  2  trunc_dvp;
  3  end;
  4  /
begin
*
ERROR à la ligne 1 :
ORA-00054: ressource occupée et acquisition avec NOWAIT (pas d'attente) indiquée
ORA-06512: à "SOC5.TRUNC_DVP", ligne 4
ORA-06512: à ligne 2
 
 
10g SOC5>

Cet exemple illustre parfaitement l'exception qui est levée à partir du moment où j'ai inséré des données dans ma table.

Ceci dit, je m'étonne de voir que la méthode call ne fonctionne pas chez moi.
J'en profite pour te demander si tu n'aurais pas une explication par hasard (j'imagine bien que c'est complètement indépendant de ce topic) au fait que cette commande soit inconnue ?

[remi4444]

Envoyé par Magnus

....
Ceci dit, je m'étonne de voir que la méthode call ne fonctionne pas chez moi.
J'en profite pour te demander si tu n'aurais pas une explication par hasard (j'imagine bien que c'est complètement indépendant de ce topic) au fait que cette commande soit inconnue ?

Je pense qu'en mettant les parenthèses dans l'appel du call, ça aurait une meilleure chance de fonctionner....

Eh oui je sais... pas de parenthèse à la déclaration, pas de parenthèse à l'interrieur d'un block, mais des parenthèses pour un call...

[Magnus]

Envoyé par remi4444

Je pense qu'en mettant les parenthèses dans l'appel du call, ça aurait une meilleure chance de fonctionner....

Eh oui je sais... pas de parenthèse à la déclaration, pas de parenthèse à l'interrieur d'un block, mais des parenthèses pour un call...

Merci de ta patience Rémi mais tu remarqueras que dans mon exemple j'ai essayé les 2 syntaxes et aucune ne fonctionne (?)

[remi4444]

Envoyé par Magnus

Merci de ta patience Rémi mais tu remarqueras que dans mon exemple j'ai essayé les 2 syntaxes et aucune ne fonctionne (?)

Ah oui ok je vais m'acheter des lunettes.....


Ceci dit, je comprends pas... chez moi j'ai essayé avec le sqlplus classique et avec le sqlplus-worksheet ça fonctionne en 10gR2 en 9i et en 8i.
La seule version que j'ai pas c'est la 10gR1.......

[Magnus]

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
8i CYRIL> SHOW release
release 801070400
8i CYRIL> CREATE OR REPLACE procedure trunc_dvp IS
  2  begin
  3    execute immediate 'trunc table dvp';
  4    dbms_output.put_line('trunc effectué sur la table dvp');
  5  end;
  6  /
 
Procédure créée.
 
8i CYRIL> DROP TABLE dvp;
 
TABLE supprimée.
 
8i CYRIL> CREATE TABLE dvp (x long);
 
TABLE créée.
 
8i CYRIL> call trunc_dvp();
commande inconnue début "call trunc..." - reste de la ligne ignoré.
8i CYRIL> DESC call
ERROR :
ORA-04043: object call does NOT exist

En 8i ça ne fonctionne donc pas chez moi mais je vais considérer que c'est dû à un paramétrage ou autre...

Merci pour les explications.