관련URL
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_obtool.htm#sthref4194
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_obtool.htm#sthref4187

1. 암/복호화 패키지 작성

기본적으로 일반사용자 아이디에는 dbms_obfuscation_toolkit 권한이 부여 되어있으므로
별도의 권한작업 없이 암/복호화 패키지 작성 및 테스트가 가능합니다.
보안상 복호화(decrypt) FUNCTION 구현하지 않는것을 권장합니다.


CREATE OR REPLACE PACKAGE pkg_test
IS
FUNCTION encrypt (
input_string IN VARCHAR2 ,
key_data IN VARCHAR2 := '1122334455667788'
) RETURN RAW;

FUNCTION decrypt (
input_string IN VARCHAR2 ,
key_data IN VARCHAR2 := '1122334455667788'
) RETURN VARCHAR2;

END pkg_test;


CREATE OR REPLACE PACKAGE BODY pkg_test
IS
SQLERRMSG VARCHAR2(255);
SQLERRCDE NUMBER;


FUNCTION encrypt (input_string IN VARCHAR2 ,
key_data IN VARCHAR2 := '1122334455667788')
RETURN RAW
IS
key_data_raw RAW(128);
input_string_raw RAW(128);
encrypted_raw RAW(2048);

BEGIN
input_string_raw := UTL_RAW.CAST_TO_RAW(input_string);
key_data_raw := UTL_RAW.CAST_TO_RAW(key_data);

dbms_obfuscation_toolkit.DESEncrypt(
input => input_string_raw,
key => key_data_raw,
encrypted_data => encrypted_raw );
RETURN encrypted_raw;
END encrypt;

FUNCTION decrypt (input_string IN VARCHAR2 , key_data
IN VARCHAR2 := '1122334455667788')
RETURN VARCHAR2
IS
converted_string VARCHAR2(48);
key_data_raw RAW(128);
decrypted_raw VARCHAR2(2048);

BEGIN
key_data_raw := UTL_RAW.CAST_TO_RAW(key_data);

dbms_obfuscation_toolkit.DESDecrypt(
input => input_string,
key => key_data_raw,
decrypted_data => decrypted_raw);

converted_string := UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw);
RETURN converted_string;
END decrypt ;

END pkg_test;

2. 암/복호화 테스트

create table card_info ( id number, card_number varchar(32) primary key) ;

insert into card_info values ( 1 , pkg_test.encrypt('1234567812345678'));
insert into card_info values ( 2 , pkg_test.encrypt('1234567812345678', '0000111122223333'));
commit;

select * from card_info;

select id, pkg_test.decrypt(card_number) card_number from card_info;
select id, pkg_test.decrypt(card_number, '0000111122223333') card_number from card_info;

select * from card_info where card_number = '1234567812345678';
select * from card_info where card_number = utl_raw.cast_to_raw('1234567812345678');
select * from card_info where card_number = '365C80EABBC8859E91C4BDE4B3C52A4B';
select * from card_info where card_number = pkg_test.encrypt('1234567812345678');

drop package body pkg_test;
drop package pkg_test;
drop table card_info;