Calling Oracle SP using JDBC

T

Tim Slattery

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.
 
L

Lee Fesperman

Tim said:
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.
 
T

Tim Slattery

Lee Fesperman said:
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.
 
L

Lee Fesperman

That's not it, := is the assignment operator in Oracle. All the
examples I've seen have that syntax.

That might be true, but what I was describing is 'JDBC' syntax (no :
before =). Notice that you are using JDBC escape syntax by enclosing
the call in braces.

Did you even try my suggestion? Did you get the exact same exception?
 
S

steve

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top