Parameterized query output not working

[pvanzyl]

I need to read and write data out of an Oracle database and am finally starting to see some success.  This forum post was my first attempt at connecting to Oracle. https://forums.ni.com/t5/LabVIEW/Performing-a-database-call/td-p/3332420 

The next step is to actually get Labview to do some of the tasks that need to be performed.  One of our IT guys wrote another procedure in Oracle that I am trying to call (dgeitz.get_catalog).  The purpose of this VI is to find out what the product number is if I know a serial number.  This procedure has two variables that are passed between Oracle and Labview.  The first is the Serial number (GC_SERIALNO).  The second value is the product number (CG_CATALOG_NO) and should be sent back from Oracle.

The question is, how do I get the value back out of Oracle?

Here is how the Oracle script works:

1) Labview sends a serial number to Oracle (CG_SERIALNO)

2) Oracle sends email to a user as a test that the serial number was received.  This is for testing purposes only.

3) Oracle retrieves the product number (CG_CATALOG_NO)

4) Oracle sends email to a user as a test that the product number was retrieved.  This is for testing purposes only.

5) Oracle procedure ends when it sends the parameter back to Labview

When I run the attached VI, I can send serial number values to Oracle.  If I send an invalid serial number I get no errors from Oracle, however if I send a valid serial number then I get the following error. 

Under both conditions, I get no output of the product number (CG_CATALOG_NO), so the VI fails to perform its task either way.  The Oracle procedure was tested by running the script manually in an Oracle command prompt like interface (please excuse my ignorance) and it returned the CG_CATALOG_NO value every time.

The most likely explanation for the error above is that when I send an invalid serial number, then the Oracle script breaks and does not complete as it has no way of dealing with an error condition.  Nothing is communicated to Labview and Labview appears to be happy.  When I send a valid serial number, then something is returned to Labview and it has no idea what to do with that information.  

At this point, I am very stuck and I cannot find any examples on how to deal with this situation.  I am including my VI and the SQL statement.  Any help is very much appreciated.

Other bits of information that may (or may not) be helpful:

Labview 2015

Oracle 11g

[aputman]

If you go back to the other post, I added some code that will allow you to specify the direction of the parameter (input/output).  If you pass the stored procedure an output parameter, and populate that variable within the stored procedure, all you have to do in Labview is use the Get Parameter value VI, using the name or index of the output parameter as the input. 

This is the code I use for stored procedures.  The first case statement after the Execute Query VI returns data from a SELECT statement, the second case statement returns IDENTITY (the auto incremented value of a table) as a parameter when needed. 

In MSSQL, you have to declare the variable as output.  You can see below that I have @identity declared as an output variable.

[aputman]

I should add that I can't open your VI.  I'm on 2012.

[pvanzyl]

I followed your code and it seems to work similar to mine, albeit mine is much more basic and not nearly as capable.  I see that you run the "free object" prior to running the "get parameter".  Is this necessary to work? Below is an image of my code.

Regarding the Oracle code, I believe that the GC_CATALOG_NO variable is set to output.  I am not familiar with sql coding at all, but I believe it is in the first few lines of the procedure in my original post.

[aputman]

You are getting the parameter at index 0.  Index 0 is your input parameter.  

[pvanzyl]

You are correct.  The parameter index I read in the above image is the Serial number that I send.  That is the only value I am able to retrieve.  If I set it to 1, then it returns a blank value.  Keep in mind, this only happens if I send an invalid serial number.

When I send a valid serial number, I get the "Invalid SQL Statement" error as shown in the first post.

[aputman]

What do you mean "invalid serial number"?  Give an example of a valid serial number.  What type is the database field for serial number?  I'm not seeing that in your procedure but I don't know Oracle, so maybe I am overlooking it.

[pvanzyl]

A valid serial number would be "ET192199" or in other words, something that already exist in a table.  Anything other than that would be invalid. e.g. "192199ET".  From what I have been told, the database field type for both GC_SERIALNO and GC_CATALOG_NO is string.

I found this knowledgebase article and am currently working through it. Does it have something to do with reserving memory for the "output" as discussed in the article?

Parameters from an SQL Stored Procedure with the Database Connectivity Toolkit?

You must also reserve memory space for the output parameter before the query is executed.  If this is not done, the returned output parameter will likely be truncated or not returned at all.  This can be accomplished by entering a default value in the Initial Value field of the cluster.  This initial default value must be at least as long as the output parameter value that you are expecting.  

---

@aputman wrote:

What do you mean "invalid serial number"?  Give an example of a valid serial number.  What type is the database field for serial number?  I'm not seeing that in your procedure but I don't know Oracle, so maybe I am overlooking it.

---

[aputman]

Yes that very well could be your problem.  I guess I didn't realize that was a requirement but in my implementation, I build the parameter list like I showed you and I do wire a default value for the output parameter.  I don't know if wiring an empty string will be enough for your situation since the statement says that the default value must be as long as the expected output.  

Why don't you just use a select statement at the end of your stored procedure that would return the value as a recordset rather than a parameter?  So instead of saying:

GC_CATALOG_NO := V_CATALOG_NO;

modify this to say

SELECT V_CATALOG_NO;

[pvanzyl]

aputman,

I spoke to our IT staff and they indicated that they cant put a select statement into their procedure.  In the end, we gave up on this method and figured out that we can use a function within Oracle.  The Oracle funciton would end up putting the data I am looking for into a table column that I have to reference with a select statement.  In our case the column name is dgeitz.get_catalog6('123456').

It is not pretty, but it works.  I hate to give up, but we are under a bit of a crunch with this project and need to keep moving.  Thanks for all of your help.