Oracle Encryption - encrypt data

Permissions on Oracle package DBMS_CRYPTO
========================================

GRANT EXECUTE ON DBMS_CRYPTO TO TEST_USER;

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

Create a wrapper package.
p_text - is the encrypted value

p_key - a key passed from application
The encrypted value - is stored in database instead of the real value

The key - should be stored outside of the database.
========================================

CREATE OR REPLACE PACKAGE DBMS_CRYPT AS

  FUNCTION encrypt_text (p_text IN VARCHAR2, 

                         p_key  IN VARCHAR2) RETURN VARCHAR2;

 

  FUNCTION decrypt_text (p_text IN VARCHAR2, 

                         p_key  IN VARCHAR2) RETURN VARCHAR2;

END DBMS_CRYPT;

/

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

CREATE OR REPLACE PACKAGE BODY DBMS_CRYPT AS

FUNCTION encrypt_text (p_text IN VARCHAR2,

                       p_key  IN VARCHAR2) RETURN VARCHAR2 IS

 v_return_value  VARCHAR2(1000);

BEGIN

  SELECT DBMS_CRYPTO.encrypt(UTL_RAW.cast_to_raw(p_text), 4353, UTL_RAW.cast_to_raw(p_key))

  INTO v_return_value

  FROM DUAL;

  RETURN v_return_value;

END;

FUNCTION decrypt_text (p_text IN VARCHAR2,

                       p_key  IN VARCHAR2) RETURN VARCHAR2 IS

 v_return_value  VARCHAR2(1000);

BEGIN

  SELECT UTL_RAW.cast_to_varchar2(SYS.DBMS_CRYPTO.decrypt(p_text, 4353, UTL_RAW.cast_to_raw(p_key)))

  INTO v_return_value

  FROM DUAL;

  RETURN v_return_value;

END;

END DBMS_CRYPT;

/

Usage 
========================================

SELECT DBMS_CRYPT.encrypt_text(p_text => TO_CHAR(1234), p_key => 'Apr2026@Tomia') FROM DUAL;

SELECT DBMS_CRYPT.decrypt_text(p_text => 'B067B800BC18271C', p_key => 'Apr2026@Tomia') FROM DUAL;

Usage with table
========================================

CREATE TABLE TOKEN_USAGE 

(id               NUMBER,

 usage_name       VARCHAR2(100),

 usage_value      VARCHAR2(1000),

 ts_last_modified DATE DEFAULT SYSDATE

) TABLESPACE IGT_TABLE;

ALTER TABLE TOKEN_USAGE ADD CONSTRAINT TOKEN_USAGE_PK PRIMARY KEY  (id) USING INDEX TABLESPACE IGT_INDEX;

INSERT INTO TOKEN_USAGE (id, usage_name, usage_value) VALUES (1,'Tomia Chat Bot',DBMS_CRYPT.encrypt_text(p_text => TO_CHAR(1234), p_key => 'Apr2026@Tomia'));

SELECT id, usage_name, usage_value FROM TOKEN_USAGE;

        ID USAGE_NAME     USAGE_VALUE

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

         1 Tomia Chat Bot B067B800BC18271C

SELECT id, usage_name, DBMS_CRYPT.decrypt_text(p_text => usage_value, p_key => 'Apr2026@Tomia') as usage_value, ts_last_modified FROM TOKEN_USAGE

WHERE id=1;S

        ID USAGE_NAME     USAGE_VALUE

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

         1 Tomia Chat Bot 1234