All.
We have an Oracle package that returns a ref cursor:
CREATE OR REPLACE PACKAGE BODY sandbox AS
FUNCTION my_function (text VARCHAR2) RETURN result_cv IS result result_cv;
BEGIN
OPEN result FOR SELECT MLS_SID FROM MLS;
RETURN result;
END;
END sandbox;
I am calling the function with the following scala code:
lazy val database = Database.forDataSource(DB.getDataSource())
database withSession {
val x = sql"select sandbox.my_function($text) from DUAL".as[(Int)]
x foreach (x => println(x))
Ok(String.valueOf(x.first))
}
The code fails with the following error:
[SQLException: Invalid column type: getInt not implemented for class oracle.jdbc.driver.T4CResultSetAccessor]
The SQL statement works when I just use the select statement that is in the function (SELECT MLS_SID FROM MLS;), but when I open it as a ref cursor and return the ref cursor it fails. I looked at the T4CResultSetAccessor and it only has one method getBytes().
Can anyone offer suggestions on how to make this work using the Oracle function call and ref cursors? Thanks in advance.
-patrick