Comment utiliser une constante de package dans une instruction SQL SELECT ?

Question

Comment utiliser une variable de package dans une simple instruction de requête SELECT dans Oracle ?

Quelque chose comme

SELECT * FROM MyTable WHERE TypeId = MyPackage.MY_TYPE

Est-ce possible ou seulement en utilisant PL/SQL (utiliser SELECT dans BEGIN/END) ?

Answer 1

Tu ne peux pas.

Pour qu'une variable de paquetage publique puisse être utilisée dans une instruction SQL, vous devez écrire une fonction d'encapsulation pour exposer la valeur au monde extérieur :

SQL> create package my_constants_pkg
  2  as
  3    max_number constant number(2) := 42;
  4  end my_constants_pkg;
  5  /

Package created.

SQL> with t as
  2  ( select 10 x from dual union all
  3    select 50 from dual
  4  )
  5  select x
  6    from t
  7   where x < my_constants_pkg.max_number
  8  /
 where x < my_constants_pkg.max_number
           *
ERROR at line 7:
ORA-06553: PLS-221: 'MAX_NUMBER' is not a procedure or is undefined

Créez une fonction enveloppante :

SQL> create or replace package my_constants_pkg
  2  as
  3    function max_number return number;
  4  end my_constants_pkg;
  5  /

Package created.

SQL> create package body my_constants_pkg
  2  as
  3    cn_max_number constant number(2) := 42
  4    ;
  5    function max_number return number
  6    is
  7    begin
  8      return cn_max_number;
  9    end max_number
 10    ;
 11  end my_constants_pkg;
 12  /

Package body created.

Et maintenant ça marche :

SQL> with t as
  2  ( select 10 x from dual union all
  3    select 50 from dual
  4  )
  5  select x
  6    from t
  7   where x < my_constants_pkg.max_number()
  8  /

         X
----------
        10

1 row selected.

Answer 2

Il existe une méthode plus générique qui fonctionne bien pour moi. Vous créez une fonction avec le nom de la constante en entrée (par exemple, schema.package.constantname) et elle vous renvoie la valeur de la constante. Vous utilisez l'exécution immédiate d'un bloc PL/SQL en liant la variable res (voir exemple).

La fonction ressemble à ceci :

CREATE OR REPLACE FUNCTION GETCONSTANTVALUE (i_constant IN VARCHAR2)  RETURN NUMBER deterministic AS

   res number; 
BEGIN

   execute immediate 'begin :res := '||i_constant||'; end;' using out res;     
   RETURN res;

END;
/

Vous pouvez ensuite utiliser la constante de n'importe quel paquet dans n'importe quel SQL, c'est-à-dire comme suit

select GETCONSTANTVALUE('PKGGLOBALCONSTANTS.constantname') from dual;

De cette manière, vous n'avez besoin que d'une seule fonction et vous profitez de l'avantage d'utiliser les constantes des paquets existants.

Answer 3

Note : J'ai seulement essayé ceci dans Oracle 11g.

J'avais un besoin similaire et j'ai trouvé plus facile de déclarer simplement une fonction (sans le package) pour retourner la valeur désirée. Pour les placer dans le ddl pour l'importation, n'oubliez pas de séparer chaque déclaration de fonction par le caractère /. Par exemple :

CREATE OR REPLACE FUNCTION UNDEFINED_INT RETURN NUMBER AS BEGIN RETURN 2147483646; END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_SHORT RETURN NUMBER AS BEGIN RETURN 32766; END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_LONG RETURN NUMBER  AS BEGIN RETURN 223372036854775806; END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_FLOAT RETURN FLOAT  AS BEGIN RETURN .4028233E38; END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_DOUBLE RETURN BINARY_DOUBLE  AS BEGIN RETURN to_binary_double('1.7976931348623155E308'); END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_STRING RETURN VARCHAR AS BEGIN RETURN '?'; END;
/

Cela vous permet de référencer la fonction comme s'il s'agissait d'une valeur constante (par exemple, vous n'avez même pas besoin des parenthèses).

Par exemple (notez les méthodes to_char pour montrer que la précision a été préservée) : SQL> select undefined_int from dual ;

UNDEFINED_INT
-------------
   2147483646

SQL> select undefined_string from dual ;

UNDEFINED_STRING
--------------------------------------------------------------------------------
?

SQL> select undefined_double from dual ;

UNDEFINED_DOUBLE
----------------
      1.798E+308

SQL> select to_char(undefined_double,'9.999999999999999EEEE') from dual ;

TO_CHAR(UNDEFINED_DOUBL
-----------------------
 1.797693134862316E+308

SQL> select to_char(undefined_double,'9.99999999999999999EEEE') from dual ;

TO_CHAR(UNDEFINED_DOUBLE,
-------------------------
 1.79769313486231550E+308

Answer 4

Non, vous n'avez pas le droit de faire ça. Vous devez fournir une fonction qui renvoie la valeur, puis l'utiliser dans le SQL :

SELECT * FROM MyTable WHERE TypeId = MyPackage.FUN_MY_TYPE