CODEWITHSUNDEEP
javasqlstored-proceduresplsqlcall
DeaIss
DeaIss

Reputation: 2585

Why won't this stored procedure call from Java work?

Here is my stored procedure:

CREATE OR REPLACE PROCEDURE VIEWBROKERS 
(o_username OUT USERS.USERNAME%TYPE) 

AS 
BEGIN
 SELECT USERNAME
 INTO o_username
 FROM USERS
WHERE Role_ID = 3 ;

END VIEWBROKERS;

Here is my method calling the stored procedure:

public ResultSet pullBrokers() {
    ResultSet rs = null;
    try {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        con = DriverManager.getConnection(Messages.getString("OracleUserManagement.0"), Messages.getString("OracleUserManagement.1"), Messages.getString("OracleUserManagement.2")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

        String storedProcedure = "{call VIEWBROKERS(?)}"; 
    CallableStatement statement = con.prepareCall(storedProcedure);

    statement.registerOutParameter(1, java.sql.Types.VARCHAR);
    rs = statement.executeQuery();

    con.commit();
    con.close();

} catch (SQLException e) {
    e.printStackTrace();
}
return rs;
}

And lastly when I tried to print out the results:

public class TEST {
    public static void main(String[] args) throws SQLException{
        OraclePullListOfUsers pull = new OraclePullListOfUsers();

        ResultSet rs = pull.pullBrokers();
        try {
            while (rs.next()){
                System.out.println(rs.getString(1));
        }
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
}

I get the error message ORA-01422: exact fetch returns more than requested number of rows

Which is strange ,because there are only two rows of data in the table...

If someone could point me in the right the direction, that would be awesome!

Upvotes: 1

Views: 546

Answers (2)

user272735
user272735

Reputation: 10648

The root cause for your problem:

ORA-01422: exact fetch returns more than requested number of rows

is that PL/SQL select into statement expects a query to match to exactly one row. If the query returns no rows or if the query return more than one row (as in your case) it will throw an exception.

You can't use select into to save the results to a single out variable if the query can return more than one row. Instead your subprogram should return a cursor (that is a pointer to a record set) that your Java component can query. Note that returning a cursor is not the only option, but in your case it looks like a good starting point.

This issue has been addressed several times in StackExchange universe. Please take a look e.g.

A Java example Using Ref Cursors To Return Recordsets.

Upvotes: 1

Luis Sep
Luis Sep

Reputation: 2402

Looks like you're problem is not related to Java, just on the SQL side. Could it be that both those two rows in the table have Role_ID=3?

Upvotes: 1

Related Questions