How to encrypt/decrypt a field in Oracle

Oracle provides package DBMS_OBFUSCATION_TOOLKIT which can be used for encrypting/decrypting database fields.

There are a couple of things to remember about this package though:

1. The package provides PROCEDURES to perform encryption/decryption. This alone may not be very useful if you need to use plain SQL. So I provide two functions to wrap these procedures (see below).
2. The length of both the value to be encrypted and the encryption key must be exact multiples of 8. Therefore you need to pad the value with somethig (i.e blanks) to reach the desired length.
   

These are the functions:

CREATE OR REPLACE function encrypt_val(
   input_string       VARCHAR2,
   key_string         VARCHAR2 ) return varchar2 as
   encrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string => input_string, key_string => key_string,
                                        encrypted_string => encrypted_string);
   --DBMS_OUTPUT.put_line ('encrypted hex value : ' || RAWTOHEX (UTL_RAW.cast_to_raw (encrypted_string)));
   return encrypted_string;
END;
/

CREATE OR REPLACE FUNCTION decrypt_val (encrypted_string VARCHAR2, key_string VARCHAR2)
   RETURN VARCHAR2
AS
   decrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string => encrypted_string, key_string => key_string,
                                        decrypted_string => decrypted_string);
--   DBMS_OUTPUT.put_line ('decrypted string output : ' || decrypted_string);
   RETURN decrypted_string;
END;
/

Example:

The following statement reads a table containing users and encrypts the username (i.e. in order to generate a default password). The actual value to be encrypted is the user name right padded with blanks so that the total length is a multiple of 8.

SELECT user_nm val,
       encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
FROM   ref_user;
        
To retrieve the decrypted value, you need to both decrypt and TRIM:

SELECT val, enc_val, TRIM (decrypt_val (enc_val, 'mykey678')) dec_val
FROM   (
        SELECT user_nm val,
               encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
        FROM   ref_user       
        );

How to make indexes UNUSABLE

The following sample procedure makes indexes UNUSABLE.

This can be helpful when trying to load many thousands or millions of records in a table and you want to avoid index maintenance in order to speed up the loading process.

Make sure to set SKIP_UNUSABLE_INDEXES to TRUE before trying to insert into any table with UNUSABLE indexes. This can be done by including the following statement in your PL/SQL code:

EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';

This procedure can handle both partitioned and non-partitioned indexes.

   PROCEDURE set_indexes_unusable
   AS
      idxs       pkg_mig_exec.vtable;
      tot_idxs   PLS_INTEGER;
      stmnt      VARCHAR2 (256);
   BEGIN
      SELECT 'alter index ' || owner || '.' || index_name || ' unusable'
      BULK COLLECT INTO idxs
      FROM   all_indexes
      WHERE  owner = 'MY_SCHEMA'
      AND    table_name IN
                  ('TABLE1', 'TABLE2', 'TABLE3')
      AND    partitioned = 'NO'
      AND    uniqueness <> 'UNIQUE';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;

-- Handle partitioned indexes

      SELECT index_owner || '.' || index_name
      BULK COLLECT INTO idxs
      FROM   all_ind_partitions
      WHERE  index_owner = 'MY_SCHEMA' AND partition_name = 'PARTITION_A';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         stmnt :=
            'alter index ' || idxs (i)
            || ' modify partition PARTITION_A unusable';
         pkg_mig_utils.log_info ('Executing : ' || stmnt);

         EXECUTE IMMEDIATE stmnt;
      END LOOP;
   END;

Unusable indexes must be rebuilt in order to become usable again.

PROCEDURE rebuild_unusable_indexes
   AS
      idxs       stmnttable;
      tot_idxs   PLS_INTEGER;
      stmnt      VARCHAR2 (1024);
   BEGIN
      SELECT 'alter index ' || owner || '.' || index_name || ' rebuild'
      BULK COLLECT INTO idxs
      FROM   all_indexes
      WHERE  owner = 'MY_SCHEMA'
      AND    status = 'UNUSABLE'
      AND    partitioned = 'NO';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;

      SELECT 'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || partition_name
      BULK COLLECT INTO idxs
      FROM   all_ind_partitions a
      WHERE  index_owner = 'MY_SCHEMA'
      AND    partition_name = 'PARTITION_A'
      AND    status = 'UNUSABLE';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;
   END;

How to estimate table stats (table only)

DBMS_STATS.gather_table_stats (
        ownname => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'),
        tabname => '',
        estimate_percent => SYS.DBMS_STATS.auto_sample_size,
        method_opt => 'FOR ALL COLUMNS SIZE 1 ',
        DEGREE => NULL, CASCADE => FALSE,
        no_invalidate => FALSE);

Script to rebuild all indexes

DECLARE
   v_tbl_nm    VARCHAR2 (32) := '';
--
   TYPE oranametable IS TABLE OF VARCHAR2 (32);

   indxs       oranametable;
   tot_indxs   PLS_INTEGER;
BEGIN
   SELECT index_name
   BULK COLLECT INTO indxs
   FROM   all_indexes a
   WHERE  a.owner = SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
   AND    a.table_name = v_tbl_nm;

   tot_indxs := SQL%ROWCOUNT;

   FOR i IN 1 .. tot_indxs
   LOOP
      EXECUTE IMMEDIATE 'alter index ' || indxs (i) || ' rebuild';
   END LOOP;
END;

How (and when) to use PIPELINED PL/SQL functions in Oracle

There have been several cases where I needed to return a list of records after performing complex processing in Oracle.

I found out that the preferred method to do it is by using PIPELINED PL/SQL functions.

The last time I have done this was a few weeks ago where I had to “dig” into a log of user actions and extract information about user’s activity on a daily basis.

Other cases where I have used the same technique in the past were involving:

• Complex validations and return of validation results (warnings, errors, etc.).
• Tree walking and joining with other tables.

In all these cases I needed to query several tables and use temporary tables to collect intermediate results.

An example of the use of PIPELINED functions can be found here.

Here I summarize the steps must be taken when I have to implement something similar.

1.       Create an Object Type with the necessary fields.

CREATE OR REPLACE TYPE TYP_DAILY_USER_TOTALS AS OBJECT (
   user_nm            VARCHAR2 (16),
   act_dt             DATE,
   tot_proc           NUMBER (6),
   tot_in_progress    NUMBER (6),
   tot_cancelled      NUMBER (6),
   tot_failed         NUMBER (6),
   tot_compl          NUMBER (6),
   avg_assign_tm      NUMBER (6),
   median_assign_tm   NUMBER (6),
   start_tm           DATE,
   end_tm             DATE
);

2.       Create a collection

CREATE OR REPLACE TYPE TYP_DAILY_USER_TOTALS_COL AS TABLE OF typ_daily_user_totals;

3.       Implement a function returning a collection

CREATE OR REPLACE FUNCTION get_daily_user_totals(v_user_nm varchar2)
   RETURN typ_daily_user_totals_col PIPELINED
AS
   PRAGMA AUTONOMOUS_TRANSACTION;

   TYPE daily_totals_rec IS RECORD (
      act_dt             DATE,
      tot_proc           NUMBER (6),
      tot_in_progress    NUMBER (6),
      tot_cancelled      NUMBER (6),
      tot_failed         NUMBER (6),
      tot_compl          NUMBER (6),
      avg_assign_tm      NUMBER (6),
      median_assign_tm   NUMBER (6),
      start_tm           DATE,
      end_tm             DATE
   );

   TYPE daily_totals_tbl IS TABLE OF daily_totals_rec;
   daily_totals   daily_totals_tbl;
BEGIN

/*
   Do whatever is needed to collect your results, i.e. in a global temporary table
*/
 
   . . . . . . .

/*
   Collect your results in a PL/SQL table
*/
 
   SELECT   act_dt,
            tot_proc,
            tot_in_progress,
            tot_cancelled,
            tot_failed,
            tot_compl,
            avg_assign_tm,
            median_assign_tm,
            start_tm,
            end_tm
   BULK COLLECT INTO daily_totals
   FROM  g_daily_user_totals;

/*
   Commit or rollback to clean up the temporary tables and close the autonomous transaction
*/

   ROLLBACK;

/*
   Loop to pipe collected results
*/

   FOR i IN 1 .. daily_totals.COUNT ()
   LOOP

      PIPE ROW (NEW typ_daily_user_totals (
           v_user_nm,
           daily_totals (i).act_dt,
           daily_totals (i).tot_proc,
           daily_totals (i).tot_in_progress,
           daily_totals (i).tot_cancelled,
           daily_totals (i).tot_failed,
           daily_totals (i).tot_compl,
           daily_totals (i).avg_assign_tm,
           daily_totals (i).median_assign_tm,
           daily_totals (i).start_tm,
           daily_totals (i).end_tm));
   END LOOP;

   RETURN;
END;


4.       Select from the function

    SELECT user_nm, act_dt, tot_proc, tot_in_progress, tot_cancelled, tot_failed, tot_compl,
           avg_assign_tm, median_assign_tm, start_tm, end_tm
    FROM   TABLE (get_daily_user_totals(:aUser));

Some notes on the above:

1. If it is necessary to write into any table (as in the example above), then we must declare our function to work as an AUTONOMOUS TRANSACTION (see PRAGMA AUTONOMOUS_TRANSACTION).
2. The TABLE resulting from the select statement above can be joined with other tables and, possibly, create a view.
3. There have been cases where I have used a view (to make life of Java developers easier). In this case, if our PIPELINED function requires a parameter, then this can be passed via a context (using SYS_CONTEXT). I do not have an example ready for this, but I remember an article in Oracle Magazine describing this technique.