Re: Calling Oracle SP using JDBC
On Sat, 16 Aug 2008 04:16:18 +0800, Tim Slattery wrote
(in article <9voba4lt1sht20ft21davs4u8qsauuhmh0@4ax.com>):
Lee Fesperman <firstsql@ix.netcom.com> wrote:
Tim Slattery wrote:
I've got an Oracle database, I've written a function within it, which
returns a sys_refcursor. The function works, I can run it from
SQL*Plus.
But I can't invoke it from Java. I get a connection, then do this:
CallableStatement sprocStmt = null;
String spName = "{ call ? := getReporterData(?) }";
sprocStmt = dbConnection.prepareCall(spName);
sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);
sprocStmt.setString(2, key);
sprocStmt.execute();
It blowu up on the "execute" call. The error message says "invalid
character". That's it, nothing else.
Since the procedure works, the problem has to be in the Java code. But
where? It looks exactly like the examples I've been working from.
Your syntax for call is incorrect. Remove the colon (:) before the
equal (=). That's probably the "invalid character" the error message
refers to.
That's not it, := is the assignment operator in Oracle. All the
examples I've seen have that syntax.
The syntax is wrong, "?" is used for parmeter substitution, so your telling
oracle to call a cursor!!
below is CORRECT, notice the "? = call"
because the cursor is returned from the proc, you need to tell the interface
this fact.
String The_qry = "{ ? = call
external_user.PACKAGE_02.RETURN_progparts2(?,?) }";
cstmt = (OracleCallableStatement) dbconn.prepareCall(The_qry);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setString(2, progid);
// the program info we want
cstmt.setInt(3, deployFlag);
cstmt.execute();
rset = ((OracleCallableStatement) cstmt).getCursor(1);
........
Steve