Hi,
Cutting right to the chase: In some elements and automation scripts we created and maintain ourselves we have the need to query some data from external databases (topology, offload, etc.). To achieve this we have been using the methods provided by Skyline’s SLDatabase.dll library. This works fine for the usual select queries we need to execute from such classes.
However we are, right now, faced with a strange situation that we’d need some feedback from the developer(s) of this library or someone else who could provide such feedback. Here is the situation:
A colleague of ours has developed an oracle SQL function that returns a cursor type. One way to execute such a function is to do a select query that looks like this: select function_name (input param 1, input param 2, …) from dual;
When we try this in oracle sql developer we see that the function executes fine and returns a valid result of the type cursor.
However if we try to execute this select query using the SLSql.Query method provided by SLDatabase it seems like we never receive a result back. This method works well when doing a "normal" select query from a regular table so my current suspicion is that this method might not support the cursor type. Either that or maybe a different method has to be used in this case.
Is it possible to get some feedback on this? Is there a way we can execute this query and retrieve the resulting cursor?
Here is a screenshot of the result in oracle sql developer.
Hey Tiago,
This should be possible by using the following features.
- Use the SLOracle class in SLDatabase if you are connecting to an oracle database.
- Execute the query using the ScalarQuery() method (either directly on SLOracle, or by wrapping it in a OraclePreparedStatement).
The result of this should be an object, which can then be cast to Oracles OracleRefCursor for further usage.
Kind regards,
Hey Brent,
Thanks but I’m still having a hard time making this work.
The methods I’m using are:
SLOracle offloadDbConnectionManager = new SLOracle(offloadDbConnectionString);
Object res = offloadDbConnectionManager.ScalarQuery(ingressQuery, 900000);
OracleRefCursor cursor = (OracleRefCursor)res;
OracleDataReader oReader = cursor.GetDataReader();
offloadDbConnectionManager.Close();
However the scalarquery never method does not seem to return any value and so the Object res variable is never populated. What is wrong?
I can provide a test setup if needed.
Thanks.