Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

calling pl/sql in Java

 
joe weakers
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
did you try to add "()" after the procedure name ?
 
joe weakers
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
stmt.execute();

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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic