How to Create Encrypt and Decrypt Function or Procedure in the Oracle database 18c

Creating encryption and decryption functions in Oracle Database 18c can be achieved using the DBMS_CRYPTO package, which provides a set of interfaces to encrypt and decrypt stored data. Here’s a high-level overview of how you can create such functions:



CREATE OR REPLACE FUNCTION encrypt_data (p_plainText IN VARCHAR2, p_key IN VARCHAR2)
RETURN RAW IS
  encrypted_raw RAW(2000);
BEGIN
  encrypted_raw := DBMS_CRYPTO.ENCRYPT(
    src => UTL_I18N.STRING_TO_RAW(p_plainText, 'AL32UTF8'),
    typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
    key => UTL_I18N.STRING_TO_RAW(p_key, 'AL32UTF8')
  );
  RETURN encrypted_raw;
END encrypt_data;

CREATE OR REPLACE FUNCTION decrypt_data (p_encryptedText IN RAW, p_key IN VARCHAR2)
RETURN VARCHAR2 IS
  decrypted_raw RAW(2000);
  decrypted_text VARCHAR2(2000);
BEGIN
  decrypted_raw := DBMS_CRYPTO.DECRYPT(
    src => p_encryptedText,
    typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
    key => UTL_I18N.STRING_TO_RAW(p_key, 'AL32UTF8')
  );
  decrypted_text := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');
  RETURN decrypted_text;
END decrypt_data;

In these functions:
p_plainText is the input text you want to encrypt.
p_encryptedText is the encrypted data you want to decrypt.
p_key is the secret key you’ll use for both encryption and decryption.
DBMS_CRYPTO.ENCRYPT_AES256 specifies the encryption algorithm (AES with a 256-bit key length).
DBMS_CRYPTO.CHAIN_CBC specifies the chaining method (Cipher Block Chaining).
DBMS_CRYPTO.PAD_PKCS5 specifies the padding scheme.
Remember to replace p_key with your actual key and manage it securely. It's also important to note that storing encryption keys within the database can pose a security risk, so consider a secure key management solution.
For more advanced security features, you might want to explore Oracle Advance Security, which provides Transparent Data Encryption (TDE) and other tools to protect sensitive data.
Please ensure that you have the appropriate permissions to use the DBMS_CRYPTO package, as it may require the EXECUTE privilege granted by your database administrator. Also, keep in mind that encryption and decryption should be part of a broader security strategy, including secure key management and access controls.
How to create Package of the above functions.
Package Specification: This is where you declare the functions that will be accessible to the users of the package.

CREATE OR REPLACE PACKAGE crypto_pkg AS
  FUNCTION encrypt_data(p_plainText IN VARCHAR2, p_key IN VARCHAR2)      RETURN RAW;
  FUNCTION decrypt_data(p_encryptedText IN RAW, p_key IN VARCHAR2)         RETURN VARCHAR2;
END crypto_pkg;

Package Body: This is where you define the actual code for the functions declared in the package specification.
CREATE OR REPLACE PACKAGE BODY crypto_pkg AS
  FUNCTION encrypt_data(p_plainText IN VARCHAR2, p_key IN VARCHAR2) RETURN RAW IS
    encrypted_raw RAW(2000);
  BEGIN
    encrypted_raw := DBMS_CRYPTO.ENCRYPT(
      src => UTL_I18N.STRING_TO_RAW(p_plainText, 'AL32UTF8'),
      typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
      key => UTL_I18N.STRING_TO_RAW(p_key, 'AL32UTF8')
    );
    RETURN encrypted_raw;
  END encrypt_data;
  
  FUNCTION decrypt_data(p_encryptedText IN RAW, p_key IN VARCHAR2) RETURN VARCHAR2 IS
    decrypted_raw RAW(2000);
    decrypted_text VARCHAR2(2000);
  BEGIN
    decrypted_raw := DBMS_CRYPTO.DECRYPT(
      src => p_encryptedText,
      typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
      key => UTL_I18N.STRING_TO_RAW(p_key, 'AL32UTF8')
    );
    decrypted_text := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');
    RETURN decrypted_text;
  END decrypt_data;
END crypto_pkg;

While Creating package you might received the error message like:
The error “identifier DBMS_CRYPTO must be declared” indicates that the DBMS_CRYPTO package is not accessible in your session. 
This could be due to several reasons:
The DBMS_CRYPTO package might not be installed in your Oracle database.
You may not have the necessary permissions to access the DBMS_CRYPTO package.
The package name might be misspelled in your code.
To resolve this issue, you can ask your database administrator to grant you the necessary privileges to use the DBMS_CRYPTO package. The typical command to grant execute privileges on DBMS_CRYPTO to a user is:
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO your_username;
Replace your_username with your actual database username. If you need the package to be accessible to all users, the administrator can grant execute privileges to PUBLIC:
An example of a p_key for AES256 encryption, which requires a 256-bit key, could be a string like this:
1234567890abcdef1234567890ABCDEF