ENCRYPTION/DECRYPTION in oracle table

Robeen

Hello everyone,

I have a few queries regarding encryption and decryption in JAVA.

a) We are using code base 64 and ASE128 to do the decrypt data

b) We are calling in DB function in java to do decrypt & encrypt.

Kindly advise whether we should create the functions below in Oracle DB?

Create or replace FUNCTION   DECRYPTPD

(

 SOURCESTR IN VARCHAR2, KEYSTR in VARCHAR2

) RETURN VARCHAR2 AS

BEGIN

 RETURN UTL_RAW.CAST_TO_VARCHAR2(

        DBMS_CRYPTO.DECRYPT

        (

            SRC => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(SOURCESTR)),

            TYP => DBMS_CRYPTO.ENCRYPT_AES128+DBMS_CRYPTO.CHAIN_ECB+DBMS_CRYPTO.PAD_PKCS5,

            key => UTL_RAW.CAST_TO_RAW(KEYSTR)

        )

    )

;

END DECRYPTPD;

 

create or replace FUNCTION   ENCRYPTPD

(

 SOURCESTR IN VARCHAR2, KEYSTR in VARCHAR2

) RETURN VARCHAR2 AS

BEGIN

 RETURN utl_raw.cast_to_varchar2(utl_encode.base64_encode(

  DBMS_CRYPTO.ENCRYPT

   (SRC => UTL_RAW.CAST_TO_RAW(SOURCESTR),

   TYP => DBMS_CRYPTO.ENCRYPT_AES128+DBMS_CRYPTO.CHAIN_ECB+DBMS_CRYPTO.PAD_PKCS5,

   key => UTL_RAW.CAST_TO_RAW(KEYSTR)

   )

  ));

END ENCRYPTPD;

 

    Here is sample code do encrypt :

 

public PSGCustomerDTO getByIdDocNum(String idDocNum, String idDocType) {

     PSGCustomerDTO result = null;

 

     Session session = this.sessionFactory.getCurrentSession();

     Criteria criteria = session.createCriteria(PSGCustomerDTO.class, "customer");

     criteria.add(Restrictions.eq("id_doc_num", Helper.encryptField(idDocNum)));

     criteria.add(Restrictions.eq("id_doc_type", idDocType));

     //   criteria.add(Restrictions.isNull("id_doc_type"));

     criteria.addOrder(Order.desc("create_date"));

     List<PSGCustomerDTO> list = criteria.list();

     result = (list.size() > 0) ? list.get(0) : null;

     return result;

   }

Kindly advise how to call the decryption functions in the code above?

We are getting error below

Function DECRYPTPD compiled

LINE/COL ERROR

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

6/3   PL/SQL: Statement ignored

7/17   PLS-00201: identifier 'DBMS_CRYPTO' must be declared

Errors: check compiler log

c) Kindly advise how to decrypt the data from tables after creating the decrypt function on DB?

Thanks,

Roshan

Robeen

Hello Team,

kindly advise how to use the decrypt function.

Thanks,

Roshan