Ask the Expert

"Invalid hex number" error with update trigger

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
       l_key    raw(2000);
       l_user  varchar2(255);
       invalid_user  EXCEPTION;
       invalid_key  EXCEPTION;
            select key
         into l_key
         from users_keys
         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
                     raise invalid_key; 
         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.

    Requires Free Membership to View

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.

This was first published in September 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: