• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Oracle Java Stored Procedures

 
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author
Posts: 4356
45
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:



Regards,

Fatih.
 
Sachin Deokar
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic