FAQ / PL/SQL PACKAGEでパプリックスコープを持つ定数をSQL文中で利用するには... Tweet

かなーり、ご無沙汰しておりました。（本業でいっぱいいっぱいで、という言い訳はこれぐらいにしてw） 偶に聞かれることがあるので、FAQネタから。 パッケージでパブリックなスコープを持つ定数は無名PL/SQLブロックやパッケージ、プロシージャ、ファンクションでしか参照できないんですよねー 例えば、DBMS_CRYPTOパッケージでHASHファンクションを利用してSH-256を作成したいなーと思って、

39.4 DBMS_CRYPTOのアルゴリズム
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_CRYPTO.html#GUID-CE3CF17D-E781-47CB-AEE7-19A9B2BCD3EC

DBMS_CRYPTO.HASH()は関数なのでSQL文から呼びたーい、と以下のような使い方をすると...

SQL> SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual;
SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual
                                         *
ERROR at line 1:
ORA-00904: "DBMS_CRYPTO"."HASH_SH2569": invalid identifier


SQL>
SQL> select DBMS_CRYPTO.HASH_SH256 from dual;
select DBMS_CRYPTO.HASH_SH256 from dual
       *
ERROR at line 1:
ORA-06553: PLS-221: 'HASH_SH256' is not a procedure or is undefined

見事にエラーとなるわけです。 DBMS_CRYPTO.HASH_SH256は、パッケージファンクションではないので...利用可能なのはPL/SQLでのみ。

SQL> set serveroutput on
SQL>
¥SQL>
SQL> begin
  2    dbms_output.put_line('DBMS_CRYPTO.HASH_SH256 : ' || DBMS_CRYPTO.HASH_SH256);
  3  end;
  4  /
DBMS_CRYPTO.HASH_SH256 : 4

PL/SQL procedure successfully completed.

SQL文で活用する為には、ファンクションでラップする必要があります。 以下のように。

SQL> l
  1  CREATE OR REPLACE FUNCTION get_hash_sh256_type
  2  RETURN NUMBER
  3  AS
  4  BEGIN
  5    RETURN DBMS_CRYPTO.HASH_SH256;
  6* END;
SQL> /

Function created.

SQL>

冒頭でエラーとなっていたSQL文をDBMS_CRYPTO.HASH_SH256を返すファンクションを使うように書き換えると、 はい、できました。

SQL> l
  1  SELECT
  2    DBMS_CRYPTO.HASH(
  3	     TO_CLOB('hoge')
  4	     , get_hash_sh256_type()
  5    ) AS "SH-256"
  6  FROM
  7*   dual
SQL> /

SH-256
--------------------------------------------------------------------------------
ECB666D778725EC97307044D642BF4D160AABB76F56C0069C71EA25B1E926825

SQL>

---

露店の焼きそばと焼き鳥を食べつつ、晩夏の夏祭りと、涼しい朝晩の気温で熟睡可能な山形より。 では、では。