I’m having some trouble trying to create a simple stored procedure group in Ignition build 4594. I have this procedure created in Oracle that outputs the input string twice:
CREATE OR REPLACE PROCEDURE ENG.IGNITION_TEST ( p_input VARCHAR2, p_output OUT VARCHAR2) IS
BEGIN
p_output := p_input || p_input;
END IGNITION_TEST;
I have a SP group configured to use a DB Sqltag tied to a text field as p_input, and I want to write p_output to a DB Sqltag tied to a Label. A one-shot button is bound to a DB Sqltag that acts as the trigger. When I click the one-shot button, the group errors. I am receiving the error seen in the attached screenshot. I’m guessing I have something configured incorrectly. I’ve tried many different combinations of target/output settings, and the section of the manual on SP groups doesn’t go into too much detail on it. Any help is greatly appreciated.
Ok, well, I’ve looked and looked and tested and tested, and as far as I can tell Oracle should support the method we’re using to pass parameters. However, I can’t get it to work on 10g, and am getting the same error you are.
One thing we’ve done that has at least created a work around is to allow specifying parameters by index. So, in your example, p_input = 0, and p_output = 1 - with version 7.0.6, you’ll be able to just enter those indexes as parameter names, and it will work.
I’m going to keep looking into the name issue, because the oracle docs say it should work…
Thanks, Colby. We’re attempting to replicate the functionality of the batching component of our legacy system as a proof of concept, so this is definitely a critical component. Do you have an ETA on 7.0.6?
We’re hoping sometime very soon, as in tomorrow or friday. We’re waiting for some feedback about an issue we’re having with the internal database… however, I may be able to get you a development version of the SQL Bridge module sooner, which should let you continue.
I installed the new version, and the stored procedure groups now work with Oracle when I use the parameter index rather than parameter name. While this is certainly enough to move forward, it would be nice if the issue could be resolved to use the actual parameter name. Some of our procedures have upwards of 25 parameters, so it can get a little confusing!
I agree, and so we still have the ticket open in our system for improvement in (hopefully) 7.1.
We’ll probably have to start by seeing if there’s a new version of the Oracle JDBC driver, since that seems to be where the problem lies… but anyhow, we’ll put some more work into it soon.
Today i downloaded the Oracle JDBC driver (for Oracle 10g 10.2.0.4) and everything works fine. It looks like that was the problem. So now i can execute stored procedures indicating the name of parameters and not the index of it.