File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need help with callableStatement()

 
George Larry
Ranch Hand
Posts: 52
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 87
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
May be this is what you are looking for in place of your '?':
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
 
George Larry
Ranch Hand
Posts: 52
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 49
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 52
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 52
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 52
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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;
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic