Spring – Quartz scheduled job : With single Thread

In one of our current integration projects, where we were using Spring, we had a Spring bean which was constantly running every 20 to 30 seconds pooling the database and fetching new status of records. These statuses would be constantly sent to our client as and when new status came up. This Spring bean is a simple Quartz(From opensymphony) bean of the class MethodInvokingJobDetailFactoryBean.

<bean id="jobBean" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
     <property name="targetObject" ref="queryDatabaseServiceTX" />
     <property name="targetMethod" value="getStatus" />
</bean>

Here the queryDatabaseServiceTX(Spring bean) is the business service with the method getStatus that actually retrieves the status and performs some updates also.

After this we define two more beans in the Spring context

<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
    <property name="triggers">
        <list>
            <ref bean="simpleTrigger"/>
        </list>
    </property>
</bean>

<bean id="simpleTrigger" class="org.springframework.scheduling.quartz.SimpleTriggerBean">
    <property name="jobDetail" ref=" jobBean " />
    <property name="startDelay" value="10000" />
    <property name="repeatInterval" value="30000" />
</bean>

Recently we started noticing that multiple threads were instantiated and the business service was called multiple times. This eventually was causing multiple updates to occur. Our design was such that the scheduler should run sequentially, repeating itself every 30 seconds. However sometimes when the database is under too much load the query execution itself takes more than 30 seconds and the next thread is spawned causing dead lock kind of situations.

To overcome this we found a silver bullet. Following was the modification done.

<bean id="jobBean" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
   <property name="targetObject" ref="queryDatabaseServiceTX" />
   <property name="targetMethod" value="getStatus" />
   <property name="concurrent" value="false" />
</bean>

Setting the attribute concurrent to false ensures that no more threads are spawned until the first one is successfully completed.

Deploying Wicket Application in OC4J

I have been working with wicket for the last one year. It is one of the best web frameworks. In our organization, we are using Oracle Application Sever (OAS) to deploy our applications. We were using OAS 9.0.4.3 and we had no issues with wicket. Recently we upgraded our OAS to 10.1.2.0.2 and then issues started to appear one after the other.

The first issue that we were facing was with securing the application. We are using Oracle LDAP as our user manager for Authorization and Oracle SSO (Single Sign On) for authentication. The authentication and authorization are working fine if the URL used to access the application is not terminated by a slash “/”. Users are asked to login and only authorized user can access the application. However authentication and authorization are bypassed when the URL used to access the application is terminated with a slash “/”. Users are not even asked to login to the system to access the application.

After debugging and investigating, we noticed that if we use wicket servlet to load our application instead of wicket filter, then the security issue is resolved. However that brought up another issue that is there even in OAS 9.0.4.

The second issue is that, when using wicket servlet, the context which is specified in the servlet mapping in web.xml is omitted by OAS. The same problem was reported in wicket forum [1] and [2]. The context was omitted while sending the response back to the client only and not while sending the request to the server. The reason for this behavior is still ambiguous but we have developed a workaround for the issue. We have implemented a filter that adds the omitted context to the response. Here is our sample filter code

package com.datel.sample.webapplication;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpServletResponseWrapper;

public class ContextFilter implements Filter{

	private FilterConfig config;

	//Used to store the servlet mapping URL that is defined in the web.xml
	private String servletMappingContext;

	public void destroy() {
		config = null;
		servletMappingContext = null;
	}

	public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
		HttpServletRequest httpRequest = (HttpServletRequest)request;
		HttpServletResponse httpResponse = (HttpServletResponse)response;

		if(servletMappingContext == null || servletMappingContext.length()==0)
		{
			//The getRequestURI returns the full context i.e. /sampleWicket/app/
			setServletMappingContext(httpRequest.getRequestURI());
		}

		myServletResponse newResponse = new myServletResponse(httpResponse);
		chain.doFilter(request, newResponse);
	}

	private void setServletMappingContext(String requestURI) {
		//1. remove last / if it exists
		if (requestURI.endsWith("/"))
			requestURI = requestURI.substring(0, requestURI.length()-1);

		//2. get only the servlet mapping defined in web.xml i.e /app
		requestURI = requestURI.substring(requestURI.lastIndexOf("/"));

		//3. remove the prefixed /
		requestURI = requestURI.substring(1);

		//4. append the / at the end
		servletMappingContext = requestURI+="/";
	}

	public void init(FilterConfig config) throws ServletException {
		this.config = config;
	}

	class myServletResponse extends HttpServletResponseWrapper
	{
		public myServletResponse(HttpServletResponse arg0) {
			super(arg0);
		}
		public void sendRedirect(String arg0) throws IOException {
			// Add the context to the query string
			super.sendRedirect(servletMappingContext+arg0);
		}
	}
}

In the web.xml you need to add the filter:

 
<filter>        
     <filter-name>contextFilter</filter-name>
     <filter-class>com.datel.sample.webapplication.ContextFilter</filter-class>
</filter>
<filter-mapping>        
     <filter-name>contextFilter</filter-name>       
     <url-pattern>/*</url-pattern>
</filter-mapping>  

Please note that this filter will always add the context to the query string and thus it will only work with Application servers that omit the context. It will not work with Tomcat for example. This is because in the response we don’t have handle to the full URL. We have a handle only to the query string.

Due to that we have reported the first issue, Securing Web Applications, to Oracle Support. Oracle was able to reproduce the problem and they agreed to file it as a bug and they suggested to download the latest patchset 5983622 Oracle Fusion Middleware to resolve the problem. We have done so and our application is now secured without the need to go for wicket servlet. We were using Wicket 1.3.4.

[1] http://www.nabble.com/wicket-servlet-mapping-to-subdirectory-td18012945.html#a18012945
[1] http://www.nabble.com/Wicket-cannot-work-on-OC4J-(ias-10g)–td16738242.html#a16744020

Thread-Safe and Concurrency

Have you ever faced strange or unexpected problems with your application after putting it into production? Is the same code works just fine when you test it? Is the problem just too hard to even simulate?Well if the problem does not appear when you test it but its there in production and assuming you have a staging environment i.e. your test environment is the same as your production environment. Then the first question is, is your application thread-safe? When you test it, it is only one user but in production is it more than one user?

The only way to ensure that your application is thread-safe is by running more than one thread at the same time. This is a very simple test but it can clear out very serious issues. Here is how you can test it:

Public class theardTest
{
	Public static void main(String[] args)
	{
		Thread userOne = new Thread()
		{
                      Public void run()
                      {
	                 //call your application here
                      }
                  };

                  Thread userTwo = new Thread()
                  {
                        Public void run()
                        {
                               //call your application here
                        }
                  };

                  userOne.start();
                  userTwo.start();
         }
}

If your application is failing here and there, then it is not a thread-safe application and it can not be accessed by more than one user concurrently. There could be many reasons for that like improper user of static modifier, collection iteration, etc.

If your application should be accessed concurrently, then it should be thread-safe.

Barcode Encoding in JAVA

Generating barcodes, be it 1D or 2D, have never been easier. I will be using the Barbecue open source project hosted on sourceforge to generate a 1D barcode. After the barcode is generated I will decode the barcode by uploading it to a website whose service we shall use. However its also very simple to write a barcode decoder which shall be a topic of discussion later.

How to ….

First of all you need to download the Barbecue . Following are the jars added in my classpath

• barbecue-1.5-beta1.jar
• jdom.jar

Following is the code to generate a sample barcode

package com.datel.barcode;

import java.io.File;

import net.sourceforge.barbecue.Barcode;
import net.sourceforge.barbecue.BarcodeFactory;
import net.sourceforge.barbecue.BarcodeImageHandler;

public class GenerateBarcode {

	public static void main(String[] args) throws Exception {

		Barcode barcode = BarcodeFactory.createCode128("HelloWorld:0505124456");
        File f = new File("c:\\barcode.png");
        BarcodeImageHandler.savePNG(barcode, f);

	}

}

Now you should find a file named barcode.png in you file system in C: drive. It should look like this

Barcode

We shall try to decode this image the easy way by uploading it to a website. Navigate to this website: ZXing This is a website hosted by the ZXing project team who have a java api for decoding barcode images. Upload your image and click Submit Query. You should see this as the output

HelloWorld:0505124456

A few words….

Even the Barbecue encoder is only beta and has been beta since May 2007. Also I really don’t comprehend why in the world these cool guys would make the Barcode class extend from javax.swing.JComponent. This really drives me mad to see such a thing but the BarcodeImageHandler class comes to the rescue. Anyways keep up the good work.

Humble Insight…

After my extensive search on the web I could not find a single open source java barcode encoder worth mentioning. I would be very much happy if any one could mention a good open source barcode encoder. But there were quite a few decoders though. Sadly to mention, if you want to encode data into barcodes on production applications you will have to rely on some commercial products out there. They are very good at what they do and are can very easily be integrated into your application. Many have facilities for resizing/rotating your image and can be very handy at times.

Using Barbecue looks all nice, simple and dandy but when you start getting requirements from your customers saying “Hey I want to put 100 MB of data in a 2D barcode” then things get hard. Although that’s a bit exaggerated, I know cases where my colleagues were asked for all weird kinds of things to be done with barcodes. To them I have only one thing to say… “Technology was made to help mankind and was not made to be abused by mankind”.

There is tremendous potential in this barcode industry and advanced encoding techniques keep coming out.

Network Data Encryption and Integrity for Thin JDBC Clients

Today we shall look into a nice feature on how to secure our JDBC connections. It’s generally a standard to make JDBC connections in java applications to connect to database. We mostly have web applications running in a web container. These web applications make JDBC connections to the databases to retrieve data for the applications. How many of us have really thought how secure these JDBC calls are. Well they aren’t actually. Like any other insecure network protocol call, so are these JDBC connections. However these JDBC connections can be very easily encrypted. Well this discussion is purely based on Oracle databases and wouldn’t work with other drivers. However, if any one finds out about other databases please let me know.

Before we go any further let us see some of the security threats we face when we use normal JDBC connections

• Eavesdropping and Data Theft
• Data Tampering
• Falsifying User Identities
• Password-Related Threats

Data flowing over the network is prone to network sniffers, be it insider or outside you company. Any one can pick up these data packets and tamper with them.

Imagine you send a query for transfer of balance for $100 from Account A to Account B. Someone could intercept this request and just play around with the zeroes (This is only hypothetical. I sure shall try this out).

Since a picture is worth a thousand words, let us look at a typical application environment.

Application Infrastructure

I believe now it is clear as to how your JDBC connections can get insecure.

Now we shall look into how to get around these issues. To solve these security challenges we have two special features that can be used

• Data Encryption
• Data Integrity

In simple lay man terms, it means that your JDBC calls will be transparently encrypted using standard encryption algorithms, there by making your data transfer secure. Secondly your data will be hashed to form messages digests, there by preventing data tampering. So that’s it. Let us get into action.

In order to encrypt and hash the data, certain configuration has to be done both at the client end and the server end. We shall look into them one at a time.

Client Configuration

This is one the easiest steps. You will only have to tell your JDBC driver to encrypt and digest the message as and when you make JDBC calls. Lets looks at a sample code

package com.datel.secure;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.net.ano.*;

public class StartAction {

    // The machine on which the database resides
    public final static String hostName    = "127.0.0.1";
    // The TNS listener port
    public final static String port  = "1521";
    // The database name (SID)
    public final static String databaseSID = "home";
    // The database User login
    public final static String userName    = "scott";
    // The database user password
    public final static String password    = "tiger";	 

    Connection connection;  

    public static void main(String[] args) {
		// TODO Auto-generated method stub
		new StartAction().makeDbConnection();
	}

	 public void makeDbConnection(){
		    try{

		      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

		      // Form the database connect string using the connection parameters
		      // as specified in ConnectionParams.java
		      String dbConnectString ="@"+StartAction.hostName+":"+
		          							StartAction.port+":"+
		          							StartAction.databaseSID;

		      Properties prop = new Properties();

		      // Set the user name and password property
		      prop.put("user", StartAction.userName);
		      prop.put("password", StartAction.password);

		      int level = AnoServices.REQUIRED;

		      // Set the Client encryption level
		      prop.put("oracle.net.encryption_client", Service.getLevelString(level));

		      // Set the Client encryption selected list
		      prop.put("oracle.net.encryption_types_client", "(RC4_40)");

		      // Set the Client integrity level
		      prop.put("oracle.net.crypto_checksum_client", Service.getLevelString(level));

		      // Set the client integrity selected list
		      prop.put("oracle.net.crypto_checksum_types_client", "( MD5 )");

		      connection = DriverManager.getConnection("jdbc:oracle:thin:"+dbConnectString,prop);

		      if ( connection != null )
		      {

		    	  Statement stmt = connection.createStatement();
		    	  ResultSet rs;
		    	  rs = stmt.executeQuery("SELECT * from EMP"); 

		    	  while (rs.next()) {
		              String lastName = rs.getString("sal");
		              System.out.println(lastName + "\n");
		           }

		      }

		    } catch(SQLException ex){
		    	//Trap SQL errors

		    	ex.printStackTrace();

		    } catch(Exception ex){
		    	ex.printStackTrace();
		    }

		  }

}

• The first thing that you would have noticed different is the client encryption level. I have set it to REQUIRED by setting the property oracle.net.encryption_client. This value signifies, encryption is required and mandated at the client end without which the connection would fail. It supports four different legal values. They are REJECTED, ACCEPTED, REQUESTED and REQUIRED. Similar configuration is required at the server end which we shall see in the server configuration. That means we can have 16 different combinations between client and server. I prefer to leave both the Client and the Server as REQUIRED, because it enforces the encryption security check and cannot be bypassed.

• The next property that is set is oracle.net.encryption_types_client.This tells the JDBC driver to follow RC4_40 encryption standard. There are other encryption standards like DES and Triple DES. Possible values are RC4_256; RC4_128; RC4_56; RC4_40; 3DES112; 3DES168

• The next property set is oracle.net.crypto_checksum_client. This tells the JDBC driver to generate checksums for the data and its values can also be REJECTED; ACCEPTED; REQUESTED; REQUIRED.

• The last property that is set is oracle.net.crypto_checksum_types_client. This tells the JDBC diver to follow the MD5 hashing algorithm to form checksums.

If you were to run this code you would obviously get an error message which states that encryption is not enabled on the server. So let us step into the server configuration.

Server Configuration

On your database server access the Oracle Net Manager. You could find this at Start->Programs->OracleHome->Configuration and Migration Tools->Oracle Net Manger on your database server. Actually this Oracle utility does nothing special but writes to your SQLNET.ORA .You could directly edit this file or else use this utility. So fire up the application and you would see a screen such as this

Oracle Net Manager

Now click on the Profile properties which will bring up another screen. Choose Oracle Advance Security from the drop down list.

Set the encryption type to Required and Selected methods to RC4_40. If you recall this is the same encryption setting that was enabled on the client. The noticeable difference is the encryption seed. Set some hard difficult string in this field. This is used by oracle to encrypt the data in its algorithms. Next chose the integrity tab which fires up this screen

Here we select the similar configurations as the client. Checksum level is Required and the Selected method is MD5

Now go to File->Save Network Configuration. If you want to see where these properties are save go to you Oracle Home folder. Under Network->Admin there will be a file called SQLNET.ORA which would look something like follows

# sqlnet.ora Network Configuration File: c:\OraHome_1\NETWORK\ADMIN\sqlnet.ora

# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (MD5)

SQLNET.ENCRYPTION_SERVER = required

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.CRYPTO_SEED = somerandomlonghardstring

SQLNET.ENCRYPTION_TYPES_SERVER= (RC4_40)

SQLNET.CRYPTO_CHECKSUM_SERVER = required

There are ways to check if your encryption is working or not. I shall give you a hint thought. You can just check your database trace files. It would log something as follows

…
[29- -2008 15:49:06:398] nau_adi: exit
[29- -2008 15:49:06:398] na_tns: authentication is not active
[29- -2008 15:49:06:398] na_tns: encryption is active, using RC4_40
[29- -2008 15:49:06:398] na_tns: crypto-checksumming is active, using MD5
[29- -2008 15:49:06:398] na_tns: exit
….

Now fire up your application and enjoy transparent network encryption.

.Net Encryption and Oracle Decryption

Performing encryption and decryption using the same language might be straight forward and many resources can be found in this regard. But when it comes to across languages, mismatches appear even if you are using the same algorithm to encrypt and decrypt. One of the reasons for this is that each language has its own specification that it supports. The second reason is that each language has different default values for its specification that you need to be aware of. The basic specification that should match in both sides i.e. encryption and decryption are:

• Algorithm
• Secret Key
• Secret Key Size
• Initialization Value (IV)
• Padding
• Mode

Lets take the example of .Net and Oracle. We will use C# for encryption and Pl/SQL for decryption. The C# code for encryption can be found here, and the PL/SQL code for decryption can be found in our previous blog Encryption/Decryption in PL/SQL. Here is a copy of the code:

C# Code

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Security.Cryptography;
namespace EncryptConsoleApp
{
 class Program
 {
   static void Main(string[] args)
   {
     string text = "Test1234";
     string key = "5379075357908764";

     byte[] textBytes = new byte[text.Length];
     textBytes = ASCIIEncoding.ASCII.GetBytes(text);

     byte[] keyBytes = new byte[key.Length];
     keyBytes = ASCIIEncoding.ASCII.GetBytes(key);

     byte[] encrptedBytes = Encrypt(textBytes, keyBytes);

     Console.WriteLine("Encrypted Text: " + ByteArrayToHexString(encrptedBytes));
  }

  public static byte[] Encrypt(byte[] clearData, byte[] Key)
  {
    MemoryStream ms = new MemoryStream();
    // Create a symmetric algorithm.
    TripleDES alg = TripleDES.Create();
    alg.Key = Key;

    CryptoStream cs = new CryptoStream(ms,alg.CreateEncryptor(), CryptoStreamMode.Write);
    cs.Write(clearData, 0, clearData.Length);
    cs.Close();

    byte[] encryptedData = ms.ToArray();
    return encryptedData;
  }
 }
} 

PL/SQL Code

create or replace FUNCTION DecryptPassword(EncryptedText IN VARCHAR2,EncKey IN VARCHAR2) RETURN VARCHAR2
IS
encdata RAW(2000);
BEGIN
  encdata:=dbms_obfuscation_toolkit.DES3Decrypt(input=>hextoraw(EncryptedText),key=>UTL_RAW.CAST_TO_RAW(EncKey));
  return  (utl_raw.cast_to_varchar2(encdata));

END DecryptPassword;

If you run the C# code to encrypt Test1234, you will get the following:
Encrypted Text: 5F48C32F78F63971E19764659E3E57F7

Now lets take this output and decrypt it using the PL/SQL code, you should get Test1234 again. Unfortunately, that will not happen. You will see totally different output. Something like ¿ E ¿¿¿(¿u¿¿¿ -¿ or some symbols that are not been displayed in my application.

Now you might be wondering, Why is it so? What went wrong over here? So, lets take a closer look.

The default configurations for the Triple DES algorithm in .Net and Oracle are as follow:

	.Net	Oracle
Mode	CBC	CBC
Padding	PKCS7	Not supported
IV in HEX	C992C3154997E0FB	0123456789ABCDEF
Secret Key Size	192 bits	192 bits

As you can notice in the above table, the padding and the IV default setting are different in each framework. Thus the result of encryption and decryption will not match. In order to resolve the mismatch we need to change the configuration in C# code as Oracle 9i does not provide any mean to change these configuration. So we will set the value of IV to be the identical to the IV value in Oracle. But what about the padding? It is not supported at all in Oracle 9i. We can not set it to none as then we will be restricted to specific data length. The alternative solution we can think of is to set one of the padding mode that we can manually remove in Oracle. We will set the padding mode in .Net to ANSIX923 . The ANSIX923 padding string consists of a sequence of bytes filled with zeros before the length. In oracle we will check the last two digits i.e. the length and based on that we will remove all the zeros i.e. the padded part. The following example shows how the ANSIX923 mode works. Given a blocklength of 8, a data length of 9, the number of padding octets equal to 7, and the data equal to FF FF FF FF FF FF FF FF FF:

FF FF FF FF FF FF FF FF FF 00 00 00 00 00 00 07

Below is the modified code.

C# Code

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Security.Cryptography;
namespace EncryptConsoleApp
{
 class Program
 {
   static void Main(string[] args)
   {
      string text = "Test1234";

      string key = "5379075357908764";
      string iv = "0123456789ABCDEF";

      byte[] textBytes = new byte[text.Length];
      textBytes = ASCIIEncoding.ASCII.GetBytes(text);

      byte[] keyBytes = new byte[key.Length];
      keyBytes = ASCIIEncoding.ASCII.GetBytes(key);

      byte[] ivBytes = new byte[iv.Length];
      ivBytes = HexStringToByteArray(iv);

      byte[] encrptedBytes = Encrypt(textBytes, keyBytes, ivBytes);

      Console.WriteLine("Encrpted Text: " + ByteArrayToHexString(encrptedBytes));
   }

   public static byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV)
  {
      MemoryStream ms = new MemoryStream();
      // Create a symmetric algorithm.
      TripleDES alg = TripleDES.Create();
      alg.Padding = PaddingMode.ANSIX923;
      alg.Key = Key;
      alg.IV = IV;

      CryptoStream cs = new CryptoStream(ms,alg.CreateEncryptor(), CryptoStreamMode.Write);
      cs.Write(clearData, 0, clearData.Length);
      cs.Close();

      byte[] encryptedData = ms.ToArray();
      return encryptedData;
   }
 }
}

PL/SQL Code

create or replace FUNCTION DecryptPassword(EncryptedText IN VARCHAR2,EncKey IN VARCHAR2) RETURN VARCHAR2
IS
encdata RAW(2000);
numpad NUMBER;
result VARCHAR2(100);
BEGIN
  encdata:=dbms_obfuscation_toolkit.DES3Decrypt(input=>hextoraw(EncryptedText),key=>UTL_RAW.CAST_TO_RAW(EncKey));

  result :=rawtohex(encdata);
  numpad:=substr(result,length(result)-2);
  result:= substr(result,1,length(result)-(numpad*2));
  result := hextoraw(result);
  result := utl_raw.cast_to_varchar2(result);
  return result;

END DecryptPassword;

Now if you encrypt Test1234 using C# you should get the following output:
Encrypted Text: 109F3C4AD99AE1B0899596AB525D5D59

Lets try to decrypt the output using PL/SQL code. The output is
Test1234

Finally, C# code and PL/SQL code matches. In the same lines any other programming language can be used.

Encryption/Decryption in PL/SQL

This may sound dumb and easy. However, I am sharing my experiences with encrypting data because it might be of help to someone.

The Oracle packages that can be used for encryption/decryption varies on the Oracle version.
DBMS_OBFUSCATION_TOOLKIT   – Oracle 8i and above
DBMS_CRYPTO                – Oracle 10.1 and above

I have recently used DBMS_OBFUSCATION_TOOLKIT and will elaborate on it here.

DBMS_OBFUSCATION_TOOLKIT supports DES(Data Encryption Standard), Triple DES and MD5(Not encryption).  The requirement for DES encryption is that the data to encrypt should be multiple 8 bytes which is a requirement of the DES encryption algorithm.

There are ways for overcoming this drawback by using standard padding methodologies. However, if you are using DBMS_OBFUSCATION_TOOLKIT you are unlucky because it doesn’t support any kind of padding. Padding is extremely important especially when you are encrypting/decrypting data for other languages like Java and .NET. We faced situations where data encrypted on a .NET client was different from data encrypted by PL/SQL package. So let’s stop blabbering and get into an example.

We shall create two functions, one that will encrypt data and another that decrypts data.

The function for encrypting data is as follows

create or replace FUNCTION EncryptPassword(PlainText IN VARCHAR2,EncKey IN VARCHAR2) RETURN VARCHAR2
IS
encdata RAW(2000);
hexdata VARCHAR2(2000);
BEGIN

encdata:=dbms_obfuscation_toolkit.DES3Encrypt(input=>utl_raw.cast_to_raw(PlainText),key=>UTL_RAW.CAST_TO_RAW(EncKey));
return  (rawtohex(encdata));

END  EncryptPassword;

The function for decrypting data is as follows

create or replace FUNCTION DecryptPassword(EncryptedText IN VARCHAR2,EncKey IN VARCHAR2) RETURN VARCHAR2
IS
encdata RAW(2000);
BEGIN

encdata:=dbms_obfuscation_toolkit.DES3Decrypt(input=>hextoraw(EncryptedText),key=>UTL_RAW.CAST_TO_RAW(EncKey));
return  (utl_raw.cast_to_varchar2(encdata));

END  DecryptPassword;

Now we shall try out the functions we have just created

First the encryption of the data ‘Test1234’ with the key ‘1234567812345678’

select encryptpassword('Test1234','1234567812345678' ) ENC from dual;

ENC
———————————————-
4EF8091F4F5DCAA2

Now we shall decrypt the data with the same key

select decryptpassword('4EF8091F4F5DCAA2','1234567812345678' ) DEC from dual;

DEC
————————————————
Test1234

Some things to note, the data is of size 8 bytes(Test1234). The key is of size 16 bytes and should be multiples of 8 again. These are certain requirements by the Triple DES algorithm.

There are ways to get around this problem and that is with the support of padding. However it is only supported in DBMS_CRYPTO, but to get it working in DBMS_OBFUSCATION_TOOLKIT, you would have to implement it yourself.

More on .NET and Java encryption and how they interact soon…..

“ROWNUM” in Oracle is equivalent to “TOP N” is SQLServer

Today a colleague of mine was trying to pick the top row in a result set on SQLServer, and we being  Oracle Database fans could only think of ROWNUM. So here is what we found out after digging for some time.

Rownum is used in Oracle to retrieve the N-top records.

SELECT  * FROM TABLENAME
WHERE ROWNUM<2

Note: Some things to keep in mind are that the “>=” or the “>” operators wont work with ROWNUM. Also remember ROWNUM is to only pick the required row from a result set and should not be confused with the position of the record in the table.

Finally the equivalent query in SQLServer would be

SELECT TOP 1 * FROM TABLENAME

and of course is more intuitive.

MyEclipse and OC4J Container

One of the days at work, we had some requirements to deploy a java web application in an OC4J container (Oracle Container for Java). We are using MyEclipse as our development tool. We had easily integrated it with Tomcat for all the previous deployments. So, we thought it is going to be a cake walk. Not a big deal. Unfortunately it took us a day to figure out how to do it. Here are the steps to integrate MyEclipse with the OC4J container:

• Install OC4J
  • Download the oc4j container
  • Unzip it to some directory e.g. c:\oc4j
  • Set the JAVA_HOME environment variable to your java directory
  • Set the ORACLE_HOME environment variable to where you unzipped the oc4j i.e c:\oc4j
  • Start the oc4j container by running the oc4j batch file located in c:\oc4j\bin from the command prompt
    c:\oc4j\bin\oc4j -start
  • It will prompt for the admin password. Set the password. You would need to remember it for later
  • Stop the oc4j either by pressing CRTL+C or just close the window
• Integrate OC4J with MyEclipse
  • In the menu bar of MyEclipse go to Windows → Preferences → MyEclipse → Application Servers
  • Click on Oracle AS
  • Set Oracle AS to Enable
  • Set Oracle AS Home Directory to c:\oc4j\j2ee\home
  • Provide the Administration Password that you have just configured
  • Click ok
  • Start the Oracle AS to ensure that it has been configured properly
• Deploy your web application in OC4J

  Assuming that you already have a web project that you need to deploy, you would need to create an Enterprise Application Project. This is because you can only deploy ear files in OC4J container and not war files

  • Create new Project File → New → Project
  • MyEclipse → J2EE Projects → Enterprise Application Project
  • Add your web project as a module to the Enterprise Application Project before deployment you would need to change the server.xml that is located at c:\oc4j\j2ee\home\config.

  <application-server application-directory="../applications"
  deployment-directory="../application-deployments"
  connector-directory="../connectors"
  application-auto-deploy-directory="../application-autodeploy"
  check-for-updates="true">
  

  • In MyEclipse, click on the Deploy button to bring up deployment dialog.
  • Choose the Enterprise Application’s project from the selection box, Click Add button, choose Oracle AS as the server, choose Packaged Archive and click Deploy.
  • If you want to change the deployment mode to Exploded Mode, shutdown the OC4J.
  • Go to deployment dialog, choose the same project in the list of deployments below the project selection box. Click on Remove. Now repeat deployment with only one exception.
  • Instead of choosing Packaged Deployment, choose Exploded Deployment option.
  • Start OC4J as usual, now it’ll have a fully hot deployable application.

Just to note that the version we are using are MyEclipse 5.1.0 and OC4J 10.1.3.3.

For more information, you can check http://www.myeclipseide.org/

Journey of Mayhem

Today we start are journey of mayhem, learning and sharing our experiences in the ever growing field of technology. This blog was born with a purpose in mind to provide a centralized repository of knowledge which can be easily accessed and counted on at desperate times. We would like to share as we too have paddled through the same stormy sea of trepidation and in turn learn a thing or two. Our final note to end this pandemonium:

“The Blog is mightier than the Pen!!!”