Need help with callableStatement()

[George Larry]

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]

May be this is what you are looking for in place of your '?':
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);

[George Larry]

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]

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(?,?)

[George Larry]

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]

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]

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;