• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Need help with callableStatement()

 
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 );
 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
 
reply
    Bookmark Topic Watch Topic
  • New Topic