Callign an oragle package in Java with JDBC

[John Kel]

All, I am attempting to convert a VB application to Java and am having difficulties calling an Oracle function inside of a package.

This is the call in VB:

"{CALL bPKG.BPKGGetData({resultset 500,Serial,Date,Agent," & _
"button,ActiveState,PIN,Back,Block,Quant,Lang,Remarks," & _
"Type,Code,Name})}"

if I port the same thing over to Java and run it as plain SQL, it fails... This i can understand.

After researching I attempted to use the "CallableStatement" like this:

strSQLStmt = "? = {CALL bPKG.BPKGGetData(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
               
CallableStatement stmt = dbBows.getConn().prepareCall(strSQLStmt); //dbBows is a connection class i have created.  I ensure you it is making a standard JDBC conn to the DB.  getConn() just returns the connection object.
 
stmt.registerOutParameter(1, OracleTypes.CURSOR);
               
//set the in params
stmt.setString(2, "resultset 500");
stmt.setString(3, "Serial");
stmt.setString(4, "Date");
stmt.setString(5, "Agent");
stmt.setString(6, "button");
stmt.setString(7, "ActiveState");
stmt.setString(8, "PIN");
stmt.setString(9, "Back");
stmt.setString(10, "Block");
stmt.setString(11, "Quant");
stmt.setString(12, "Lang");
stmt.setString(13, "Remarks");
stmt.setString(14, "Type");
stmt.setString(15, "Code");
stmt.setString(16, "Name");
 
stmt.execute();

Every time it gets to the "stmt.execute()" line it fails with a null pointer exception. Again, i have tested the connection, the stmt, and the sql String and they are all valid and NOT null.

Any ideas would be appreciated.

Thanks,
John

[Jeanne Boyarsky]

John,
Welcome to JavaRanch!

You are correct in using CallableStatement. Can you post the stack trace of the null pointer? This may give a clue as to what is wrong.

try {
  // your code here
} catch (Exception e) {
  e.printStackTrace();
}  

[John Kel]

Here is the StackTrace...



10/18/2005 10:46:58,fetchBowData(),CBow.java,ERROR Fetch Bows Data --> ORA-00900: invalid SQL statement

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

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:684)
at com.myapp.fetchdata.CBow.fetchBowData(CBow.java:170)
at com.myapp.fetchdata.CBow.run(CBow.java:81)


The only thing that happens in the run method is the call to fetchBowData()... Inside of fetchBowData() the very first thing I am doing is setting up and attempting to make this DB call (code in 1st posting).

[John Kel]

Please someone help...

*bump*

[Jeanne Boyarsky]

John,
I'm not sure what the problem is, so I recommend the standard debugging technique: simplify. Try a stored proc without parameters and gradually add one at a time until it works.

[praveen kanakarajan]

Im not an expert
AS far as i know

"{CALL bPKG.BPKGGetData({resultset 500,Serial,Date,Agent," & _"button,ActiveState,PIN,Back,Block,Quant,Lang,Remarks," & _"Type,Code,Name})}"

it seems all are out parameters

{resultset 500,Serial,Date,Agent," & _"button,ActiveState,PIN,Back,Block,Quant,Lang,Remarks," & _"Type,Code,Name}

it means its all outparameters and the out parameters are Serial,Date,Agent
button,ActiveState,PIN,Back,Block,Quant,Lang,Remarks,Type,Code,Name

U will get an idea if u visit this link
http://www.kbalertz.com/kb_314637.aspx

hope i had helped u