procedure execute immediate problème plantage

**nathieb** · 02/10/2007, 09h08

Bonjour,

J'essaye de créer une fonction générique que je dois appeler souvent
dans le coeur de mon programme .
function lookup-generique(nomdetable,colonne,erreur0,erreur1)

bref voici le prog

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
   FUNCTION lookup_generique (
      p_table VARCHAR2,
      p_colonne VARCHAR2,
      p_var varchar2,
	  p_error_0   IN INTEGER,
	  p_error_1   IN INTEGER
   )
   RETURN BOOLEAN
   IS
      myquery   VARCHAR2 (4000);
      istrue    BOOLEAN  default TRUE;
   BEGIN
      myquery := 'CREATE OR REPLACE FUNCTION proc_gen RETURN BOOLEAN IS';
      myquery := myquery || 'local_gen ' || p_table || '.' || p_colonne || '%TYPE;';
      myquery := myquery || 'cursor  cursor_gen is select distinct '|| p_colonne||' from '|| p_table|| ';';
      myquery := myquery || 'type tref_gen is table of '|| p_table||'.'|| p_colonne||'%type;';
      myquery := myquery || 'var_gen tref_gen;';
	  myquery := myquery || 'istrue boolean := true;';
	  myquery := myquery || 'begin';
	  myquery := myquery || 'if local_apprenti.'||p_colonne||'is null then';
	  myquery := myquery || 'insert into apprenti_upload_error(ara_apprenti_pk,numero_uai,'||p_colonne||')';
	  myquery := myquery || 'values(local_apprenti.apprenti_pk,apprenti.numero_uai,local_apprenti.'||p_var
	  		  	 		 || '(select from description from apprenti_type_error where type_error_pk='||p_error_0||';';
	  myquery := myquery || 'return false;';
	  myquery := myquery || 'else';
	  myquery := myquery || 'open cursor_gen;';
	  myquery := myquery || 'fetch cursor_gen bulk collect into var_gen;';
	  myquery := myquery || 'close cursor_gen;';
	  myquery := myquery || 'for i in var_gen.FIRST .. var_gen.LAST';
	  myquery := myquery || 'loop';
	  myquery := myquery || ' if local_apprenti.'||p_colonne||'=var_gen(i) then';
	  myquery := myquery || '   return true;';
	  myquery := myquery || ' end if;';
	  myquery := myquery || 'end loop;';
	  myquery := myquery || 'insert into apprenti_upload_error(apprenti_pk,numero_uai,'||p_colonne||')';
	  myquery := myquery || 'values(local_apprenti.apprenti_pk,local_apprenti.numero_uai,local_apprenti.'||p_var
	  		  	 		 || '(select from description from apprenti_type_error where type_error_pk='||p_error_1||';';
	  myquery := myquery || 'return false;';
	  myquery := myquery || 'end if;';
	  myquery := myquery || 'end proc_gen';
 
 
	  execute immediate 'begin'||myquery||'(:1,:2,:3,:4);end;'
	  		  into istrue
	  		  using p_table,p_colonne,p_error_0,p_error_1 
			  ;
 
	  return istrue;
   END lookup_generique;

mais tout plante quand je fais un appel ???????

l'appel en question ::

Code :

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

1
2
 
if (NOT lookup_generique('N_STATUT','CODE',12,15)) then ....

et là impossible de prendre la main sous Toad.
Je teste les SetBreakpoints mais sans succès.

Ah oui l'erreur : ORA-03113: fin de fichier sur canal de communication

Par contre impossible d'afficher myquery dans une sortie DBMS_OUTPUT m'affiche une erreur de taille .
Olivier

**McM** · 02/10/2007, 10h06

Pour débugguer de l'exécute immediate, fais un DBMS_OUTPUT (faudra découper la chaine)

Edit : J'avais pas vu la dernière ligne :
Fais comme ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DECLARE
	v_chaine VARCHAR2(500) := LPAD('a', 500, 'A');
	v_c VARCHAR2(500);
BEGIN
	v_c := v_chaine;
	WHILE v_c IS NOT NULL
	LOOP
		DBMS_OUTPUT.PUT_LINE(SUBSTR(v_c,1,255));
		v_c := SUBSTR(v_c, 256);
	END LOOP;
END;

**pifor** · 02/10/2007, 10h46

Le Error Messages dit:

ORA-03113: end-of-file on communication channel
Cause: The connection between Client and Server process was broken.
Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time

En pratique, ORA-03113 est souvent lié à un bug côté serveur: voir le fichier alert.log de l'instance et vérifiez s'il n'y pas une erreur du type ORA-07445 et un fichier trace généré côté serveur.

PS: précisez votre version Oracle.

**Loyd1974** · 02/10/2007, 11h12

Il faut rajouter un espace après le IS du Create or replace function ...
Il faut rajouter un espace après le begin
Il faut rajouter un espace après le Local_Apprenti.Colonne
Il faut rajouter un espace après le then
donc, il faut rajouter un espace après chaque chaine de caractère pour éviter ce genre de désagrément

Tu fais un insert dans la table APPRENTI_UPLOAD_ERROR pour 3 champs (ara_apprenti_pk, numero_uai , p_colonne) mais tu définis 4 valeurs apparemment (local_apprenti.apprenti_pk, apprenti.numero_uai, local_apprenti.p_var, et le select)
Dans le select depuis ta table description, il n'y a pas de définition de la ou les colonnes à ramener

Pour revoir ton code, j'ai fait:

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
31
32
33
34
35
36
37
38
39
40
 
DECLARE
myquery VARCHAR2(4000);
p_table VARCHAR2(25):='TABLE';
p_colonne VARCHAR2(25):='COLONNE';
p_var VARCHAR2(25) := 'VAR';
p_error_1 VARCHAR2(25) := 'ERROR1';
p_error_0 VARCHAR2(25) := 'ERROR0';
BEGIN
 
 myquery := 'CREATE OR REPLACE FUNCTION proc_gen RETURN BOOLEAN IS';
      myquery := myquery || 'local_gen ' || p_table || '.' || p_colonne || '%TYPE;';
      myquery := myquery || 'cursor  cursor_gen is select distinct '|| p_colonne||' from '|| p_table|| ';';
      myquery := myquery || 'type tref_gen is table of '|| p_table||'.'|| p_colonne||'%type;';
      myquery := myquery || 'var_gen tref_gen;';
      myquery := myquery || 'istrue boolean := true;';
      myquery := myquery || 'begin';
      myquery := myquery || 'if local_apprenti.'||p_colonne||'is null then';
      myquery := myquery || 'insert into apprenti_upload_error(ara_apprenti_pk,numero_uai,'||p_colonne||')';
      myquery := myquery || 'values(local_apprenti.apprenti_pk,apprenti.numero_uai,local_apprenti.'||p_var
                              || '(select from description from apprenti_type_error where type_error_pk='||p_error_0||';';
      myquery := myquery || 'return false;';
      myquery := myquery || 'else';
      myquery := myquery || 'open cursor_gen;';
      myquery := myquery || 'fetch cursor_gen bulk collect into var_gen;';
      myquery := myquery || 'close cursor_gen;';
      myquery := myquery || 'for i in var_gen.FIRST .. var_gen.LAST';
      myquery := myquery || 'loop';
      myquery := myquery || ' if local_apprenti.'||p_colonne||'=var_gen(i) then';
      myquery := myquery || '   return true;';
      myquery := myquery || ' end if;';
      myquery := myquery || 'end loop;';
      myquery := myquery || 'insert into apprenti_upload_error(apprenti_pk,numero_uai,'||p_colonne||')';
      myquery := myquery || 'values(local_apprenti.apprenti_pk,local_apprenti.numero_uai,local_apprenti.'||p_var
                              || '(select from description from apprenti_type_error where type_error_pk='||p_error_1||';';
      myquery := myquery || 'return false;';
      myquery := myquery || 'end if;';
      myquery := myquery || 'end proc_gen';
dbms_output.put_line(myquery);
END;

ensuite, je l'ai passé dans un outil permettant la mise en forme du code (Toad, PLSQL Developper ...)

**nathieb** · 03/10/2007, 13h27

bonjour,

Vous aviez raison, j'ai enfin l'affichage qui marche
par contre cela plante toujours après.

Je traine toujours d'autres pb, je pense avoir mis le doigt sur un bug.
Error ORA-07445
j'attends une réponse de mon admin
Ma procédure crée une autre procèdure dans le schéma de base
et pas dans pon package GESTION

create or replace myproc ....

faudrait que je la crée dans le meme package ...?

olivier

**McM** · 03/10/2007, 13h35

redonne la version débugguée de ta fonction stp.

**nathieb** · 03/10/2007, 13h54

Version N+1

En fait je désirais que la procédure se crée dans la package GESTION et pas ailleurs ...

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
create or replace package GESTION.....
 
 
     /*****************************************************************
   Procedure genérique pour leq gestion des erreurs
   ******************************************************************/
   function lookup_generique(p_table VARCHAR2,p_colonne VARCHAR2,p_var varchar2,p_error_0 IN INTEGER,p_error_1 IN INTEGER)
   RETURN BOOLEAN
   IS
      myquery   VARCHAR2 (4000);
      istrue    BOOLEAN  default TRUE;
	  v_c VARCHAR2(4000);
 
   BEGIN
 
 
	  myquery := 'CREATE OR REPLACE FUNCTION proc_gen RETURN BOOLEAN IS ';
      myquery := myquery || 'local_gen ' || p_table || '.' || p_colonne || '%TYPE; ';
      myquery := myquery || 'cursor  cursor_gen is select distinct '|| p_colonne||' from '|| p_table|| ' ; ';
      myquery := myquery || 'type tref_gen is table of '|| p_table||'.'|| p_colonne||'%type; ';
      myquery := myquery || 'var_gen tref_gen; ';
	  myquery := myquery || 'istrue boolean := true; ';
	  myquery := myquery || 'begin ';
	  myquery := myquery || 'if local_apprenti.'||p_colonne||' is null then ';
	  myquery := myquery || 'insert into apprenti_upload_error(apprenti_pk,numero_uai,'||p_var||') ';
	  myquery := myquery || 'values (local_apprenti.apprenti_pk,local_apprenti.numero_uai,'
	  		  	 		 || '(select from description from apprenti_type_error where type_error_pk='||p_error_0||'));';
	  myquery := myquery || 'return false; ';
	  myquery := myquery || 'else ';
	  myquery := myquery || 'open cursor_gen; ';
	  myquery := myquery || 'fetch cursor_gen bulk collect into var_gen; ';
	  myquery := myquery || 'close cursor_gen; ';
	  myquery := myquery || 'for i in var_gen.FIRST .. var_gen.LAST ';
	  myquery := myquery || 'loop ';
	  myquery := myquery || ' if local_apprenti.'||p_colonne||'=var_gen(i) then ';
	  myquery := myquery || '   return true; ';
	  myquery := myquery || ' end if; ';
	  myquery := myquery || 'end loop; ';
	  myquery := myquery || 'insert into apprenti_upload_error(apprenti_pk,numero_uai,'||p_var||') ';
	  myquery := myquery || 'values (local_apprenti.apprenti_pk,local_apprenti.numero_uai,'
	  		  	 		 || '(select from description from apprenti_type_error where type_error_pk='||p_error_1||'));';
	  myquery := myquery || 'return false; ';
	  myquery := myquery || 'end if; ';
	  myquery := myquery || 'end proc_gen; ';
 
 
	  v_c := myquery;
	  while v_c is not null
	  loop
	  	  dbms_output.put_line(substr(v_c,1,255));
		  v_c := substr(v_c,255);
	  end loop;
 
  	  execute immediate 'begin'||myquery||'(:1,:2,:3,:4,:5);end;'
  	  		  into istrue
  	  		  using p_table,p_colonne,p_var,p_error_0,p_error_1 
  			  ;
 
	  return istrue;
   END lookup_generique;

En plus c'est plutot violent, j'ai sous Toad des erreurs 007445 bref après meme quand je désactive le execute immediate , l'affichage ne fonctionne plus toad est dans les choux.

bref je rame..

olivier

**nathieb** · 03/10/2007, 13h58

j'ai un doute sur la structure de mon execute immediate
le nombre de paramètres

merci olivier

**McM** · 03/10/2007, 14h02

Myquery est un ordre de CREATION de fonction, pas d'EXECUTION
Donc déjà ça marchera jamais.

Il faut faire un execute immediate de l'ordre,
puis refaire un execute immediate de la fonction.

Code :

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

1
2
	EXECUTE IMMEDIATE myquery;
	EXECUTE IMMEDIATE 'begin :1 := proc_gen; end;' USING OUT istrue;

Bon, il y a peut être plus simple sans créer de fonction, je regarde

EDIT : Correction de l'appel de la fonction par execute

**nathieb** · 03/10/2007, 14h06

bonjour,
je viens de tenter en mode natif et la ...
SQL> ed
écrit file afiedt.buf

1 BEGIN
2 SIFAGESTION.MAINARA();
3* END;
SQL> /
BEGIN
*
ERREUR à la ligne 1 :
ORA-04068: état de packages existant rejetés
ORA-04061: état existant de package body "SIFA2.SIFAGESTION" invalidé
ORA-04065: package body "SIFA2.SIFAGESTION" a été modifié ou abandonné - non
exécuté
ORA-06508: PL/SQL : Impossible de trouver unité de programme appelé
ORA-06512: à ligne 2

olivier

**McM** · 03/10/2007, 14h10

vérifie ton package, il doit être compilé avec erreurs.

**McM** · 03/10/2007, 14h11

En fait je désirais que la procédure se crée dans la package GESTION et pas ailleurs

C'est pas possible, tu peux pas recréer un package à partir de ce même package !

**pifor** · 03/10/2007, 14h53

Envoyé par nathieb

ERREUR à la ligne 1 :
ORA-04068: état de packages existant rejetés
ORA-04061: état existant de package body "SIFA2.SIFAGESTION" invalidé
ORA-04065: package body "SIFA2.SIFAGESTION" a été modifié ou abandonné - non
exécuté
ORA-06508: PL/SQL : Impossible de trouver unité de programme appelé
ORA-06512: à ligne 2

olivier

Ces erreurs peuvent être dues au fait que le package a des variables globales qui sont des variables de session (voir la discussion ) . Si vous relancez le même code, l'erreur devrait disparaître.

**Loyd1974** · 03/10/2007, 15h37

Envoyé par McM

C'est pas possible, tu peux pas recréer un package à partir de ce même package !

Alors à quoi sert le execute immediate ?

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
 
CREATE OR REPLACE PACKAGE LOYD_TST AS
PROCEDURE CREATE_FUNC;
END loyd_tst;
/
> Package Créé
 
CREATE OR REPLACE PACKAGE BODY loyd_tst AS
PROCEDURE CREATE_FUNC IS
sql_stmt VARCHAR2(255) := 'CREATE OR REPLACE FUNCTION HeyWord RETURN VARCHAR2 IS BEGIN RETURN ''HeyWord''; End HeyWord;';
BEGIN
EXECUTE IMMEDIATE sql_stmt;
END create_func;
END loyd_tst;
/
> Corps de package Créé
 
BEGIN
loyd_tst.CREATE_func;
END;
/
> Procedure exécutée
 
SELECT heyword FROM dual
 
>HeyWord
/

**McM** · 04/10/2007, 09h56

Cette fonction n'est pas créée DANS le package, c'est une fonction simple.
Nathieb veux la créer dans le package pour quelle puisse lire les infos locales au package il me semble.

**Loyd1974** · 04/10/2007, 10h08

Envoyé par McM

Cette fonction n'est pas créée DANS le package, c'est une fonction simple.
Nathieb veux la créer dans le package pour quelle puisse lire les infos locales au package il me semble.

Effectivement, tu as raison.

Par contre, j'ai relu (rapidement, j'avoue) son code, il ne me semble pas qu'il utilise des infos locales, et le but semble de créer une fonction externe au package. Pourrais-tu nous confirmer cela Nathieb ?

**McM** · 04/10/2007, 11h29

il me semble que ceci est une variable locale

if local_apprenti.'||p_colonne

**Loyd1974** · 04/10/2007, 11h57

Envoyé par McM

il me semble que ceci est une variable locale

C'est la valeur contenu dans p_colonne qui est utilisée pour construire la chaîne de caractère qui servira à créer la fonction. p_colonne ne sera plus utilisé par la suite.

Si il avait écrit :

Code :

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

1
2
 
'if local_apprenti.p_colonne ...'

Dans ce cas là, il aurait tenté d'utiliser une variable en dehors de sa portée

Par contre, Mea Culpa, je n'avais pas bien lu le message où il disait :

En fait je désirais que la procédure se crée dans la package GESTION et pas ailleurs

Tu avais raison, ce point là est impossible

**nathieb** · 04/10/2007, 17h32

Bonjour,

effectivement je tente de créer la rocédure dans le package user.GESTION.maprocedure

A mon sens pas trouver encore ....

olivier
si cc'est possible des réponses ....

**Loyd1974** · 05/10/2007, 11h33

Envoyé par nathieb

Bonjour,

effectivement je tente de créer la rocédure dans le package user.GESTION.maprocedure

A mon sens pas trouver encore ....

olivier
si cc'est possible des réponses ....

Non, ce n'est pas possible de modifier ton package comme cela. Il n'y a pas d'option ajouter un bout de code dans mon package déjà créé. Quand on modifie un package, il est obligatoire de repasser par l'ordre CREATE OR REPLACE PACKAGE ...

Tu devras créer ta procédure comme une procédure autonome. Je ne vois pas où cela pourrais poser problème ?

Autre chose, je ne vois pas du tout l'intérêt de créer cette fonction en dynamique, tu peux surement faire du code standard et n'avoir qu'un curseur dynamique ?

procedure execute immediate problème plantage

SQL Oracle

Discussions similaires

Partager

Partager