Solved

JDBC Callable Statement Casting Exception

Posted on 2009-05-11
4
817 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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
print map entry 34 69
hibernate insert example 13 29
Java exception bubble up 2 18
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO) 1 18
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)
An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
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:

803 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