6 Replies Latest reply: Nov 20, 2012 3:38 AM by Hema RSS

    DBMS_CRYPTO.DECRYPT issue

    Hema
      Dear oracle Experts.
      Im using the following oracle database.

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE     10.2.0.4.0     Production
      TNS for Linux: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production

      I have a issue in "DBMS_CRYPTO" oracle supplied API.

      My functionality is , I have to encrypt something and later using the encrypted value, i have to decrypt. For example, sample text " oracle" is to be encrypted and it will give "85999E8E7A39C6651E9FC4A9893FF1DB" as encrypted value. later part of my functionality, I have to use this "85999E8E7A39C6651E9FC4A9893FF1DB" as a input string and I have decrypt this back to original string ( ie Oracle).

      I wrote this API for the same. But for encrypt it is working fine.
      for Decypt , it is giving the error. Please help me.

      CREATE OR REPLACE PROCEDURE ENCRYPT_HEMA
      /********************************************************************************************
      * Procedure Name : ENCRYPT_HEMA
      * Parameters : p_input_text,p_encrypted_value,p_decrypted_value,p_err_msg
      * Purpose : To encrypt and decypt a given text
      * Author : Hemakumar for Q3 Enh 2012
      * Created : 22-OCT-12
      ********************************************************************************************/
      (P_INPUT_TEXT IN VARCHAR2,
      P_ENCRYPTED_VALUE IN OUT VARCHAR2,
      P_DECRYPTED_VALUE OUT VARCHAR2,
      P_DECRYPT IN VARCHAR2 DEFAULT NULL,
      P_ERR_MSG OUT VARCHAR2) IS

      CHK_EXCEPTION EXCEPTION;
      V_ENCRYPTED_TEXT VARCHAR2(4000);
      V_DECRYPTED_TEXT VARCHAR2(4000);
      ENCRYPTED_RAW RAW(2000);
      DECRYPTED_RAW RAW(2000);
      NUM_KEY_BYTES NUMBER := 256 / 8;
      KEY_BYTES_RAW RAW(32);
      ENCRYPTION_TYPE PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256 +
      DBMS_CRYPTO.CHAIN_CBC +
      DBMS_CRYPTO.PAD_PKCS5;

      BEGIN

      IF P_INPUT_TEXT IS NULL AND P_ENCRYPTED_VALUE IS NULL THEN
      P_ERR_MSG := EXC_HANDLER.DISPLAY_WARNING(226130);
      RAISE CHK_EXCEPTION;
      END IF;

      IF P_INPUT_TEXT IS NOT NULL AND P_ENCRYPTED_VALUE IS NULL THEN

      KEY_BYTES_RAW := DBMS_CRYPTO.RANDOMBYTES(NUM_KEY_BYTES);

      ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC => UTL_I18N.STRING_TO_RAW(P_INPUT_TEXT,
      'AL32UTF8'),
      TYP => ENCRYPTION_TYPE,
      KEY => KEY_BYTES_RAW);

      V_ENCRYPTED_TEXT := ENCRYPTED_RAW;
      P_ENCRYPTED_VALUE := V_ENCRYPTED_TEXT;
      END IF;
      IF P_DECRYPT = 'Y' THEN

      IF V_ENCRYPTED_TEXT IS NOT NULL OR P_ENCRYPTED_VALUE IS NOT NULL THEN
      IF V_ENCRYPTED_TEXT IS NULL THEN
      V_ENCRYPTED_TEXT := P_ENCRYPTED_VALUE;
      END IF;
      KEY_BYTES_RAW := DBMS_CRYPTO.RANDOMBYTES(NUM_KEY_BYTES);
      DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(SRC => V_ENCRYPTED_TEXT,
      TYP => ENCRYPTION_TYPE,
      KEY => KEY_BYTES_RAW);

      V_DECRYPTED_TEXT := UTL_I18N.RAW_TO_CHAR(DECRYPTED_RAW, 'AL32UTF8');
      P_DECRYPTED_VALUE := V_DECRYPTED_TEXT;

      ELSE

      P_ERR_MSG := EXC_HANDLER.DISPLAY_WARNING(226131);
      RAISE CHK_EXCEPTION;

      END IF;
      END IF;

      EXCEPTION
      WHEN CHK_EXCEPTION THEN
      IF P_ERR_MSG IS NULL THEN
      P_ERR_MSG := EXC_HANDLER.DISPLAY_WARNING(226129);
      END IF;
      WHEN OTHERS THEN
      -- p_err_msg := exc_handler.display_warning(226129);
      P_ERR_MSG := SQLERRM;
      END ENCRYPT_HEMA;