File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes calling pl/sql in Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Make it so: Java DB Connections & Transactions this week in the JDBC forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "calling pl/sql in Java" Watch "calling pl/sql in Java" New topic

calling pl/sql in Java

joe weakers
Ranch Hand

Joined: May 31, 2004
Posts: 38
Hi there. I having problems running pl/sql procedures from inside my java code. I have created and compiled several pl/sql procedures that all work perfectly when executed outside of my code. However, once I try to call these procdures inside my Java I keep encountering the same error. The following code snippet is where I attempt to call the pl/sql:

CallableStatement plsqlblock = cont.conn.prepareCall("Hellen.TEST2");
plsqlblock.execute(); //this line gives the error

where Hellen.TEST2 is the name of the procedure and cont.conn is the connection instance. The error I am getting is as follows:

java.sql.SQLException: ORA-00900: invalid SQL statement.

Secondly, how does one pass arguments from java into PL/SQL and then pass the result from the pl/sql procedure back to the java for further processing. Any advice here would be gratefully appreciated. joe
pascal monfils

Joined: Aug 09, 2004
Posts: 9
did you try to add "()" after the procedure name ?
joe weakers
Ranch Hand

Joined: May 31, 2004
Posts: 38
Hi Pascal. If you mean passing "Hellen.TEST2()" as the string into my code? yes I tried it just there and it made no difference. I still encounter the same error:

CallableStatement plsqlblock = cont.conn.prepareCall("Hellen.TEST2()");
java.sql.SQLException: ORA-00900: invalid SQL statement

Have you any other possible solutions?
Thanks, Joe
pascal monfils

Joined: Aug 09, 2004
Posts: 9
You could also try "CALL <pkgName>.<procName>()";

Search the internet ... the jdbc driver accepts 2 syntaxes for calling a pl/sql stored proc.

Regarding the argument and return values, assume a store proc "PROC1" in package "PKGA" takes 2 args as int; the first one is an IN and the second an OUT:
you have a connection named conn.

you write something like

stmt = conn.prepareCall("{CALL PKGA.PROC1(?,?)}");
stmt.registerOutParameter(2, javaSQL.Types.FLOAT); // the 2nd out param
stmt.setInt (1, <theValue> ; // fill the first arg.

if the pl/sql code is not a store proc but a function FCT1 returning an int then :
stmt.conn.prepareCall("{CALL ? := PKGA.FCT1(?,?)}");

Doesn't know the full specs by heart but searching the internet will greatly help you.

Good job
I agree. Here's the link:
subject: calling pl/sql in Java
It's not a secret anymore!