--[EDIT BY SINU]
conn / as sysdba
create user sinu
identified by sinu;
grant resource, connect to sinu;
grant execute on DBMS_CRYPTO to sinu;
conn sinu/sinu
CREATE OR REPLACE PACKAGE pkg_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 pkg_crypto;
/
CREATE OR REPLACE PACKAGE BODY pkg_crypto
IS
SQLERRMSG VARCHAR2(255);
SQLERRCDE NUMBER;
FUNCTION encrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
RETURN RAW
IS
key_data_raw RAW(64);
converted_raw RAW(64);
encrypted_raw RAW(64);
BEGIN
converted_raw := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
key_data_raw := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');
encrypted_raw :=
DBMS_CRYPTO.ENCRYPT(
src => converted_raw ,
typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
key => key_data_raw ,
iv => NULL);
RETURN encrypted_raw;
END encrypt;
FUNCTION decrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
RETURN VARCHAR2
IS
converted_string VARCHAR2(64);
key_data_raw RAW(64);
decrypted_raw VARCHAR2(64);
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);
converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');
RETURN converted_string;
END decrypt ;
END pkg_crypto;
/
col card_number format a64
create table card_info ( id number, card_number varchar2(64) ) ;
insert into card_info values ( 1 , pkg_crypto.encrypt('1234567812345678')) ;
insert into card_info values ( 2 , pkg_crypto.encrypt('12345678')) ;
commit;
select * from card_info ;
select id , pkg_crypto.decrypt(card_number) card_number
from card_info;
insert into card_info values ( 3 , pkg_crypto.encrypt('1234567812345678','00000000')) ;
select id , pkg_crypto.decrypt(card_number,'00000000') card_number from card_info where id = 3 ;
select * from card_info ;
drop package body pkg_crypto;
drop package pkg_crypto;
drop table card_info;
conn / as sysdba
drop user sinu cascade;
--[EDIT BY SINU]
