I want to fetch data from Oracle 12c database PL/SQL using Select operation. I have 3 options to return data. 1. Using Sys_RefCursor
Oralce Part
Procedure Get_Data(
o_Cursor In Out SYS_REFCURSOR,
i_Row_Id In Number
)
Is
Begin
Open o_Cursor For
Select * From My_Table Where Row_Id = i_Row_Id;
End;
Java Part
CallableStatement cs = conn.prepareCall("{call My_Package_Name.Get_Data(?,?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.setInt(2, data.get("row_id").getAsInt());
cs.execute();
rs = (ResultSet)cs.getObject(1);
But, in this case, I don't know Oracle Procedure itself closing the Cursor implicitly or not.
When I use
Close o_cursor
at the end of procedure it is not giving me desired result.
Using Custom Apex Json since I used Oracle 12c it supports Json format so I can return my resultSet row using custom Json
Select * Into rt From My_Table t Where t.Row_Id = 1;
json.Push('row_id', rt.Row_Id);
json.Push('row_name', rt.Row_Name);
Return json.to_string();
And reading it as string in Java
Can I use
TYPE CUSTOMER_REC IS RECORD
(
CUST_NO NUMBER,
CUST_CODE VARCHAR2 (50),
CUST_NAME VARCHAR2 (500)
);
Returning my select resultSet as CUSTOMER_REC format and in Java read it as
cs.registerOutParameter(1, OracleTypes.STRUCT, "typeName");
Can someone give detailed explanation about each part and which is better to use (performance perspective) and which is old approach in this case ? and Is Sys_RefCursor closed implicitly by Oracle Environment?
As your can see for yourself in the code for option 1, the SYS_REFCURSOR maps to a ResultSet in Java, so the cursor is closed when you close the ResultSet.
Note that ResultSet objects are automatically closed when the Statement is closed, or when the Statement executes another SQL statement, and that the Statement is closed when the Connection is closed.
Even for pooled connections, where calling connection.close() returns the connection to the pool, so the physical connection remains open, the call to close() still closes all Statement objects on the Connection, and hence closes all ResultSet objects too.
If your code runs for a while after using a ResultSet, and especially if your code executes a loop to process multiple ResultSet objects, you should always close the ResultSet objects as soon as you're done with them, preferably using try-with-resources.
If connection is closed soon after using a ResultSet object, you can rely on the connection closing the ResultSet objects for you. If you're not sure, then close them yourself.
As for your question, option 1 is best. Option 2 would be much slower, and option 3 is more work.