-- SYS 계정으로 유저에 권한 부여

GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO USER_ID; 
GRANT EXECUTE ON DBMS_CRYPTO TO USER_ID;




CREATE OR REPLACE PACKAGE CRYPTO_AES256
IS
    /******************************************************************************
      암호화
     ******************************************************************************/

    FUNCTION ENC_AES    ( input_string IN VARCHAR2) RETURN VARCHAR2;


    /******************************************************************************
      복호화
     ******************************************************************************/

    FUNCTION DEC_AES    ( encrypted_raw IN VARCHAR2) RETURN VARCHAR2;                        

END CRYPTO_AES256;


CREATE OR REPLACE PACKAGE BODY CRYPTO_AES256
IS

    /******************************************************************************
      암호화
     ******************************************************************************/

    FUNCTION ENC_AES    ( input_string IN VARCHAR2) RETURN VARCHAR2
    IS
        encrypted_raw   RAW(2000);                                  -- 암호화된 RAW타입 데이터
        key_bytes_raw   RAW(32);                                    -- 암호화 KEY (32RAW => 32Byte => 256bit)
        encryption_type PLS_INTEGER :=  DBMS_CRYPTO.ENCRYPT_AES256  -- 암호화 알고리즘 선언
                                    +   DBMS_CRYPTO.CHAIN_CBC
                                    +   DBMS_CRYPTO.PAD_PKCS5;

    BEGIN
        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');
        
        encrypted_raw := DBMS_CRYPTO.ENCRYPT    (   src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8')
                                                ,   typ => encryption_type
                                                ,   key => key_bytes_raw);

       
        -- 에러 방지를 위해 base64_encode로 인코딩 처리.. 
        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));

    END ENC_AES;


    /******************************************************************************
      복호화
     ******************************************************************************/

    FUNCTION DEC_AES    ( encrypted_raw IN VARCHAR2) RETURN VARCHAR2
    IS
        output_string   VARCHAR2(200);                              -- 복호화된 문자열
        decrypted_raw   RAW(2000);                                  -- 복호화된 raw타입 데이터
        key_bytes_raw   RAW(32);                                    -- 256bit 암호화 key
        encryption_type PLS_INTEGER :=  DBMS_CRYPTO.ENCRYPT_AES256  -- 복호화 알고리즘 선언
                                    +   DBMS_CRYPTO.CHAIN_CBC
                                    +   DBMS_CRYPTO.PAD_PKCS5;

    BEGIN
        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        -- 에러 방지를 위해 base64_decode로 인코딩 처리.. 
        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

        decrypted_raw := DBMS_CRYPTO.DECRYPT    (   src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw))
                                                ,   typ => encryption_type
                                                ,   key => key_bytes_raw);

       output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

       RETURN output_string;

    END DEC_AES;

END CRYPTO_AES256;