• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC, PL/SQL in preparedstatement or callable statement (but not a stored procedure)

 
Tommy Griffith
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello. I am working on a legacy system, trying to get it to run for another year or two. it's a front-end for what was SQLServer but now is Oracle.

Anyways, the system was sending in procedure code (stored in a config file) as the arg in PreparedStatement. The script is fed into executeQuery and SQL Server returns a ResultSet, just as it does for any SQL query, which are also stored in the config file.

The standardized code can handle it either way as it always processes a ResultSet (always returned by SQLServer).

However, trying this with Oracle and sending in PL/SQL as the arg in executeQuery() (presumably, out of what I've seen) returns an Oracle Cursor object, which has to be cast to a ResultSet. Therefore, I get nothing as the return. In trying to do this as seamlessly as possible, I am trying to run a (hideous) test in standalone Java to see how the regusterOutParameter could work if the PL/SQL is the arg in a CallableStatement (but no stored procedure).

How do I make this call and do the registerOutParameter with no stored procedure but straight PL/SQL in the CallableStatement? I am thinking now I can't use CallableStatement, as it ~has~ to be a call to a stored procedure. I've tried so many things but it always seems to refer to a stored procedure call.

Can I do this with PreparedStatement, somehow? I don't think as the procedural syntax bombs it. I've tried variants of that so many times as well. Thank you so much for any help.


d="DECLARE ";
   
d=d+"cust_data_key NUMBER; ";

d=d+"cust_key NUMBER; ";

d=d+"TYPE REF_CUR  IS REF CURSOR; ";

d=d+"pout REF_CUR;";


d=d+"Begin ";

d=d+"cust_data_key:=2545; ";

d=d+"cust_key:=1761; ";

d=d+"OPEN pout FOR;";

d=d+"SELECT CustomerID,";
d=d+"CustomerType,";
d=d+"CustomerNumber,";
d=d+"CustomerName,";
d=d+"CurrentStatus,";
d=d+"from ClientTable e ";
d=d+"where e.CUSTOMER_TYPE_KEY = cust_key";
d=d+"AND e.CUSTOMER_CATEGORY_KEY<>cust_data_key;";

d=d+"END;";


System.out.println(d);

CallableStatement cstmt=conn.prepareCall(d);

cstmt.registerOutParameter(1,OracleTypes.CURSOR);

cstmt.execute();

System.out.println("IT HAS EXECUTED");

rs=(ResultSet)cstmt.getObject(1);

while (rs.next()) {

...etc...

 
Stefan Evans
Bartender
Posts: 1807
10
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is a link to some Oracle Documentation
They seem to have code very similar to yours.

It looks like Callable Statement is the right thing for you (declare, begin, end makes a stored procedure). So you have that bit right.
The major difference I can see is that they include a ? placeholder in their SQL when they OPEN the cursor.


Also, please UseCodeTags when posting code.
And the SQL code could preferably have been posted without the string wrapping code. (i.e. the actual SQL being executed). It just makes it easier for US to read. And the easier it is for us to read, the more likely it is someone will respond :-)
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That is horrible. Maybe it's just me, but I hate dynamic SQL, and dynamic PL/SQL is simply an abomination - the whole point of using PL/SQL is to allow you to use pre-compiled imperative code within the RDBMS.

I guess it depends how much work you want to do (and have to test) on a legacy application, but you could probably just implement a PL/SQL wrapper function in the DB to wrap the SQL, which would cut out some of this dynamic crap. Even better, you could just put the SQL itself into a PreparedStatement and run it like any other JDBC-based query without any PL/SQL at all. All this dynamic voodoo just confuses things: a classic example of "clever != sensible".
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster wrote:Even better, you could just put the SQL itself into a PreparedStatement and run it like any other JDBC-based query without any PL/SQL at all. All this dynamic voodoo just confuses things: a classic example of "clever != sensible".


This was my first thought on seeing that code.
That SQL does not need to be in a PL/SQL block.
 
Tommy Griffith
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Stefan. yeah, I was reviewing that page before but for whatever reason I missed a crucial part of it.

Yeah, I know guys. This SQL is just test stuff and a majority of it can be taken out of PL/SQL, which I already started to do, but there are a few of these where there are a bunch of SELECT statements running in succession. This servlet was built to take one lump SQL as an arg from a config file then throw it in a single Preparedstatement. It then returns an xml stream of the results. The SQLs with multiple SELECT statements appends each result within the return xml stream. The SQLserver runs ok with the multiple SELECTs using it's procedural language in one PreparedStatement, returning a ResultSet, but I think Oracle needs to do PL/SQL to do that, right?

From there, my issue is that I have to deal with the return Oracle cursor, and convert this to a ResultSet, the root of all this. I was initially feeding the PL/SQL I was given into the existing servlet in the PreparedStatement, praying then it returned no results, which led me down the path of dealing with the Oracle cursor, then CallableStatement.
 
Tommy Griffith
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello. ok, this is what I am hitting now. It works fine in standalone Java but I am getting ORA-01008 not all variables bound when running inside the servlet.

I've even hard-coded in the servlet to do this...and it stills throws ORA-01008...



It's pretty straight-forward. unfortunately this is deep inside a servlet, is there any way I can print to tomcat log form the code? I tried putting in system.out.println but can't find anything to see what Oracle doesn't like, so I'm not sure if I can even get a trace on it form Oracle.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you using the latest version of Oracle JDBC driver? Your code correspond to an example in Oracle 11g documentation, perhaps the 11g JDBC driver is required:

I've sometimes encountered situations when OUT parameters had to be explicitly assigned to. Try replacing your PL/SQL block with this one:

And of course, you should replace 7121 with another ? and bind it as well.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic