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.
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 :-)
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".
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.
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.
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.