Can we call java program from a Oracle stored procedure?

[Padma Prasad]

Hi,

I have a Oracle Stored Procedure and a java application. There is a dependency on the logic written in stored procedure and the business logic in java. Can I call java program from within a stored procedure? If it is possible, is it a good practice?

Thanks,
Padma.

[Scott Selikoff]

While I'm sure there might be a trick to do it, off hand, it wouldn't be available. Its bad enough to see the content of SQL stored procedures in java, but visa versa would lead to some really difficult to maintain code. You'd essentially be writing code no one could update.
[ August 05, 2008: Message edited by: Scott Selikoff ]

[Padma Prasad]

Thanks Scott,

But my requirement is such that I have to call a java class from a oracle stored procedure. I cannot move the logic from stored proc to java; neither can I move my java logic to a different stored proc.

is there a way to call java application from stored procedure other than using JNI?

Thanks,
Padma.

[Ulf Dittmer]

What kind of application is it you want to call into - a desktop application? What if it's not running?

This was also discussed in this recent thread. Why couldn't the application check with the DB every so often (maybe once a minute or so) instead of the other way round?

[Scott Selikoff]

Originally posted by Padma Prasad:
But my requirement is such that I have to call a java class from a oracle stored procedure.

Then you should reconsider the reason for such a requirement and find a solution that satisfies the problem without the architecture nightmare of having oracle call java. As Ulf all ready mentioned, polling is one such option.

[arun nohwar]

We can certainly do that. Call java program from oracle stored procedure.

Suppose there is oracle stored procedure OSP1 and a Java program Java1.
Now need to create a oracle stored procedure OSP2 on Java1.
Then we can simply call OSP2 from OSP1.

Steps in breif are:

1. Use loadjava (command line executable found in bin directory of where you installed oracle) to load the java class in oracle.
$bin> loadjava -user scott/tiger Java1.class

The above class is now loaded in database and can be seen in dba_object view.

2. Now create an oracle stored procedure OSP2 built on Java1.class loaded in database.
$bin> create or replace procedure OSP2 as language java
name 'Java1.method1()';
where method1() is a method in java class Java1.

3. Now OSP2 can be directly executed :
execute OSP2;
or called from dbms_java
or can be called from another oracle stored procedure OSP1.

I am currently not using oracle database (m now using open source db), so cant provide code snippet, but hope the above helps.

[Ulf Dittmer]

Well, Padma has referred to the entity to be called variously as "Java class" and "Java application", the latter more frequently than the former. So the assumption was that this code lives outside of the database. Maybe we'll get some clarification on that.

[Avi Abrami]

Padma,
There are several ways to invoke java code from an Oracle stored routine.
1. As Arun has suggested, create a java stored procedure.
2. Use RMI.
3. Use servlets.
4. Use Web Services.
You can find more details in this book:

http://www.amazon.com/gp/product/1555583296/

Good Luck,
Avi.
P.S. Scott, pardon my ignorance, but why is it bad to have SQL in your java code and what are the alternatives? (Thanks)

[Scott Selikoff]

Originally posted by Ulf Dittmer:
Well, Padma has referred to the entity to be called variously as "Java class" and "Java application", the latter more frequently than the former. So the assumption was that this code lives outside of the database. Maybe we'll get some clarification on that.

Thanks Arun but I agree with Ulf. Loading java files as an external stored procedure is a lot different from calling live java programs running in independent systems. My concern is that while you might be able to setup some type of communication, the maintenance of keeping such communication up to date would be hard.

As other mentioned connecting through a more open interface such as a web service is preferred, although that really has nothing to do with java (since you can build web services out of almost anything).

[Scott Selikoff]

Originally posted by Avi Abrami:
Scott, pardon my ignorance, but why is it bad to have SQL in your java code and what are the alternatives? (Thanks)

It's not bad, after all that's what JDBC is. Since we were on the subject of stored procedures I was imagining putting the contents of stored procedure code inside java. I've seen pages of stored procedures saved as java strings, its not pretty. Sorry for the confusion I've made the correction.
[ August 05, 2008: Message edited by: Scott Selikoff ]

[Padma Prasad]

Thanks Arun for the detailed information.

Ulf is right. I cannot store my java class in database. It is a live application. Java class in java application and Oracle stored procedure are independent. There could be changes to the java class as well so I may not store it in Database.

Thanks Avi. I guess webservices should be a good alternative. Need to explore this option. Thanks much for the help.

Thanks,
Padma.

[Jimmy Clark]

Try this...

1. Create a shell script that contains the command line to execute the Java-based program.

2. Create a program to serve as a listener on a database pipe.

3. From the stored procedure, put command to run Java program in pipe.

4. When listener hears command in pipe, it will run the Java-based program.

5. Java-based program can put results in another pipe.

6. Stored procedure gets results from results pipe.