| Use of dbms_crypto packageto encrypt table columns [message #424282] |
Thu, 01 October 2009 04:33  |
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
|
Senior Member |
|
|
Hi,
I am tryiing to use dbms_crypto package for the first time to encypt my tables column
Following are my table columns
NAME1 VARCHAR2(2000),
ID1 NUMBER,
SCORE number
This table is already populated
i want to encrypt Name1 and Score column. Following are the functions i have created for Encryption and decryption.
--For Encryption
create or replace function get_enc_val
(
p_in in varchar2,
p_key in raw
)
return raw is
l_enc_val raw (2000);
l_mod number := dbms_crypto.ENCRYPT_AES128
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin
l_enc_val := dbms_crypto.encrypt
(
UTL_I18N.STRING_TO_RAW
(p_in, 'AL32UTF8'),
l_mod,
p_key
);
return l_enc_val;
end;
--For Decryption
create or replace function get_dec_val
(
p_in in raw,
p_key in raw
)
return varchar2
is
l_ret varchar2 (2000);
l_dec_val raw (2000);
l_mod number := dbms_crypto.ENCRYPT_AES128
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin
l_dec_val := dbms_crypto.decrypt
(
p_in,
l_mod,
p_key
);
l_ret:= UTL_I18N.RAW_TO_CHAR
(l_dec_val, 'AL32UTF8');
return l_ret;
end;
Key: I have stored a key in other schema and calling it by using function get_key().
Following is my insert
INSERT INTO Score_table VALUES
(get_enc_val('John',get_key()),25,get_enc_val(79,get_key()))
it is giving me following error
ORA-00932:Inconsistent Datatypes:Expected number got binary.
I checked, it is an error due to Score field, which is of number type. So do i need to change type of Score field to varchar or is there any other way to encrypt number and date field.
If i need to change the type then what will happen to the data already in Table and how do i encrypt data already in table.
[Updated on: Thu, 01 October 2009 05:05] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
