SQL & PL/SQL

Dear All

i m trying to encrypt numbered column in an existing  oracle table. i m getting below error

While doing encrypting number, number has to convert as RAW, this RAW values cant insert in to the numbered column so we are getting error as below

SQL> update users set salary=enc_dec.encrypt(salary) where userid=1;

update users set salary=enc_dec.encrypt(salary) where userid=1

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

======================

$ sqlplus / as sysdba

CREATE OR REPLACE PACKAGE enc_dec

AS

  FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;

  FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;

END;

/

CREATE OR REPLACE PACKAGE BODY enc_dec

AS

  encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES

  + DBMS_CRYPTO.CHAIN_CBC

  + DBMS_CRYPTO.PAD_PKCS5;

   /*
   ENCRYPT_DES is the encryption algorithem. Data Encryption Standard. Block cipher.
  Uses key length of 56 bits.
   CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext
  block before it is encrypted.
   PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based
  Cryptography Standard
  */

  encryption_key RAW (32) := UTL_RAW.cast_to_raw('MyEncryptionKey');

  -- The encryption key for DES algorithem, should be 8 bytes or more.

  FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC

  IS

  encrypted_raw RAW (2000);

  BEGIN

  encrypted_raw := DBMS_CRYPTO.ENCRYPT

  (

  src => UTL_RAW.CAST_TO_RAW (p_plainText),

  typ => encryption_type,

  key => encryption_key

  );

  RETURN encrypted_raw;

  END encrypt;

  FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC

  IS

  decrypted_raw RAW (2000);

  BEGIN

  decrypted_raw := DBMS_CRYPTO.DECRYPT

  (

  src => p_encryptedText,

  typ => encryption_type,

  key => encryption_key

  );

  RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));

  END decrypt;

END;

/

grant execute on enc_dec to scott;

create public synonym enc_dec for sys.enc_dec;

exit;

==================

SQL> desc users

Name Null?    Type

----------------------------------------- -------- ----------------------------

USERID NOT NULL NUMBER

USERNAME VARCHAR2(30)

USERLOCATION VARCHAR2(30)

PASSWORD VARCHAR2(200)

SALARY NUMBER

SQL> select enc_dec.encrypt('Hello World') encrypted from dual;

ENCRYPTED

--------------------------------------------------------------------------------

89738046FA0CFDD2581198FBF98DE2C5

SQL> select enc_dec.decrypt('89738046FA0CFDD2581198FBF98DE2C5') decrypted from dual;

DECRYPTED

--------------------------------------------------------------------------------

Hello World

SQL> select enc_dec.encrypt(123456789) encrypted from dual;

ENCRYPTED

--------------------------------------------------------------------------------

FDCFD90D28EACDD2731E4BEF181EC570

SQL> select enc_dec.decrypt('FDCFD90D28EACDD2731E4BEF181EC570') decrypted from dual;

DECRYPTED

--------------------------------------------------------------------------------

123456789

SQL> desc dual

Name Null?    Type

----------------------------------------- -------- ----------------------------

DUMMY VARCHAR2(1)

SQL> update users set userid=enc_dec.encrypt(userid) where userid=1;

update users set userid=enc_dec.encrypt(userid) where userid=1

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

SQL> update users set salary=enc_dec.encrypt(salary) where userid=1;

update users set salary=enc_dec.encrypt(salary) where userid=1

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

================================================

please give the solution, to

1.encrypt existing numbered column

2.need to encrypt while insert new value

3.need to decrypt when needed

i m using oracle 11gr2 11.2.0.1/solaris 10

Thanks