I am trying to write a before update trigger which will fire only when we update the password in the users table. The password column is encrypted in the users table. The keys are stored in users_keys. I want the user to give a normal character for updating like 'test123,' but internally it will update the users table with the encrypted password.
My trigger code is below :
create or replace trigger password_setup
before update on users
for each row
where user_id = :old.user_id;
:new.password := get_enc_val(:new.password,l_key);
when no_data_found then
raise invalid_user ;
when others then
when invalid_user then
raise_application_error(-20001, 'User is not valid');
when invalid_key then
raise_application_error(-20000, 'SQL Error is '||SQLERRM);
Problem is when I try to update like :
UPDATE USERS SET PASSWORD = 'test123' where user_name = 'Mangesh'
It is showing an error: "ORA-01465: invalid hex number." Please suggest something.
I see two areas where you could be getting the ORA-1465 error. The variable L_KEY is defined as a RAW datatype and the SELECT INTO statement populates this variable. Ensure that the data being returned can be converted to the RAW datatype. This must hold hex values. The other area to check is the function GET_ENC_VAL which accepts L_KEY as a parameter. Ensure that this function can accept a raw datatype.
Dig Deeper on Oracle database design and architecture