Solved

JDBC Callable Statement Casting Exception

Posted on 2009-05-11
4
816 Views
Last Modified: 2013-12-29
I am trying to execute an ORACLE Funtion using a JDBC Callable statement.  The function returns a Varchar.  When I execute, I get a ClassCastException.  The funtion statment and the method are both below.  What am I doing wrong, JDBC will handle a Varchar, correct?
Oracle Function: 

CREATE OR REPLACE FUNCTION OMSOWNER.PRC_CCA_DELETE_INMATE_FUNCTION (INJLOCAT VARCHAR2, BOOKNUM VARCHAR2, TICKETNUM VARCHAR2)

return varchar2
 

Code:

    public void execute(String jlocat, String booknum, String ticketnum)

        throws SQLException {
 

        String query = useOracleQuery ? oracleQuery : genericQuery;

        System.out.println("Query: " + query + "\n");

        CallableStatement stmt = conn.prepareCall(query);
 

        // register the type of the out param - an Oracle specific type

        stmt.registerOutParameter(1, OracleTypes.VARCHAR);

        //stmt.registerOutParameter(1, OracleTypes.CURSOR);
 

        // set the in param

        stmt.setString(2, jlocat);

        stmt.setString(3, booknum);

        stmt.setString(4, ticketnum);
 

        // execute and retrieve the result set

        stmt.execute();

        ResultSet rs = (ResultSet)stmt.getObject(1);
 

        // print the results

        while (rs.next()) {

            System.out.println(rs.getString(1));

            

        }
 

        rs.close();

        stmt.close();

    }

Open in new window

0
Comment
Question by:sf_gray
4 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 24354325
What does the following give?
System.out.println(stmt.getObject(1).getClass());

Open in new window

0
 
LVL 12

Accepted Solution

by:
Gibu George earned 175 total points
ID: 24354735
You have registered out parameter as VARCHAR, then you are trying to cast the object to result set.
String rs = (String)stmt.getObject(1);
0
 
LVL 4

Assisted Solution

by:pellep
pellep earned 75 total points
ID: 24355071
What does the actualy query look like you're executing (String query = useOracleQuery ? oracleQuery : genericQuery)?

If it's something like:
"begin ? := OMSOWNER.PRC_CCA_DELETE_INMATE_FUNCTION(?,?,?); end;"
And the function returns a VARCHAR, then your code should work fine if you change
ResultSet rs = (ResultSet)stmt.getObject(1);
to
String s = stmt.getString(1);
(like gibu_george alerady suggested).

Also, if you're returning a simple VARCHAR, you shouldn't mess around with OracleTypes, just specify it as java.sql.Types.VARCHAR and let the JDBC driver worry about type marshalling.

If, on the other hand, you want a resultset to work with, the Oracle-function will have to return a REFCURSOR.
0
 

Author Closing Comment

by:sf_gray
ID: 31580134
Thank you, so much.  I really appreciate it.  I hope my point split was fair.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now