Issues with Calling Oracle Stored procedure

General Discussion
,
1

I'm having trouble with using/calling stored procedures in Oracle DB. The stored procedure that return a data result set in table form are easy to use. I've used the database query browser with no problem. My problem is that I don't know how or if Ignition will allow me to use stored procedures with in and out parameters.

image
image1214×615 24.1 KB

How do I use this stored procedure using the script console in Ignition?

2

Start here:

2 Likes
3

I don't know if I have the syntax correct or not.

image
image1388×594 22.3 KB

4

The syntax error is improper indentation. Spaces matter to python. Look at the red squiggly mark at the point of the error.

I doubt you will be able to return an Oracle cursor.

5

Another way we've done this many many times using SQL Server procedures was to just put your EXEC statement into a named query.

So, an example of calling an MSSQL procedure will look something like this:

EXEC [schema].[procedureName]
    @paramName1 = :ParamName1,
    @paramName2 = :ParamName2,
    ...
    @paramNameN = :ParamNameN

where :ParamNameN is a Named Query parameter.

I'm not familiar at all with Oracle DB, but I would venture to guess you can probably use a similar strategy?

1 Like
6

Not for out or inout parameters.

1 Like
7

If there are many output parameters, I agree - there isn't really a way to return them. But what does work in SQL Server is something like

-- could also be a table
DECLARE @myVar BIGINT;

EXEC [schema].[procedureName]
    @paramName1 = :ParamName1,
    @paramName2 = :ParamName2,
    ...
    @paramNameN = @myVar        -- @paramNameN is OUTPUT

SELECT @myVar;
-- or SELECT * FROM @myVar; if @myVar is a table

Then you would set the query type based on if you return one value or many. This doesn't really answer the Oracle question, but is a SQL Server equivalent

8

I avoid brand-specific functionality. You are using a SQL script. Vanilla JDBC only explicitly supports SQL statements. Some JDBC drivers support scripts, but they only work by accident in Ignition.

And Ignition will seriously screw up your NQ if you have a colon-delimited parameter embedded in a SQL comment.

I strongly recommend you not use this technique, to protect your future self from changes in JDBC drivers and/or Ignition implementation details.

(Comments, DECLARE, conditional blocks, and any use of @ parameters are features of SQL scripts. Do not use them from Ignition. If you absolutely need a script, define it entirely in the database.)

2 Likes