This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes Add a jar file for a Java Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Add a jar file for a Java Stored Procedure" Watch "Add a jar file for a Java Stored Procedure" New topic

Add a jar file for a Java Stored Procedure

Omar Al Kababji
Ranch Hand

Joined: Jan 13, 2009
Posts: 357
Hi guys,

I am developing a java stored procedure and i want to load it into oracle using the loadjava command line, however inside the method of the procedure I use some classes that are obrained from a jar file, how can I include the .jar file into the class-path of oracle ??

in addition one of the arguments of the java method is ResultSet do you know to which oracle type it should be mapped, or if there is any other alternatives for using the ResultSet ??

for example:

1) in pl/sql i do a select into statement and put the data in an appropriate type.
2) then i want to call my Java Stored Procedure passing it the data i retrieved, ( which should be mapped to a ResultSet in Java or something else don't know).
3) finally in the Java Stored Procedure I handle the data i received.

for now the only workaround is that i don't pass the data, i just pass a String containing the Query to execute, and i create a PreparedStatment in my Java Stored Procedure and execute this query, and obtain the ResultSet from it, however i think its not very good.

I have read around how to return a ResultSet from a Java Stored Procedure, but nothing talking about how to pass it as an argument, I have seen that the java.sql.Array interface has a method getResultSet, but I didn't understand how i could put the result of a select query into an object of type ARRAY or VARRAY

any help would be appreciated. Thanks.
p.s. I am using Oracle 10g, and Java 1.4

Omar Al Kababji - Electrical & Computer Engineer
[SCJP - 90% - Story] [SCWCD - 94% - Story] [SCBCD - 80% - Story] | My Blog
Omar Al Kababji
Ranch Hand

Joined: Jan 13, 2009
Posts: 357
Ok for the jars it was simple i just used the same command loadjava to load the needed jar files. however i am still blocked with the REF CURSOR --> ResultSet mapping, which is needed as a method parameters.

Sudarshan Sreenivasan
Ranch Hand

Joined: Jun 28, 2007
Posts: 188


I was just trying to use a external jar in my java stored procedure, can you just let me know the exact steps you followed like where did you keep the jar ?

Which loadjava command did you use , the one I see most easily available on the net is

is this the correct one ?

Omar Al Kababji
Ranch Hand

Joined: Jan 13, 2009
Posts: 357
Yes its the same.
It is sorta covered in the JavaRanch Style Guide.
subject: Add a jar file for a Java Stored Procedure
Similar Threads
getting the resultset of a stored procedure?
best programming practises of JDBC
Calling Oracle Stored Procedure which has parameter as CURSOR
How to obtain a resultset using a stored procedure
Need a disconnected resultset