jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes  java stored procedure problems Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark " java stored procedure problems" Watch " java stored procedure problems" New topic
Author

java stored procedure problems

Chris Staten
Ranch Hand

Joined: Sep 24, 2004
Posts: 101
Hey all, at this moment I�m so confused about creating/using java stored procedures in an Oracle database (9i) that I may not even know how to ask a logical question�so please bear with me.

I�m coding a sample JSP to try to understand how java stored procedures work, I�m using a basic MVC architecture to do this. I start with an index.jsp view, it gets needed SQL parameters. I forward the parameters to my controller FunctionTest.java. The controller makes the call to the function (select_address) in the database (it also gets a database connection from the model), and forwards the result on to the result.jsp view.

Simple as this seems when I picture it, I can�t seem to make it work. Here is the code and the error from the database:

FunctionTest.java (the controller), this is a fragment:



TestSelectAddress.java (the stored procedure):



The PL/SQL wrapper code


Error returned from the database



The view pages and model code don�t seem to have any problems so I�m not posting that code unless someone requests it.
Jeremy Wilson
Ranch Hand

Joined: Feb 18, 2003
Posts: 166
Not sure if this will help but, can you run the procedure in sql plus and get the result. If so I would try prefixing the call with the user name

user.package.procedure

I don't see where you are logging in with a user name and password for the connection so you may need the user to identify the schema which has the applicable package and procedure.


Jeremy Wilson
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Chris,
Pardon me if I am not telling you something you don't already know, but I couldn't find the information in your post.

Have you loaded the "TestSelectAddress" class into the database?

Good Luck,
Avi.
Chris Staten
Ranch Hand

Joined: Sep 24, 2004
Posts: 101
Thanks for the replies!

Jeremy, I cannot run the stored procedure through my IDE (Toad). I�m not an expert with Toad by any stretch, but I think I�m attempting to test the stored procedure the correct way. I haven�t touched this problem since Friday so I�ll try to test the stored procedure in Toad again and I�ll post the error. Also, my username and password are provided in the datasource settings in my container (WebSphere). The connection settings are made as part of the code in the MVC model (OraConn.java).


Avi, I created TestSelectAddress.java in an IDE and then compiled it with the javac utility. After it compiled without errors I used the loadjava utility provided by Oracle to load the class into the database as an object.
Chris Staten
Ranch Hand

Joined: Sep 24, 2004
Posts: 101
I still don�t have this figured out, but I think I�m moving in the right direction. Here is a quick overview on my understanding of java stored procedures: in Oracle (I don�t know about other DBs) a java stored procedure is an object in the database. You need to publish/make it accessible by using PL/SQL to call the java class.

OK, some of my problem may be coming from the fact that I�m thinking in terms of pass-by-value when in fact PL/SQL uses pass-by-reference. The reason that I�m thinking that this may be a problem is because of what I read on Oracle's site:


This code fragment�s comments state that the parameters numRoomsAvailable and standardRoomRate are �OUT� parameters. This makes me think that the PL/SQL code still has a variable referencing the actual object that is sent in as an arg to the java method. With this in mind I�ve changed my code to the following:



The changes that I made are accepting a ResultSet array as a param, and not returning one. I now just place the result of the query into the first index of the array rs[]. I am hoping that because the PL/SQL code still has a reference to rs[] that it will not simply go away when the method ends and my local object falls out of scope.

I�ll post my results.
Chris Staten
Ranch Hand

Joined: Sep 24, 2004
Posts: 101
I got it figured out! Unfourtunatly I�m headed away from the computer for the next week or so (and I�m late for an appointment right now). I�ll post my working code when I get back around the second week of May.

The main reason that I want to post my working code is because every forum post (not java ranch) that I found when searching about this only had the problem. No one posted the answer! I know those people must have found a solution.

I�m going to try to post an answer that makes sense to other people (not just me) so that people searching for answers about returning a ResultSet from an Oracle stored procedure/function using JDBC may not have to go through what I did.
Chris Staten
Ranch Hand

Joined: Sep 24, 2004
Posts: 101
OK, here comes my solution to why I was having problems with Java stored procedures/functions (sorry that it�s taken me a few months to get back to this). But first let me say two things.
  • I hope this helps anyone who is searching for how to create a java stored procedure in Oracle. When I googled this out I found (multiple times) other people with exact same question that I had, but no answers.
  • If I am mistaken in my solution PLEASE point that out so that neither I nor anyone else who is new at stored procedures is doing this wrong.


  • One of my major problems was that I was thinking in java terms while writing my java stored procedure and not in Oracle/java terms. What I mean is that when writing a java stored procedure to be placed as an object in an Oracle database you aren�t necessarily dealing with java in the same way that you would be if you were in a standard JVM.

    For example, when I converted the stored procedure above (TestSelectAddress) I changed the method signature of the sELECT_ADDRESS method from:

    to:


    This was due to the fact that I wanted to return a ResultSet, and as we all know (normally) in java if you want to return something you declare the return type in the method signature the way I did in the first of the two methods above. BUT, in Oracle, using a java stored procedure, you would declare it as an incoming parameter and simply set that parameter to whatever value you want returned and be done with it. As a matter of fact, if you wanted to do any clean up of things such as possible open connections to a database you could do it after setting the parameter (rs in this case) to the value you wanted to return. This is opposed to the normal thinking of returning something in java where the method ends when you hit the return statement. To use an incoming arg to return a value in a java stored procedure in Oracle you need to declare it as an �out� parameter in your PL/SQL, then set the variable of that parameter in your stored procedure to the value that you want to return. Because this didn�t feel natural to me I converted all of my java stored procedures over to what I think of as java stored functions . All this really means is that my PL/SQL wrapper is declared as a function and not a procedure. By using a function to call my java stored procedure I can return a value from my java code and the database can handle it. My working version of the java stored procedure is in the post below. I have read in a few places that if you plan on returning something from the database you would normally want to use a function as opposed to a procedure (as your PL/SQL wrapper).

    I hope this helps and doesn�t confuse people further. If anyone ever comes across this post and is just as confused about java stored procedures in Oracle as I was please don�t hesitate to post to this thread and use my listed email address to ask me to come read it.
    Chris Staten
    Ranch Hand

    Joined: Sep 24, 2004
    Posts: 101
    Here is the working PL/SQL wrapper, java stored procedure, and calling Servlet code:

    PL/SQL wrapper:


    NOTE: you need to create a REF CURSOR before you can use the above function.


    Java stored procedure:


    Servlet code (calling fragment):

    [ July 07, 2005: Message edited by: Chris Staten ]
    Chris Staten
    Ranch Hand

    Joined: Sep 24, 2004
    Posts: 101
    I'm back!

    It seems that there were some other lurking issues that I didn't think about when setting up my Java Stored Procedures this way. Of course I found out about these issues in production rather than testing.

    The problem that I ran into is

    I'd love some feed back if anyone sees a problem with this change.

    Thanks!

    EDIT: It looks as if bold text doesn't work well inside the code block text. The changes start after the
    rs = stmt.executeQuery();
    line.
    [ August 02, 2006: Message edited by: Chris Staten ]
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: java stored procedure problems
     
    Similar Threads
    retrieve data in PL/SQL proc using JDBC in Websphere
    Stored Procedure Problem
    Cannot get Oracle CURSOR from Oracle procedure
    Cannot get Oracle CURSOR from Oracle procedure in Websphere
    CallableStatement Error