Q: Achieving the row level least value along with its column name

You asked: I need to have  a query which will give me the smallest value in a table and the column containing it. Please note there can be any number of columns. All will be number data type.

We said: This can be achieved by unpivoting the result set and then group them to find their minimum value as shown below.

Implicit conversion of Cursor FOR LOOP into BULK FETCH using the PLSQL_OPTIMIZE_LEVEL parameter

Prior to the Oracle version 10gR1, the PL/SQL compiler does not apply any changes to the PL/SQL unit to improve their performance while translating them to the system code. From and after the release 10gR1, Oracle has introduced the initialization parameter PLSQL_OPTIMIZE_LEVEL to instruct the PL/SQL optimizer to automatically rearrange the source code, remove redundant and unreachable code, transform the FOR loops to Bulk Collect, and inline the subroutines for better performance.

The PL/SQL optimizer performs the optimization techniques based on the level value that is set for the PLSQL_OPTIMIZE_LEVEL parameter. The possible values allowed for this parameter are 0, 1, 2 (Default), 3 (11g and higher only). The higher the value set for this parameter, the more the effort the PL/SQL optimizer makes to optimize the PL/SQL units.

The prototype of the PLSQL_OPTIMIZE_LEVEL initialization parameter is shown below,

plsql_optimize_level=<0|1|2|3>;

Where, 2 is the default.

Altering the Optimization Level

The PL/SQL optimization level can be set/altered using the PLSQL_OPTIMIZE_LEVEL initialization parameter either at the SYSTEM or SESSION or at the OBJECT level.

The prototype for setting the optimization level either at the SYSTEM or SESSION level is shown below,

ALTER <SYSTEM | SESSION> SET plsql_optimize_level=<0|1|2|3>;

The prototype for setting the optimization at the object level is shown below,

ALTER <object_type> <object_name> COMPILE plsql_optimize_level = <0|1|2|3>;

If the object is recompiled without the REUSE SETTINGS clause, it takes the database’s current optimization level as its own. With this clause included in the compilation script, the object retains the optimization level it was last compiled with, instead of the database’s current optimization level.

Consider the test procedure for the further demonstration of the PLSQL_OPTIMIZE_LEVEL parameter for the implicit conversion of  LOOPs into BULK FETCHes.

CREATE OR replace PROCEDURE Proc_test 
  authid current_user 
IS 
  l_pi_start_time PLS_INTEGER; 
  l_pi_end_time PLS_INTEGER; 
  l_pi_cpu_start_time PLS_INTEGER; 
  l_pi_cpu_end_time PLS_INTEGER; 
  l_pi_var1 PLS_INTEGER; 
  l_pi_var2 PLS_INTEGER; 
FUNCTION Func_test( ip_pi_var1 PLS_INTEGER) 
  RETURN PLS_INTEGER 
IS 
BEGIN 
  RETURN ip_pi_var1+ip_pi_var1; 
END; 
BEGIN 
  l_pi_start_time :=NULL; 
  l_pi_cpu_start_time :=NULL; 
  l_pi_start_time :=dbms_utility.get_time; 
  l_pi_cpu_start_time :=dbms_utility.get_cpu_time; 
  FOR i IN 
  ( 
         SELECT object_name 
         FROM   dba_objects ) 
  LOOP 
    NULL; 
  END LOOP i; 
  FOR j IN 
  ( 
         SELECT table_name, 
                column_name 
         FROM   dba_tab_cols ) 
  LOOP 
    l_pi_var2 :=0; 
    l_pi_var2 :=1; 
  END LOOP j; 
  l_pi_var2 :=Func_test(10); 
  IF 1 =1 THEN 
    l_pi_var2 :=100; 
  ELSE 
    l_pi_var2:=1000; 
  END IF; 
  NULL; 
  l_pi_end_time :=dbms_utility.get_time; 
  l_pi_cpu_end_time :=dbms_utility.get_cpu_time; 
  dbms_output.put_line( 'Program run time: ' 
  ||(l_pi_end_time l_pi_start_time) /100 
  ||' Seconds'); 
  dbms_output.put_line( 'CPU run time: ' 
  ||(l_pi_cpu_end_time l_pi_cpu_start_time)/100 
  ||' Seconds'); 
END;
/

After the procedure is created, its compilation mode can be verified by querying the USER_PLSQL_OBJECT_SETTINGS data dictionary table as shown below,

SELECT name, 
       TYPE, 
       plsql_optimize_level 
FROM   user_plsql_object_settings 
WHERE  name = 'PROC_TEST';

NAME               TYPE                    PLSQL_OPTIMIZE_LEVEL
----------------   ------------------  ------------------------------------
PROC_TEST    PROCEDURE     2

Note: The default optimization level for the procedure will be the database’s current optimization level.

The PLSQL_OPTIMIZE_LEVEL=2 setting performs the below implicit actions on the package.

1. Redundant code removal
1. The null assignment to the variables, L_PI_START_TIME and L_PI_CPU_START_TIME in the line numbers 16 and 17 respectively, are already null during their creation in the declaration section, thus making these two statements redundant.
2. The statements L_PI_VAR2=0 and L_PI_VAR2=1 are redundant as it assigns a literal integer for every iteration inside the loop. Also, the assignment of the first statement is overridden by the second statement. The PL/SQL compiler finds these properties, takes the first statement (L_PI_VAR2=0) out of the loop structure and skips the second statement (L_PI_VAR2=1) during the program execution.
2. Unreachable code removal
1. The statement  l_pi_var2:=1000; is unreachable in the code and this will be avoided.
3. Implicit conversion of Cursor FOR LOOP into BULK ARRAY FETCH.
1. This setting along with NCOMP mode (PLSQL_CODE_TYPE=NATIVE) converts all the FOR loops into ARRAY fetches with a limit of 100. let us compare the result of the trace file output for the above package with PLSQL_OPTIMIZE_LEVEL>=2 and PLSQL_OPTIMIZE_LEVEL<=1 settings respectively.

The above trace file output is extracted when the procedure code was executed with the PLSQL_OPTIMIZE_LEVEL parameter value set to <=1. The total number of rows processed is 2,24,208 and the total fetch operations performed is 2,24,214. This indicates that approximately 1 row is fetched for each fetch operation, resulting in CPU overhead and context switching.

The above trace file output is extracted when the procedure code was executed with the PLSQL_OPTIMIZE_LEVEL parameter value set to >=2 in the NCOMP mode (PLSQL_CODE_TYPE=NATIVE). The total number of rows processed is 2,24,208 and the total fetches performed is 2,247. This shows that the recursive statements are implicitly converted into ARRAY fetches with a limit of 100.

This approach automatically reduces the context switching and increases the performance of the package.


Thank you,
Boobal Ganesan
Author of Advanced PL/SQL Programming - The Definitive Reference

Neat trick to encrypt your PL/SQL subprograms and still be able to execute it!

We have encrypted our data while storing it in the database, but have we ever tried to encrypt our PL/SQL subprograms while creating them? There are scenarios where we must hide our code from our client for few reasons like stopping anyone from reading and then recreating our code, and much more.

There is an Oracle provided utility to achieve this task, that is the Oracle's WRAP utility. The WRAP utility doesn't encrypt our code but just obfuscates it. Here is an example,

CREATE OR replace PROCEDURE Proc_wrap(ip_date DATE) 
IS 
BEGIN 
    dbms_output.Put_line('The input date is ' 
                         ||ip_date); 
END;
/ 

Consider that we have planned to make the procedure unreadable to anybody who tries to understand it. For this scenario we can use the WRAP utility in Oracle to get this done as below,

SELECT dbms_ddl.wrap(q'[CREATE OR REPLACE PROCEDURE proc_encrypt(     ip_date DATE) IS BEGIN   dbms_output.put_line('the input DATE IS '||ip_date); END; /]') 
FROM   dual;

The above SELECT query returns the obfuscated form of this PROC_WRAP procedure as below,

CREATE OR REPLACE PROCEDURE proc_encrypt wrapped 
a000000 
b2 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
abcd 
7 
71 ae 
g10MIi6Tv0Y9j0IvM3mHKN7UxXIwg5nnm7+fMr2ywFwWFpeuSq7XDNHlVhazaabh0XKpMB/G 
L+pEL2v6Q4hHQ138uSlbibhSm7JK/iiyveeysx0GMCyuJOqygchfwwJHNKwl0wKJ29PKA1Q1 
7eg5J0DoKpwKT20qnCIdpgxFynY=

This obfuscated procedure can be still executed just like a normal procedure.

But wait! Can we Unwrap this obfuscated script to its readable format? The answer is YES!!

There are lots of online utilities like codecrete.net, which does the job. So, this is not the most efficient way to hide our code.

While thinking of a better approach, I tumbled upon the DBMS_CRYPTO package.

Now, what is the DBMS_CRYPTO package and what is it used for?

The DBMS_CRYPTO package encrypts and decrypts messages and was introduced to replace the DBMS_OBFUSCATION_TOOLKIT package by providing support for a range of new secure algorithms with easy usage. The algorithms 3DES_2KEY and MD4 are only provided for backward compatibility and the algorithms 3DES, AES, MD-5, SHA-1, and SHA-2 provide more security compared to them.

The DBMS_CRYPTO package consists of ENCRYPT and DECRYPT subprograms for encrypting and decrypting the string messages respectively.

The prototype of the ENCRYPT and the DECRYPT functions in this package are shown below,

DBMS_CRYPTO.ENCRYPT( src IN RAW, typ IN PLS_INTEGER, KEY IN RAW, iv IN RAW DEFAULT NULL)
RETURN RAW;

DBMS_CRYPTO.DECRYPT( src IN RAW, typ IN PLS_INTEGER, KEY IN RAW, iv IN RAW DEFAULT NULL)
RETURN RAW;

• SRC parameter accepts RAW and LOB input data for the encryption for the overloaded function and the procedures respectively.
• TYP parameter accepts the stream or block ciphers and the modifiers to be used.  
• KEY parameter accepts the encryption key used by the user for the encryption process.  
• IV parameter is also called as the initialization vector. This is for the block ciphers. The default is Null.

Now, consider the above same PROC_WRAP procedure for the encryption process. As a initial step, we must encrypt it using the below block. Note that the procedure is forward declared inside an anonymous block and this block is encrypted as shown below,

DECLARE 
  l_vc_input_string clob:= q'[DECLARE PROCEDURE proc_encrypt(     ip_date DATE) IS BEGIN   dbms_output.put_line('The input date is '||ip_date); END; BEGIN   proc_encrypt(sysdate); END;]'; 
  l_rw_encryption_key raw(32); 
  l_rw_encrypted_string raw(32767); 
  l_pi_encryption_type pls_integer:=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN 
  l_rw_encryption_key   :=dbms_crypto.randombytes(32); 
  l_rw_encrypted_string := DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (l_vc_input_string, 'AL32UTF8'), l_pi_encryption_type, l_rw_encryption_key);
  dbms_output.put_line('Input string before encryption=> '||l_vc_input_string); 
  dbms_output.put_line('Encryption Key=> '||l_rw_encryption_key); 
  dbms_output.put_line('Encrypted Script=> '||l_rw_encrypted_string); 
END;
/

Input string before encryption=> DECLARE

PROCEDURE proc_encrypt(

    ip_date DATE)

IS

BEGIN

  dbms_output.put_line('The input date is '||ip_date);

END;

BEGIN

  proc_encrypt(sysdate);

END;

Encryption Key=> B3012F6470338E0DE91E47B219E175593919CFC41FC5A50E7FD1D768034CD813

Encrypted Script=> A5B2FDEA81B528B5546A11A51EB5368DC750AA8B4E671E62CD977AD4203923E451131934E2BBE11981D75B9BC8F3AF65160F22A6018839C6E48E8FB70C273515A76D67ACD3081FD23BE0D671A93B19CC61DE0DC37E41B7607EABB41DF8397F0D540257B3196071468CEBE0B0F5353F57D8F8B1273F1DC8643F3347B159E5E9F6C1C6A769426F5FE8E3F3BEF8AE5241CF5E67FC33735F30777EB1C0DD231A4909

Here, we have generated the KEY randomly of 32 bytes using the DBMS_CRYPTO.RANDOMBYTES function.

After getting the encrypted form of the procedure, we must create our "client facing procedure" as like below,

CREATE OR REPLACE PROCEDURE proc_test(
    ip_rw_encryption_key raw)
IS
  l_vc_encrypted_code VARCHAR2(32767):='750C744D1CCC6380303EACCD0A9ED8805980C9F3FFDA6EC67688396E1DDDFC87D2B8055E9A9256EF1DD4FAED42AC13569C25FFBE260107AF060651B7836E75E2CC20C19BE8E5AFE3F46FCA37BE0A62EAE0B3B15241D60B11A1E40C3F80A9A4DA6A575614B1BB4AF98A222919D1D654996D4567F475D99FD53021433A3940A38AE7F14B7C0EA4C374956D1BBDDA5C6145C1E0B000FFCF1622F3250160EC721C65';
  l_pi_encryption_type pls_integer   :=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
  EXECUTE immediate UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.DECRYPT(l_vc_encrypted_code, l_pi_encryption_type, ip_rw_encryption_key), 'AL32UTF8');
END;

/

The above procedure just decrypts our encrypted message and then executes it using the EXECUTE IMMEDIATE clause, considering it as a dynamic code. Not everyone can decrypt and run the procedure successfully as the KEY which is used for encryption is required for the decryption also and this KEY is to be passed as input to the procedure.

If the KEY is wrong, the procedure exits with an error as below,

EXEC proc_test('ABCDEFFBE508071AA1EC5FF4D301A5DF12572742BCA5420C78B2');

 Error report -
ORA-28234: key length too short
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 44
ORA-06512: at "SYS.PROC_TEST", line 7
ORA-06512: at line 1
28234. 00000 -  "key length too short"

When we execute the procedure with the right KEY,

EXEC proc_test('6A0B656248D9955AEEFBE508071AA1EC5FF4D301A5DF12572742BCA5420C78B2');

Result:
The input date is 10-OCT-17

Thus, with this trick, we have made sure that our PL/SQL code is unreadable and can be accessed only if we have the right KEY.

Thank you,

Boobal Ganesan

Author of Advanced PL/SQL Programming - The Definitive Reference

Duplication check on a BLOB column containing image files

We can store multimedia files like music, image, or video files either inside the database (BLOB type) or outside the database (BFILE type).

BLOB:

The BLOB datatypes are read-write binary large datatypes, that lets us store large binary files like video, audio, and PDF documents in the database. The maximum size of these types can range from 8 TB to 128 TB based on the DB_BLOCK_SIZE initialization parameter, which is set during the database creation in the init.ora file. The formula for calculating the maximum size of the LOB type is (4 GB - 1) * DB_BLOCK_SIZE value. Once this parameter is set, there is no going back for any change except for re-creating the database. The maximum value that can be set for the DB_BLOCK_SIZE parameter is 16 KB for Linux X86 environments and 32 KB for other environments. However, setting this parameter’s value to its maximum may affect the I/O operations.

BFILE: Binary File

BFILE is a read-only datatype that the database uses to access external binary files with a maximum size limit of 4 GB. Even though the BFILE and the BLOB types intends to store binary data, they both work differently from each other. Both the BFILE and BLOB column stores only the locators which point to the actual data that resides outside the ROW, but the BLOB type stores its binary data inside the database whereas the BFILE type stores its data outside the database in the OS layer. The BFILE type is capable of storing external binary files which can be in PDF, GIF, JPEG, MPEG, MPEG2, text, or other formats.

Note: The External LOB type doesn’t support UPDATE or DELETE operations as they are READ-ONLY.

Consider the below example for creating a table which holds a sequence number and a BLOB column to hold image files.

CREATE TABLE mytable 
  ( 
     seq        NUMBER, 
     image_file BLOB 
  );

/

When we want to maintain uniqueness for a non-LOB column, we can either create an unique index, primary key (which by default creates an unique index), or an unique key (which by default creates an unique index).

Can we also create a similar unique index, primary key or an unique key for a LOB column?

The answer is NO!

Oracle does not allow us to create any of those for a LOB column. Let's check it out using the below examples.

• Creating an unique index on a LOB column

CREATE UNIQUE INDEX idx_image_file ON mytable(image_file);

Error report -SQL Error: ORA-02327: cannot create index on expression with datatype LOB
02327. 00000 -  "cannot create index on expression with datatype %s"
*Cause:    An attempt was made to create an index on a non-indexable
           expression.

• Creating a unique constraint on a LOB column

ALTER TABLE mytable ADD CONSTRAINT uk_image_file UNIQUE (image_file); 

Error report -
SQL Error: ORA-02329: column of datatype LOB cannot be unique or a primary key
02329. 00000 -  "column of datatype %s cannot be unique or a primary key"
*Cause:    An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
           on a column of datatype VARRAY, nested table, object, LOB, FILE
           or REF.

So, how can we achieve the uniqueness for a LOB column? There is a neat trick to do that.

We can create another column to our table which can be used for holding the hash value of the image and check uniqueness for that column. The hash value is actually a compact numeric version of the image file which will be same for same input image. By checking uniqueness on this column, we can make sure that the image is not duplicated.

Let's try this using the below examples.

Firstly, we are adding a new column to our MYTABLE. This new column is a virtual column which will return the hash value of the IMAGE_FILE column.

ALTER TABLE mytable ADD (hash_value generated always AS 

   (dbms_crypto.hash(image_file, 2)) virtual);

The second parameter to the DBMS_CRYPTO.HASH function accepts any of the below hash functions.

• HASH_MD4    = 1
• HASH_MD5    = 2
• HASH_SH1     = 3
• HASH_SH256 = 4
• HASH_SH384 = 5
• HASH_SH512 = 6

After the column is added, we must create a unique index on this virtual column as below,

CREATE UNIQUE INDEX idx_hash_value ON mytable(hash_value);

We must now create a directory object for the images' location as below,

CREATE OR replace directory dir_pictures AS 'F:\Pictures';

Now, we are all set to insert the image file into our table using the below script.

DECLARE 
    l_bf_src        BFILE := Bfilename('DIR_PICTURES', 'Batman.JPG'); 
    l_bl_dest       BLOB := Empty_blob(); 
    l_i_src_offset  INTEGER := 1; 
    l_i_dest_offset INTEGER := 1; 
BEGIN 
    dbms_lob.Createtemporary(l_bl_dest, TRUE, dbms_lob.SESSION); 

    dbms_lob.OPEN(l_bf_src, dbms_lob.file_readonly); 

    dbms_lob.OPEN(l_bl_dest, dbms_lob.lob_readwrite); 

    dbms_lob.Loadblobfromfile(l_bl_dest, l_bf_src, dbms_lob.lobmaxsize, 
    l_i_dest_offset, l_i_src_offset); 

    INSERT INTO mytable 
                (seq, 
                 image_file) 
    VALUES      (1, 
                 l_bl_dest ); 

         commit;

    dbms_output.Put_line('Total size of the image file is ' 
                         ||dbms_lob.Getlength(l_bl_dest) 
                         ||' Bytes'); 

    dbms_lob.CLOSE(l_bf_src); 

    dbms_lob.CLOSE(l_bl_dest); 
END; 
/ 

We can now check our image file using any IDE. I am currently using the SQL DEVELOPER 4.1.3 version for checking the inserted image file.

Now, when we rerun the same PL/SQL block again to insert the same image, we get the below unique index violation.

Error report -
ORA-00001: unique constraint (SYS.IDX_HASH_VALUE) violated
ORA-06512: at line 11
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

We have found a workaround to check uniqueness on the LOB columns in this blog.


Thank you,
Boobal Ganesan