JDBC Code Change From SQL Server to Oracle

Question

In the JDBC code, I have the following that is working with SQL Server:

CallableStatement stmt = connection.prepareCall("{ call getName() }");

ResultSet rs = stmt.executeQuery();

if(rs != null)
{

  while(rs.next())
  {

    //do something with rs.getString("name")

  }

}

Multiple rows are returned for the above situation.

I understand that the use of a cursor is required to loop through the table in Oracle, but is there any way to keep the above code the same and accomplish the same thing?

Sample PL/SQL code would be much appreciated.

Thanks in advance.

Answer 1

This is straight JDBC, so it'll work with any database that has a valid JDBC driver.

It assumes, of course, that the stored proc exists in both and that you aren't using any non-standard, vendor-proprietary code in your class.

Answer 2

You could implement getName() as a pipelined function:

CREATE OR REPLACE name_record AS OBJECT ( name  VARCHAR2(100) );
/

CREATE OR REPLACE name_table AS TABLE OF name_record;
/

CREATE OR REPLACE FUNCTION getName RETURN name_table PIPELINED
AS
  n  name_record;
BEGIN
  -- I have no idea what you're doing here to generate your list of names, so
  -- I'll pretend it's a simple query
  FOR i IN (SELECT name FROM someTable) LOOP
    n := name_record( i.name );
    PIPE ROW(n);
  END LOOP;
END;
/

You would need to change the actual query in Java to SELECT name FROM TABLE(getName()).