I'm in 8.1 and trying create a script to run a stored procedure in Oracle which returns SYS_REFCURSOR datatype. I want to get the result into a dataset. I'm not sure what to put as the datatype in call.registerReturnParam() or how to get the into a dataset.
Pretty sure you won't be able to do that. The Oracle JDBC examples require you to register the parameter as
oracle.jdbc.OracleTypes.CURSOR, which is not available in Ignition. (Only generic JDBC types are supported in Ignition--the actual drivers are in isolated classloaders in order to make connection pooling transparent.)
You'll probably have to wrap that procedure with another that explicitly SELECTs from that cursor. (Looks complicated--wrapping in a TABLE() function somehow. I've not used Oracle in years....)
This seems to be the correct answer. Although, I'm not sure how to do what you're suggesting. I tried changing it to output an array, but I'm not having success retrieving the data. Would you have any suggestion to point me in the right direction?
Sorry, I don't know enough about the TABLE() function in Oracle. If PostgreSQL, it would be trivial.
If you can alter the procedure, arrange for it to perform the proper SELECT in the clear. That will deliver the resultset to Ignition.
Can you use a Stored Procedure to manage the workload in the database?
I expect that would be much more efficient.
My understanding of SYS_REFCURSOR is that it's internal to the RDBMS for looping through data - I'm not sure how Ignition would instruct the cursor in Oracle to getNext() or terminate the cursor.
If you need Ignition to loop through data, try using a NamedQuery to SELET the data and then work with the dataset.
If you stick with system.db.runPrepQuery, you can use system.dataset.toDataSet to convert a pyDataSet to a dataset if that is what you need.