Oracle Java Stored Procedures

[Sachin Deokar]

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.

    
CREATE OR REPLACE FUNCTION "MYDB"."MY_TIMESTAMP" return varchar2 AS LANGUAGE JAVA  NAME 
'MyTimestamp.getTimestamp() return java.lang.String';

CREATE OR REPLACE FUNCTION "MYDB"."FN_COMM2"(v_formattedMessage IN VARCHAR2, 
v_formattedMessageNo IN NUMBER, v_hostip IN VARCHAR2,  v_port IN NUMBER) 
return varchar2 as language java 
name 'MessageServer2.srvResponse (java.lang.String,java.lang.Integer,java.lang.String,java.lang.Integer)
return java.lang.String';

Thanks in advance.
Sachin Deokar

[Scott Selikoff]

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.

[Fatih Keles]

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:

select * from all_objects where object_type like 'JAVA%' and dbms_java.longname(object_name) like 'MyTimestamp%' 

Regards,

Fatih.

[Sachin Deokar]

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]

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.