This discussion is archived
6 Replies Latest reply: Nov 20, 2012 1:38 AM by Hema RSS

DBMS_CRYPTO.DECRYPT issue

Hema Newbie
Currently Being Moderated
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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points