Oracle Stored Procs & Named Queries

Ignition
1

Hey folks,

I realize that this is not an Oracle support forum, but I am hoping someone has advice on how to do this...

We're migrating a series of stored procedures from MSSQL to Oracle per customer request. I've run into issues with the SELECT procs; specifically Ignition's DB engine doesn't seem to support the oracle cursor type, nor receiving a series of out params. Oracle doesn't appear to offer a standard dataset return

I have been able to execute stored procedures via scripting by defining OUT parameters.

I can run the contents of any stored procedure directly from Ignition's DB browser or a named query.

My ask: Is there any way to return an Ignition query-friendly dataset from an Oracle Stored Procedure? Or are they incompatible (as they appear thus far)?

Any assistance would be greatly appreciated.

2

Hi @AIO,

This topic was actually fairly recently discussed on a previous thread here, and has some potential workarounds discussed:

1 Like
3

Thanks, Austin - I appreciate the reply.

I found that same post. From my experience, Oracle's method for stored procedures does not integrate well with Ignition unless you intend to script every stored procedure call.

Our solution was to put INSERT / UPDATE procs into scripts and pull heavy SELECT procs out of stored procedures and move them into named queries in Ignition.

It's been a pretty excellent headache in that regard. :sweat_smile:

TL:DR - Would not recommend Oracle for integration with Ignition.