As you know, Oracle offers some support for encryption and hashing in the database. Taking advantage of this underlying infrastructure offered by Oracle allows us to accelerate our business considerably.

With Oracle 11g R2, we can see that there are many ways to look at the structure provided to the user.

When we look at the list, we see that Oracle 11g R2 does not have every method. One of these methods is the SHA-256 Hashing algorithm. This support was provided with Oracle 12c, but if we do not have the option to upgrade the database, we can implement the SHA-256 method indirectly.

To implement this method, I will use the ability to create Java classes within the Oracle database. So in summary, I will do the data hashing with the help of a Java code that I write in the database. Later, I will wrap this java code with a PL/SQL function and use it as a normal SQL function.

First, I create the Java class in the database that will implement the SHA-256 method:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED test."calcsha"
   AS import java.security.MessageDigest; 
public class calcsha2 
    {
        static public String fncsha(String inputVal) throws Exception
        {           
            MessageDigest myDigest = MessageDigest.getInstance("SHA-256");        
            myDigest.update(inputVal.getBytes());
            byte[] dataBytes = myDigest.digest();
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < dataBytes.length; i++) {
             sb.append(Integer.toString((dataBytes[i])).substring(1));
            }        

            StringBuffer hexString = new StringBuffer();
            for (int i=0;i<dataBytes.length;i++) {
                String hex=Integer.toHexString(0xff & dataBytes[i]);
                    if(hex.length()==1) hexString.append('0');
                    hexString.append(hex);
            }
            String retParam = hexString.toString();
            return retParam;           
        }    
    }

We created our Java resource in the database. Now let's write a PL/SQL function that will wrap this resource:

CREATE OR REPLACE FUNCTION test.hash_sha256 (txt varchar2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'calcsha2.fncsha(java.lang.String) return String';

We wrote the PL/SQL function. Now we can test it:

select hash_sha256('123456789') from dual;



HASH_SHA256('123456789')                                                        
-----------------------------------------------------------------
15e2b0d3c33891ebb0f1ef609ec419420c20e320ce94c65fbc8c3312448eb225                
1 row selected.

Yes, with this example we have implemented a hashing method that is not supported by Oracle 11g R2.