=============================================================================  페키지 생성 ===============================================

CREATE OR REPLACE PACKAGE CRYPTO
IS
   FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2, KEY_DATA IN VARCHAR2 := '12345678')RETURN RAW;
   FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2, KEY_DATA IN VARCHAR2 := '12345678')RETURN VARCHAR2;
END;


CREATE OR REPLACE PACKAGE BODY CRYPTO
IS
    -- 에러 발생시에 error code 와 message 를 받기 위한 변수 지정.
    SQLERRMSG VARCHAR2(255) ;
    SQLERRCDE NUMBER;
    -- 암호화 함수 선언 key_data 는 입력하지 않을 시에 default 로 12345678 로 지정됨.


FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2
               , KEY_DATA     IN VARCHAR2 := '12345678'
                 )
    RETURN RAW
IS
    KEY_DATA_RAW RAW(4000) ;
    CONVERTED_RAW RAW(4000) ;
    ENCRYPTED_RAW RAW(4000) ;
BEGIN
    -- 들어온 data 와 암호키를 각각 RAW 로 변환한다.
    CONVERTED_RAW := UTL_I18N.STRING_TO_RAW(INPUT_STRING, 'AL32UTF8') ;
    KEY_DATA_RAW  := UTL_I18N.STRING_TO_RAW(KEY_DATA, 'AL32UTF8') ;
    -- DBMS_CRYPTO.ENCRYPT 로 암호화 하여 encrypted_raw 에 저장.
    ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC => CONVERTED_RAW,
    -- typ 부분만 변경하면 원하는 알고리즘을 사용할 수 있다.
    -- 단, key value bype 가 다 다르니 확인해야 한다.
    TYP => DBMS_CRYPTO.DES_CBC_PKCS5, KEY => KEY_DATA_RAW, IV => NULL) ;
    RETURN ENCRYPTED_RAW;
EXCEPTION
WHEN OTHERS THEN
    RETURN INPUT_STRING;
END ENCRYPT;


FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2
               , KEY_DATA     IN VARCHAR2 := '12345678'
                 )
    RETURN VARCHAR2
IS
    CONVERTED_STRING VARCHAR2(4000) ;
    KEY_DATA_RAW RAW(4000) ;
    DECRYPTED_RAW VARCHAR2(4000) ;
BEGIN
    KEY_DATA_RAW  := UTL_I18N.STRING_TO_RAW(KEY_DATA, 'AL32UTF8') ;
    DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(SRC => INPUT_STRING, TYP => DBMS_CRYPTO.DES_CBC_PKCS5, KEY => KEY_DATA_RAW, IV => NULL) ;
    -- DBMS_CRYPTO.DECRYPT 수행 결과 나온 복호화된 raw data 를 varchar2 로 변환하면 끝!
    CONVERTED_STRING := UTL_I18N.RAW_TO_CHAR(DECRYPTED_RAW, 'AL32UTF8') ;
    RETURN CONVERTED_STRING;
EXCEPTION
WHEN OTHERS THEN
    RETURN INPUT_STRING;
END DECRYPT;
END;

=============================================================================  WRAP BODY  ===========================================

CREATE OR REPLACE PACKAGE CRYPTO
IS
   FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2, KEY_DATA IN VARCHAR2 := '12345678')RETURN RAW;
   FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2, KEY_DATA IN VARCHAR2 := '12345678')RETURN VARCHAR2;
END;


CREATE OR REPLACE PACKAGE BODY CRYPTO wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
534 227
S9q5hOMAFPBu56CRP8/j/b1wQp8wg5Ve2UjWfC+KMQ8teONGUUZG38XybAsg45TgTX+hrQi1
qsxZinAJiYRcJiLYpSLUA5iE6TH0i+eT+rpw5Tv1oaIuDiTWJiSZw/xxwbhrGKQmGGpXoW/I
I8zU85HH/2lVNEbIhO9Yb2M2iR0RFpE9feI88oV2RjSMskQZ9I9GecYm6Go92Ub+ADYeMAMu
h5qfrwuk3eC4Pnfw8Q59PQ3upUUZF+Tunw2jvjlUPpZNIZsxTWwkttd8H8BuJY7oSYN9UbYe
kHEZe7n13ma3PqIvUOC2eJJLmWjWtuKuuHEZdtR0ThzjF8FFmM2/KZ8J1Tn40D46EdsEX0jV
t4acx4okgjxcSStJarxZQK4A4VJ/IjGOAowBH4Kop9Pk4FSIa8ge82yJkuPlQBnynYUsug/v
vtahECZS5B8mVe47GdRSo57+LMacPF5cg9yKMz930Ab4me1ZLBLIGBMmjZfDnaro8Dq+Os3S
Augfgge45jDi75h1gN2aCte8V3EJm5uyTvDmxQ==


=============================================================================  테스트 ===============================================



-- 암/복화 키 : 'f35b292d-8943-4869-99f7'

SELECT CRYPTO.ENCRYPT('이거슨 암호화 할 문장', 'f35b292d-8943-4869-99f7')
  FROM DUAL ;

SELECT CRYPTO.DECRYPT('4142AAC7B23F7939FD49B1BC1B34A2AD57875011F41D2FE65B72AADDF0B721BE', 'f35b292d-8943-4869-99f7')
  FROM DUAL ;

SELECT *
  FROM(SELECT CRYPTO.DECRYPT('4142AAC7B23F7939FD49B1BC1B34A2AD57875011F41D2FE65B72AADDF0B721BE', 'f35b292d-8943-4869-99f7') AS STR
          FROM DUAL
       )
 WHERE STR LIKE '%암호화%' ;