Appel de function en SQL Dynamique

**dkd** · 20/09/2004, 17h55

Bonjour à tous,

Selon ma base de données, j'ai des packages différents. Je voudrais centraliser tout ça dans un package.
Le but est d'appeler un seul package au lieu de faire des versions multiples. Et je ne peux utiliser textuellement mes pakages puisque certains n'existeront pas et donc ne compileront pas.

Je pensais à du SQL Dynamique mais ....
Le petit souci est qu'avec execute immediate je n'aurais pas le retour de ma fonction ; en DBMS_SQL je ne peux pas utiliser de BIND_VARIABLE puisque c'est ma fonction.

Auriez-vous une idée ?

Thanks

DKD

**SheikYerbouti** · 20/09/2004, 19h03

Comment cela pas de retour de fonction avec 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
CREATE OR REPLACE FUNCTION retour RETURN VARCHAR2
IS
BEGIN
  return ('ben ça alors !');
END ;
/
 
declare
 ret varchar2(256) ;
 req varchar2(256) := 'select retour from dual' ;
Begin
 execute immediate req into ret ;
 dbms_output.put_line ( ret ) ;
End ;

**dkd** · 21/09/2004, 16h36

Je ne savais pas qu'on pouvais faire execute immediate avec un INTO.

Tanks

**orafrance** · 21/09/2004, 16h40

voila de quoi en apprendre bien plus encore

http://sheikyerbouti.developpez.com/execute_immediate/

**dkd** · 27/09/2004, 10h14

Je te remercie pour le sussport de SQL Dynamique. Il est supper.

J'utilise la table magique "dual". Mais j'ai un souci car l'appel de ma fonction utilse en param un type qui lui est propre.

Existe-t-il une solution ?

Merci d'avance

DKD

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
FUNCTION Fc_APPEL (vPackage in varchar2,
		        vFonction in varchar2,
                                         vDecod in out toto ) return integer
IS
/* Contenu dans un autre package
toto is record (
    machaine varchar2(20);
    monInteger integer; );*/
 
MaRequete		varchar2(124);
MonResultat		integer;
vDecod     toto;
 
BEGIN
MaRequete := 'select ' || vPackage || '.' || vFonction  || '( :1 ) from dual';
EXECUTE IMMEDIATE MaRequete USING vDecod, OUT MonResultat;
 
-- Si j'utilise cette 2ème solution, il lui faut un type string (ce qui est comprehensible)
--MaRequete := 'select ' || vPackage || '.' || vFonction  || '('  ||  vDecod|| ') from dual';
--EXECUTE IMMEDIATE MaRequete  INTO MonResultat;	
 
-- 3ème solution, il n'aime pas le concatenation de chaine dans le EXECUTE
--MaRequete := 'select ' || vPackage || '.' || vFonction  || '(' ;
--EXECUTE IMMEDIATE (MaRequete   ||  vDecod || ') from dual') INTO MonResultat;	
 
 
return MonResultat;
end;

**SheikYerbouti** · 27/09/2004, 10h23

Ce n'est pas gênant. votre procédure doit utiliser une variable du même type que celle de votre package.

exemple :

Code :

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

1
2
3
4
5
6
7
8
9
MON_PACKAGE...
Declare
  TYPE mon_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
 
  ma_var_type mon_type ;
...
--fonctions du package
...
END;

procédure d'appel

Code :

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

1
2
3
4
5
6
7
8
Declare
  var MON_PACKAGE.montype%type ;
  req VARCHAR2(256) := 'BEGIN MON_PACKAGE.ma_fonction( :1 ) ....
  ...
Begin
  EXECUTE IMMEDIATE req USING var INTO ... ;
...
End ;

**dkd** · 27/09/2004, 11h26

Je suis désolée, mais j'obtients une erreur selon les cas décrit dessous.
Ma variable vDecod doit etre retournee. Je ne peux pas la décomposée. Et c'est un type de type RECORD avec

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
 
 TYPE TOTO IS RECORD (
    message       VARCHAR2(32000),
    messageid    VARCHAR2(32),
    codage		OtherTyRecord,
    autrecode	OtherOtherTypeRecord)
------------------------
------------------------
 
FUNCTION Fc_APPEL(vFonction in varchar2,
		vDecod IN OUT Autre_Pck.toto) RETURN INTEGER
IS 
MonPackage		varchar2(124);
MaRequete		varchar2(124);
MonResultat		integer;
 
BEGIN
	EXECUTE IMMEDIATE 'BEGIN ' || MonPackage || '.' || vFonction  || '( :1 ); END;' INTO MonResultat USING vDecod ; 	
-- PLS-00457: les expressions doivent être de type SQL.
 
 
EXECUTE IMMEDIATE 'BEGIN ' || MonPackage || '.' || vFonction  || '( :1 ); END;' INTO MonResultat USING  in out vDecod ;
--PLS-00254: Les modes OUT et IN/OUT ne peuvent pas être utilisés dans ce contexte
 
RETURN MonResultat;
END;

**SheikYerbouti** · 27/09/2004, 11h46

Comprends pas bien votre problème.

voici un exemple :

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
CREATE OR REPLACE package test_blob
IS
  TYPE mon_record is RECORD (
    ch1 varchar2(10),
	ch2 varchar2(10) ) ;
 
  FUNCTION test ( param IN OUT test_blob.mon_record ) RETURN VARCHAR2 ;
END ;
/
 
CREATE OR REPLACE package body test_blob IS
 
  FUNCTION test ( param IN OUT test_blob.mon_record ) RETURN VARCHAR2 IS
    LC$req varchar2(256) := 'select texte from messages where code = :1';
	result varchar2(256);
  Begin
	execute immediate LC$Req INTO result USING param.ch1 ;
	return result ; 
  End ;
 
  END;
/

et le bloc PL/SQL anonyme qui l'appelle :

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
SQL> select code, texte  from messages where code = 100 ;
 
      CODE
----------
TEXTE
----------------------------------------------------------
       100
L'unique utilisateur PARAM ne peut pas être désactivé
 
 
SQL> Declare
  2  mavar test_blob.mon_record ;
  3    result varchar2(256) ;
  4  begin
  5    mavar.ch1 := '100';
  6    mavar.ch2 := 'toto' ;
  7    result := test_blob.test( mavar ) ;
  8    dbms_output.put_line( result) ;
  9  end ;
 10  /
 
L'unique utilisateur PARAM ne peut pas être désactivé
 
Procédure PL/SQL terminée avec succès.
 
SQL>

**lalystar** · 27/09/2004, 13h15

Bonjour,

Je ne comprends pas ton problème :

Bonjour à tous,

Selon ma base de données, j'ai des packages différents. Je voudrais centraliser tout ça dans un package.
Le but est d'appeler un seul package au lieu de faire des versions multiples.

Qu'est-ce-que tu souhaites faire exactement : regrouper tous le code de toutes les fonctions de tous les packages dans un seul package.
Ou bien avoir un package qui appelle les méthodes d'autres package ?

Et je ne peux utiliser textuellement mes pakages puisque certains n'existeront pas et donc ne compileront pas.

De toutes manières au moment où tu exécutes une fonction d'un package, cette fonction doit exister.

Si c'est pour des problèmes de compilation, déclares dans l'en-tête du package toutes les méthodes qui à terme existeront, et implémentes les au fur et à mesure que tu avances dans ta migration, non ?

Laly.

**dkd** · 27/09/2004, 14h38

En fait, j'ai selon ma base de données des packages différents qui appartiennent à d'autres personne (ex PCK_TOTO, PCK_TITI) qui ont les mêmes fonctions qui différes de peu (ex Fc_Add_Client). On passe un parametre toto qui est un type bien spécifique.
Je ne suis pas du tout maitre des autres packages. Je dois simplement les utiliser et dans certains cas il existeront ou pas.

Code :

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

1
2
3
4
5
TYPE TOTO IS RECORD ( 
    message       VARCHAR2(32000), 
    messageid    VARCHAR2(32), 
    codage      OtherTyRecord, 
    autrecode   OtherOtherTypeRecord)

Je dois créer un package qui appelle les fonctions de ces packages. Il est évident que je teste la presence de mon package avant le "EXECUTE IMMEDIATE".

La décalaration de ou des fonctions est la suivante : PCK_TOTO.Fc_Add_Client(monToto in out TOTO) return integer;

Ma fonction générique sera du style:

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
FUNCTION Fc_APPEL(vFonction in varchar2,
			vvTOTO IN OUT TOTO ) RETURN INTEGER
IS 
MonPackage		varchar2(124);
MaRequete		varchar2(124);
MonResultat		integer;
 
BEGIN
	-- Traitement de l'existence du package 
 
	-- Le package existe 
--	MaRequete := 'select ' || vPackage || '.' || vFonction  || '(' || vvTOTO|| ') from dual' ;
 
	MaRequete := 'BEGIN ' || MonPackage || '.' || vFonction  || '( :1 ); END;' ;
 
--	EXECUTE IMMEDIATE MaRequete INTO MonResultat USING IN OUT vvTOTO;
	EXECUTE IMMEDIATE 'BEGIN ' || MonPackage || '.' || vFonction  || '( :1 ); END;' INTO MonResultat USING  in out vvTOTO ; 
	RETURN MonResultat;
 
EXCEPTION		
WHEN OTHERS THEN
	RETURN SQLCODE;
 
END Fc_APPEL;

Mon probleme est que je n'arrive pas à passer correctement le parametre TOTO.
Les erreurs sont les suivantes
PLS-00457: les expressions doivent être de type SQL.
OU
PLS-00254: Les modes OUT et IN/OUT ne peuvent pas être utilisés dans ce contexte

**SheikYerbouti** · 27/09/2004, 14h45

Si le type est déclaré dans un autre package, vous devez vous y référer dans le votre.

exemple : package_toto (de l'autre user)
TYPE mon_type .....

autre package_titi (le votre)

Function ....( ... param user_toto.package_toto.mon_type, ... )

**dkd** · 27/09/2004, 15h33

Je fais référence à mon type puisqu'il fait parti de mes parametres d'appel à ma fontion "générique".

**SheikYerbouti** · 27/09/2004, 17h08

Je ne sais que vous dire. Tout cela fonctionne sur ma base 9.2

quelle est votre version d'Oracle ?
pouvez-vous joindre l'intégralité du code (notament le lieux de définition de votre type TOTO)

**dkd** · 27/09/2004, 17h34

Si vous etes en 9.2 et moi en 8.1.7, ca veut tout simplement dire qu'on ne peut pas le faire en 8i.

Code :

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

1
2
3
4
5
6
  TYPE IHE_DEC_T IS RECORD (
    message        MSG_T,
	messageid      MSG_ID_T,
	hl7dec		   PCK_HL7_DEF.HL7decRec,
	ihecfg		   IHE_CFG_T
  );

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
62
63
FUNCTION Fc_APPEL_ADT (vFonction in varchar2,
					  vDecod IN OUT PCK_IHE_DEF.IHE_DEC_T) RETURN INTEGER
IS 
MonPackage		varchar2(124);
MaRequete		varchar2(124);
MonResultat		integer;
MonCurseur      REF_CURSOR;
 
OtherCursor 	integer;
Execution		integer;
 
BEGIN
 
	 contexte := 'Verification du parametre obligatoire'; 
	-- Il est obligatoire d'avoir le nom de la fonction et ses parametres -- 
	IF vFonction IS NULL THEN
	   TRACE_ERREUR (CONTEXTE , C_ERR_APPEL_ADT); 
	   RETURN C_ERR_APPEL_ADT;
	END IF;
 
	-- On cree un requete pour vérifier l'existence de la fonction --
	contexte := 'Construction de la requete pour la recherche du package';
 
	MaRequete :='select upper(object_name) from dba_objects ';
	MaRequete := MaRequete || ' where status = ''VALID'' and object_type = ''PACKAGE'''; 
	MaRequete := MaRequete || ' and ( object_name = upper(''PCK_IHE_CPAGE_RECV_ADT'')';
	MaRequete := MaRequete || '      OR object_name = upper(''PCK_IHE_ADT_RECV_STD'')';
	MaRequete := MaRequete || '      OR object_name = upper(''PCK_IHE_ADT_RECV_IMS''))';
 
 
	contexte := 'Verification de l''existence du package';
	OPEN MonCurseur FOR MaRequete ;		
	FETCH MonCurseur INTO MonPackage ;
	-- si la fonction ou le package n'existe pas c'est une erreur et on sort --
	IF MonCurseur%NOTFOUND OR MonPackage is null THEN
	    contexte := 'Il n''y a pas d''interface installée ADT connue';
		TRACE_ERREUR (CONTEXTE , C_ERR_APPEL_ADT);
	    RETURN C_ERR_APPEL_ADT;
	END IF;
	CLOSE MonCurseur;
 
	-- Le package existe 
	contexte := 'Creation de l''appel à '||MonPackage ||'.'||vFonction ;
--	MaRequete := 'select ' || vPackage || '.' || vFonction  || '(' || vDecod|| ') from dual' ;
 
	--MaRequete := 'BEGIN ' || MonPackage || '.' || vFonction  || '( :1 ); END;' ;
 
	EXECUTE IMMEDIATE MaRequete INTO MonResultat USING vdecod;
--	EXECUTE IMMEDIATE 'BEGIN ' || MonPackage || '.' || vFonction  || '( :1 ); END;' INTO MonResultat USING vDecod ;
	RETURN MonResultat;
 
 
EXCEPTION		
WHEN OTHERS THEN
	 IF MonCurseur%ISOPEN THEN
	 	CLOSE MonCurseur;
	 END IF;
	 TRACE_ERREUR (CONTEXTE , C_ERR_APPEL_ADT);
	 TRACE_ERREUR (MaRequete , C_ERR_APPEL_ADT);
 	 TRACE_ERREUR (SQLERRM ,SQLCODE);
	 RETURN SQLCODE;
 
END Fc_APPEL_ADT;

**SheikYerbouti** · 27/09/2004, 17h45

Et votre type TYPE IHE_DEC_T est bien créé dans les spécifs de votre package ?

avez-vous essayé de reproduire le code que j'ai donné ?

**dkd** · 27/09/2004, 18h18

En fait le type IHE_DEC_T fait parti d'un autre package dont je ne maitrise pas le contenu mais que j'utilise.

Toutefois, j'ai dupliqué TOUT son type, et cela me donne les mêmes erreur.

Quand à
" Function ....( ... param user_toto.package_toto.mon_type, ... )", je ne cromprends pas comme l'utiliser.

**SheikYerbouti** · 27/09/2004, 18h31

Attention : un type même identique d'un autre package ne l'est pas pour Oracle. c'est pourqoui vous devez préfixer le type voulu avec le nom du package qui le déclare, et éventuellement le nom du user qui possède le package.

compilez l'exemple suivant chez vous

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
 
CREATE OR REPLACE package test
IS
  TYPE mon_record is RECORD (
    ch1 varchar2(10),
	ch2 varchar2(10) ) ;
 
  FUNCTION test ( param IN OUT test.mon_record ) RETURN VARCHAR2 ;
END ;
/
 
CREATE OR REPLACE package body test IS
 
  FUNCTION test ( param IN OUT test.mon_record ) RETURN VARCHAR2 IS
    LC$req varchar2(256) := 'select texte from messages where code = :1';
	result varchar2(256);
  Begin
	execute immediate LC$Req INTO result USING param.ch1 ;
	param.ch2 := substr(result,1,10) ;
	return result ; 
  End ;
 
 
  END;
/

et appelez-le avec le bloc PL/SQL anonyme donné quelques posts plus haut

**dkd** · 28/09/2004, 09h37

J'ai fait exactement ce qu'il y avait dans ton code, et j'ai toujours la même erreur lors de la compilation "PLS-00457: les expressions doivent être de type SQL", au niveau de "USING param.ch1 ".

Cela ne viendra pas tout simplement de la version d'Oracle (8.1.7) ?