CODEWITHSUNDEEP
oracle-databasejdbcplsqlora-06553
BeginnerAmongBeginners
BeginnerAmongBeginners

Reputation: 279

Retrieving Oracle Cursor with JDBC

I have been experiencing some frustrations trying to make a simple Oracle cursor retrieval procedure work with JDBC.

I keep on getting an error of "[Oracle][ODBC][Ora]ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETNAME'", but I cannot figure out what I am doing wrong.

Here is my code in Java:

CallableStatement stmt = connection.prepareCall("call getName(?)");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute();

stmt.close();
con.close();

Here is my procedure in Oracle:

CREATE OR REPLACE PROCEDURE getName(cur out SYS_REFCURSOR)
IS
BEGIN
    OPEN cur FOR
        SELECT name FROM customer;
END;

The error occurs on stmt.execute().

Thanks in advance.

By the way, I am working with Oracle 10.2.0.

Upvotes: 0

Views: 2667

Answers (2)

Dave Costa
Dave Costa

Reputation: 48111

I tried essentially the same thing and it worked for me. The only difference was that the Oracle JDBC library I am using does not have a method registerOutputParameter; I used registerOutParameter instead. Perhaps you are calling a generic JDBC method instead of the Oracle-specific one that support Oracle types.

The only other explanation I can think of is that your Java code is connecting to the wrong schema, and accessing a different getName object.

Upvotes: 2

duffymo
duffymo

Reputation: 308763

Nope, this is wrong. You should not be returning a raw cursor. You should call the stored proc and iterate through the ResultSet.

Upvotes: 0

Related Questions