wood burning stoves 2.0*
The moose likes JSP and the fly likes Need help with callableStatement() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "Need help with callableStatement()" Watch "Need help with callableStatement()" New topic
Author

Need help with callableStatement()

George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
I have a PL/SQL procedure that SELECTs lists of data from several tables. I want to be able to use this with JSP. I've read about people using CURSORS to retrieve multiple tuples from database queries. I'm new to cursors and callableStatements.
Can someone please walk me through (examples would be nice) this process?
My procedure is fairly basic. I give it a userID and/or location that are used in the WHERE clause and it will list all the information for that user and/or location.
I think this is how it's supposed to be done-> but I'm not positive:
callableStatement cs = con.prepareCall ( "{ call myProc ( ?,?, ? )}" );
cs.registerOutParameter( 1, CURSOR );
cs.setString( 2, uNum );
cs.setString( 3, location );
cs.execute();
resultSet rs = cs...?
while( rs.next() ) {
//List data
}
Is this right?
shilpa kulkarni
Ranch Hand

Joined: Jun 07, 2000
Posts: 87
May be this is what you are looking for in place of your '?':
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
I added the Oracle stuff- I had to include <%@ page import="oracle.jdbc.driver.*" %> as well, but now I'm getting several errors like this one:
C:\Tomcat\work\localhost\examples\jsp\Henry\telcom\tReports\tCreateReport$jsp.java:0: The method oracle.jdbc2.Clob getClob(int) / getRef(int) / getBlob(int) / getArray(int) declared in class oracle.jdbc.driver.OracleCallableStatement cannot override the method of the same signature declared in interface java.sql.CallableStatement. They must have the same return type.
package org.apache.jsp;
^
I tried a few other things, but I'm not sure what I'm supposed to do with that. Any ideas?
Here's my code:
cs = con.prepareCall ( "{ call myProc ( ?,? )}" );
cs.registerOutParameter( 1, OracleTypes.CURSOR );
cs.setString( 2, uNum );
cs.execute();
rs = ( ( OracleCallableStatement ) cs ).getCursor( 1 );
Syam Veerakumar
Ranch Hand

Joined: Sep 20, 2001
Posts: 49
Try this,
cs = con.prepareCall("{? = call myProc(?,?)}");
cs.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR);
cs.setString(2,uNum); // if its string or if its numeric give setInteger or setLong like that
cs.setString(3,something);
cs.execute();
ResultSet rs=(ResultSet)cs.getObject(1);
while(rs.next()){
//do something
}
If u'r procedure is in a package then u have to mention that package name like packageName.myproc(?,?)

A Quitter Never Wins<br />A Winner Never Quits
George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
Thank you... I think I'm making progress, but it's still not working.
I've got this:
cs = con.prepareCall ( "{? = call billByDept ( ? )}" );
cs.registerOutParameter( 1, oracle.jdbc.driver.OracleTypes.CURSOR );
cs.setString( 2, uNum );
cs.execute();
rs = ( ResultSet ) cs.getObject( 1 );
But now I'm getting an error:
PLS-00905: object TELCOM.BILLBYDEPT is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I know that's a PL/SQL error... but I'm guessing it has something to do with my trying to retreive the results as an OBJECT. What would you do?
George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
Here's my procedure:
PROCEDURE billByDept(
rSet OUT reportC.rc,
uNum IN ASSIGNMENTS.USERID%TYPE
) AS
BEGIN
OPEN rSet FOR
SELECT
a.VENDOR, a.INVOICEDATE, a.INVOICENUM, a.INVOICEAMT,
a.DATESTAMPED, a.RCVDINTELCOMDATE,
( SYSDATE - a.RCVDBYTRDATE ) AS DAYS
FROM
INVOICE a, BILLING b, ASSIGNMENTS c, SERVICES d
WHERE
a.INVOICENUM = b.INVOICENUM
AND
b.BILLTOUSERID = NVL( uNum, bBILLTOUSERID )
AND
b.ASSIGNID = c.ASSIGNREFNUM
AND
c.SERVICEID = d.SERVICEID
AND
d.SERVICETYPE = 'Pager';
END;
George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
Now I'm getting this error:
PLS-00306: wrong number or types of arguments in call to 'BILLBYDEPT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Here's my procedure:
PROCEDURE billByDept(
rSet OUT reportC.rc,
uNum IN ASSIGNMENTS.USERID%TYPE
) AS
BEGIN
OPEN rSet FOR
SELECT
a.VENDOR, a.INVOICEDATE, a.INVOICENUM, a.INVOICEAMT,
a.DATESTAMPED, a.RCVDINTELCOMDATE,
( SYSDATE - a.RCVDBYTRDATE ) AS DAYS
FROM
INVOICE a, BILLING b, ASSIGNMENTS c, SERVICES d
WHERE
a.INVOICENUM = b.INVOICENUM
AND
b.BILLTOUSERID = NVL( uNum, b.BILLTOUSERID )
AND
b.ASSIGNID = c.ASSIGNREFNUM
AND
c.SERVICEID = d.SERVICEID
AND
d.SERVICETYPE = 'Pager';
END;
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Need help with callableStatement()
 
Similar Threads
Stored Procedure Problem
Trouble executing stored procedure and getting a value back
Oracle wrong number or types of arguments...
Error while inserting data through procedure
Calling Oracle stored procedures in JRun container