Help trying to call a stored procedure

Ignition
,
1

I am trying to integrate a new PLC with an existing Oracle DB using Ignition. I need to call some stored procedures which I am trying to call using Named Queries, but I am getting an error message.

Following two screenshots, one of what I am trying to do and another with the error message, any help / guidance would be appreciated:

image
image1549×864 94.9 KB

This is the query:

BEGIN
	Assembly_V3.pGetBuildRecordData(:strlineid,
	:strmoduleid,
	:strstationid,
	:strhandlerid,
	:strlocationid,
	:strpalletid,
	:strserialno,
	:intjobno,
	:intbuildno,
	:strcurrenthandlerid,
	:strcurrentlocationid,
	:strcurrentpalletid,
	:strcurrentrouteto,
	:strcurrentserialno,
	:intfault,
	:strfaultmsg);
END;

And this is the error I get:

image
image582×384 9.22 KB

2

Simply based on the exception, something is attempting to create an array with a size of -1 which will throw an exception in java. Do you have access to the stored procedure to see what the code is actually doing?

You should verify that all of your values are the correct types.

On another note, I believe this is attempting to retrieve a value and return it to the client correct?

I think stored procedures like this require an output variable in oracle and then you will need to actually select that output variable for the Named Query to actually return that value. I am not an oracle master by any means so I may be mistaken.

3

I don't believe that the BEGIN and END will work. Not 100% certain but I think that may be considered SQL scripting which isn't strictly supported by the JDBC specification.

I would try this:

execute Assembly_V3.pGetBuildRecordData(:strlineid,
	:strmoduleid,
	:strstationid,
	:strhandlerid,
	:strlocationid,
	:strpalletid,
	:strserialno,
	:intjobno,
	:intbuildno,
	:strcurrenthandlerid,
	:strcurrentlocationid,
	:strcurrentpalletid,
	:strcurrentrouteto,
	:strcurrentserialno,
	:intfault,
	:strfaultmsg)
2 Likes
4

Hi, I have tried this but I get the following error:

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

I'll keep trying and come back here if I find a solution.

5

Here is how I pass arguments to a MSSQL stored procedure:

EXEC [schema].[procedure_name] :param01, :param02, etc.

Be sure the correct database is selected and whether or not the procedure is a query or update query.

Also, check permissions on the procedure in the DB/Server.

And, check the datatypes match.