Q

"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. It is showing an error: "ORA-01465: invalid hex number."

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
    declare
       l_key    raw(2000);
       l_user  varchar2(255);
       invalid_user  EXCEPTION;
       invalid_key  EXCEPTION;
   
begin
     
      begin  
            select key
         into l_key
         from users_keys
         where user_id = :old.user_id;
        
         :new.password := get_enc_val(:new.password,l_key);

      exception
         when no_data_found then
             raise invalid_user ;
                 when others then
                     raise invalid_key; 
      end;
      
    
exception
         when invalid_user then  
              raise_application_error(-20001, 'User is not valid');
         when invalid_key then   
              raise_application_error(-20000, 'SQL Error is '||SQLERRM);
end;
/
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.
This was first published in September 2006

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close