1 Reply Latest reply on Aug 13, 2012 4:07 PM by JustinCave

    How to encrypt the Number(data type) column

      h3. How to encrypt the number column and have to store the result in that same number column ?

      I know number column wont accecpt encrypted values like *"A0ED23RT"*.


      create table TEST(name varchar2(20),amount number(20));

      Insert into table TEST values('name',encrypt(20));

      SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY


      I am using DBMS.CRYPTO to encrypt.

      There is only one option to encrypt number column by using TDE(Transparent Data Encryption) , But this can be only used in System Level, not in session level.



      *** And also by using TDE its unable to hide the original value, as the name implies its transparent.

      h3. Is there any DBMS package to encrypt the number column and to produce the output as encrypted number ?

      I want to get the output like this.

      Original Values:

      insert into table TEST values('John',encrypt(20000000));

      After Encryption:

      select * from TEST;

      Name Amount

      John 46739528
        • 1. Re: How to encrypt the Number(data type) column
          I'm not sure that I understand the question.

          If you want to manually encrypt and decrypt data using DBMS_CRYPTO, the encrypted data should be stored in a RAW column. This is true whether the source data is a string, a number, or any other data type. Yes, you can often get away with storing encrypted data in a VARCHAR2 column up until you find yourself struggling with character set conversion issues at which point you'll need to convert to RAW anyway.

          You certainly can manually encrypt a number and store it in a RAW column. You can't manually encrypt anything and store it in a NUMBER column. Well, I guess you could, it just would be a lot of effort for no real benefit. It rarely makes sense to do your own manual encryption and decryption-- key management becomes a major issue at that point. Letting Oracle handle the encryption using TDE generally makes much more sense.

          1 person found this helpful