# Help trying to call a stored procedure

**URL:** <https://forum.inductiveautomation.com/t/help-trying-to-call-a-stored-procedure/98369>\
**Category:** Ignition\
**Tags:** ignition81, namedqueries\
**Created:** [November 7, 2024, 9:29pm UTC](https://forum.inductiveautomation.com/t/help-trying-to-call-a-stored-procedure/98369 "2024-11-07T21:29:07Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Leonardo\_Lozada](https://avatars.discourse-cdn.com/v4/letter/l/35a633/32.png) [@Leonardo\_Lozada](https://forum.inductiveautomation.com/u/Leonardo_Lozada)\
**Post date:** [November 7, 2024, 9:29pm UTC](https://forum.inductiveautomation.com/t/help-trying-to-call-a-stored-procedure/98369/1 "2024-11-07T21:29:07Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/f/9f3cd3c54e38addbaccdc03100616725dd8f5174.png)

This is the query:

```python
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/a/7a55cb615854b27a584b28e61e27f879645f2e07.png)

---

<div class="post-metadata">

**Author:** ![Benjamin\_Furlani](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/benjamin_furlani/32/66084_2.png) [@Benjamin\_Furlani](https://forum.inductiveautomation.com/u/Benjamin_Furlani)\
**Post date:** [November 8, 2024, 4:30pm UTC](https://forum.inductiveautomation.com/t/help-trying-to-call-a-stored-procedure/98369/2 "2024-11-08T16:30:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [November 8, 2024, 5:30pm UTC](https://forum.inductiveautomation.com/t/help-trying-to-call-a-stored-procedure/98369/3 "2024-11-08T17:30:48Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![Leonardo\_Lozada](https://avatars.discourse-cdn.com/v4/letter/l/35a633/32.png) [@Leonardo\_Lozada](https://forum.inductiveautomation.com/u/Leonardo_Lozada)\
**Post date:** [November 8, 2024, 9:01pm UTC](https://forum.inductiveautomation.com/t/help-trying-to-call-a-stored-procedure/98369/4 "2024-11-08T21:01:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [November 8, 2024, 9:28pm UTC](https://forum.inductiveautomation.com/t/help-trying-to-call-a-stored-procedure/98369/5 "2024-11-08T21:28:52Z")

</div>

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

```python
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.