JAVA: EXTENDING THE POWER OF PL/SQL

Article by Vadim Loevski, founder of Dimlar Consulting

Oracle 8i introduced so many new features to the Oracle RDBMS that it will take some time for the Oracle developers to absorb them. It also created some fears among developers about the fate of the PL/SQL programming language, which for many years ruled the world of back-end development in Oracle. These fears are based on the introduction of JServer, which is an environment enabling the use of Java language to write Oracle stored procedures, functions and triggers. The goal of this presentation is

to show how Java and PL/SQL can co-exist and even complement each other
to show PL/SQL developers how they can use Java now in order to create functionality they were not able to implement in PL/SQL before (e.g. executing OS commands, advanced file handling routines, ZIP archiving etc).

This approach will help the traditional Oracle developers to make use of Java immediately, extending and enhancing the power of PL/SQL to easily perform tasks that were previously unachievable in PL/SQL.

Introduction to JAVA: EXTENDING THE POWER OF PL/SQL

1999 was one of the most exciting years in the Oracle community. Undoubtedly, the main event was the release of the long awaited Oracle 8i database, which added so many new features to Oracle RDBMS, that it will take probably some time for Oracle developers to absorb them, let alone implementing them as part of their architectural solutions.

The excitement was not the only thing that this new release brought into the Oracle World, it also created some fears among the traditional Oracle application developers about the fate of PL/SQL - the programming language, which for many years was ruling the world of back-end development. These fears were instantiated by the introduction of the JServer, which is effectively the environment enabling the use of Java language to write Oracle stored procedures, functions and triggers. This article will be interesting to PL/SQL developers who want to use some Java capabilities to extend the power of PL/SQL beyond its traditional limits.

This article may not present any interest to pure Java developers since it does not intend to cover Java technology as such. It may interest those who sincerely want to understand the mentality of PL/SQL developers, who are believed, according to some very rough estimates to be the army of more than 150K individuals worldwide.

The future of PL/SQL

Oracle is still committed to PL/SQL. Last year I was at Oracle Open World in Los Angeles where I met the management of Oracle's PL/SQL development. I could not resist asking them a question if they felt any signs of PL/SQL group going the stagnation period because of Java.

They laughed and reassured me that the PL/SQL group is quite big and getting bigger. They have huge plans and they are committed to deliver lots of new PL/SQL functionality in the upcoming releases of Oracle. This statement was very encouraging and I felt that the internal competition at Oracle between Java and PL/SQL groups is in fact a very healthy one in terms of speeding up the efforts in PL/SQL development.

I am pretty sure that we will see lots of wonderful additions to PL/SQL in the nearest future. The future of PL/SQL is still pretty bright because PL/SQL excelled as a procedural extension to SQL language and definitely, at this point, is the language of choice for development of Oracle client-server solutions. According to many reports it is a faster language for pure database applications, because of its better integration with SQL.

Java stored programs show unbeatable results when implementing pure computational tasks, but they leg behind in the area of traditional database applications and definitely cannot be used for database applications where the performance is crucial. I remember several articles that tried to convince the Oracle community in the opposite saying that Java is a faster then PL/SQL for database access. A particular memorable article comes to mind, written in one of the respectable Java journals where the author misled readers (hopefully unintentionally) when comparing the performance results of two programs written in Java and PL/SQL.

You can read the whole Oracle article here (PDF file: 129 KBytes).

Conclusion

This article demonstrated lots of examples of Java stored programs callable from PL/SQL. Java created huge opportunities for PL/SQL developers who really want to extend PL/SQL language capabilities. Just think of the different application functions, which we could easily implement in PL/SQL using Java stored programs. For instance we can select multiple LOBs (e.g. MS Word documents) from the database, ZIP them into one archive file and email to the nominated addressee and all this can be 100% transaction driven. At the same time PL/SQL developers must be ready to challenges as well. Java technology could present a steep learning curve for PL/SQL developers that are not ready to grasp OO concepts. My advice would be to start writing simple classes and not get intimidated by the complexities of Java. It is a great language and you will be rewarded in the long run for learning it.