Appel de function en SQL Dynamique

Version imprimable

20/09/2004, 17h55
dkd

Appel de function en SQL Dynamique

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

Comment cela pas de retour de fonction avec execute immediate ?

Code:

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 ;

21/09/2004, 16h36
dkd

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

Tanks
21/09/2004, 16h40
orafrance

voila de quoi en apprendre bien plus encore ;)

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

Appel dynamique avec un type particulier

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:

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;

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

exemple :

Code:

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:

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 ;

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:

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;

Comprends pas bien votre problème.

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
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:

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>

27/09/2004, 13h15
lalystar

Bonjour,

Je ne comprends pas ton problème :

Citation:

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 ?

Citation:

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.

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:

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:

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

27/09/2004, 14h45
SheikYerbouti

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, ... )
27/09/2004, 15h33
dkd

Je fais référence à mon type puisqu'il fait parti de mes parametres d'appel à ma fontion "générique".
27/09/2004, 17h08
SheikYerbouti

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)

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:

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:

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;

27/09/2004, 17h45
SheikYerbouti

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é ?
27/09/2004, 18h18
dkd

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.

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:

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

28/09/2004, 09h37
dkd

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) ?
28/09/2004, 09h49
SheikYerbouti

Si, probablement...

essayez de déclarer les types au niveau SQL (CREATE TYPE mon_type is RECORD...) et voyez si c'est mieux accepté dans le PL/SQL
28/09/2004, 10h12
dkd

Comment peut-on déclarer les types au niveau SQL (CREATE TYPE mon_type is RECORD...) ? En utilisant aussi EXCECUTE IMMEDIATE ?
28/09/2004, 11h17
SheikYerbouti

Vous devriez lire l'article sur le SQL dynamique natif. tout y est expliqué.

mais avant de vous lancer dans l'entreprise, faites un petit essai avec un seul type sous Sql*Plus ou Toad.
28/09/2004, 15h42
dkd

Au tant pour moi, il faut que j'appelle chaque composant de mon type.

Mais étant donné que mon type contient aussi des autres types, ça va etre HARD. :roll:
28/09/2004, 18h01
dkd

En fait, je me moque de passer X parametres, car la fonction (du execute) attend un seul parametre un record.

Oracle 8.1.7 n'accepte pas de renvoyer un type Record qu'il soit simple ou complexe.