File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Oracle Java Stored Procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle Java Stored Procedures" Watch "Oracle Java Stored Procedures" New topic

Oracle Java Stored Procedures

Sachin Deokar
Ranch Hand

Joined: May 09, 2008
Posts: 41
I was recently looking to migrate/rewrite one of our legacy app developed in VB6 and Oracle 9i. I noticed this below mentioned stored procedures and few other like that, which used "AS LANGUAGE JAVA". I am a mid level java developer but never used this before. Can you help me understand why this is used and is there any benefits using it in Oracle 11g and is there any better option to replace it?

These are very generic stored procedures, there are few more that have complex business/computation logic in them.

Thanks in advance.
Sachin Deokar
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

As someone who has successfully "migrated" stored procedures, let me tell you right now its impossible to directly migrate them from anything into anything. Stored procedures fall under the category of write-once-use-once code. The syntax between different DBMS's, sometimes different versions of the same DBMS, is drastic enough any major change to the database will require a rewrite. In the past, I've determined what the stored procedure does then rewrite them from scratch in the new system using the older version as a guideline.

[OCA 8 Book] [Blog]
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Hi Sachin,

Those are pl/sql mappings for java stored procedures/functions which Oracle calls it as publishing. Most java developers intend to use java in database under highly computational circumstances rather than data intensive cases. Here Oracle explains it in key features and here in detail.
But Oracle also uses java stored procedures and functions too and they encourage java usage in database in the proper way.

Using java to parse a string or regex operations or xml dom manipulation or array operations or generic sql execution etc... is much more easier in java than to deal with pl/sql. Since java and pl/sql are designed for different purposes. In some ways it is more efficient to use java than pl/sql and sometimes the only option left to programmer.

You should access the java source in order to see what is done inside java code as your scripts are only for publishing. You can search for java source/class with the following query:


Sachin Deokar
Ranch Hand

Joined: May 09, 2008
Posts: 41
Thank you guys. Appreciate your response.

The current app was written as FAT client in VB6 with Oracle 9i which has been migrated to Oracle 11g. Right now I am in process of analyzing if it makes sense to re-write the app in Java or ASP.Net as a web client though few of my colleagues are pushing for .Net fat client (most of the team here is .Net based). This app has all its business logic stored in Stored Procedures, some of them used for complex financial calculations, but few of them just using this Java stored procedures rather than doing it in Java Back end.

My question is that how resource intensive are this java stored procedures compared to if replaced by POJO classes running on Weblogic Server.

My current option is just to replace the presentation logic in VB6 and move to a web-client but not sure what to do with the not-so complex business logic in Java Stored Procedures. Does it make sense to replace few stored procedures which does not have any complex calculations and move it to business logic layer?

Hope I am making some sense .. It's friday and just tired after a long week :-)

As again thank you Scott and Fatih for replying to my post.

Sachin Deokar
Henry Mbadiwe

Joined: Feb 09, 2010
Posts: 4
There's a way that you can access EJBs with Visual Basic:
Visual basic for the JVM
You might want to wrap the database calls with a session bean and use the above method to access the data.
I haven't tried this but it looks interesting :-)

Or a better idea would be to use a Web service layer to connect the two.
I agree. Here's the link:
subject: Oracle Java Stored Procedures
It's not a secret anymore!