Event calling a stored procedure not working

I have a button for testing purposes which calls a stored procedure from an Oracle DB, executes it, and then fills it values from Ignition into the Oracle DB.
The stored procedure does the following:
It takes values from four arrays (lot_number, part_number, heat_code, and number_of_parts), uses lot_number_index to go through the array index, and simply places them into a table within the Oracle DB. Pretty straight forward.

I have got the same exact code working for a MSSQL DB.
I also can execute the calling of the stored procedure if I feed it direct values and have it fill in the DB.

However whenever I run the same script for the Oracle DB I run into this issue:

image

image

image

When I look up this TypeError online it seems as if it says I am feeding the range () a float value instead of an integer. The thing is I know for a fact that the number_of_lots is reading a tag that is an integer.

Thoughts?

can you repost your script using three ticks ``` before and after it? that way we can actually read it.

You’re number_of_lots variable is None instead of an integer so your loop is not starting because range(0, None) - python doesn’t know what to do with this. So you’re not getting to the stored procedure logic.

1 Like
	number_of_lots = system.tag.read('[BladesideHT]BS/FurnaceBT{}/Recipe/Control/lot_number_index'.format(self.view.params.BrassTagNum)).value
	for i in range(0, number_of_lots):
		tags = []
		for tag in ['lot_number', 'part_number', 'heat_code', 'number_of_parts']:
			tags.append('[BladesideHT]BS/FurnaceBT{}/Recipe/Lots/{}[{}]'.format(self.view.params.BrassTagNum, tag, i))
		values = [tag.value for tag in system.tag.readAll(tags)]
		
		call = system.db.createSProcCall("ga_cfb_dw_proc.CFB_BF_CREATE_LOT_RECORD","DB")

		call.registerInParam(1,system.db.VARCHAR,values[0])
		call.registerInParam(2,system.db.VARCHAR,values[1])
		call.registerInParam(3,system.db.VARCHAR,values[2])
		call.registerInParam(4,system.db.INTEGER,values[3])
		call.registerOutParam(5,system.db.VARCHAR)
		call.registerOutParam(6,system.db.VARCHAR)

		system.db.execSProcCall(call)


I did realize the tag provider BladesideHT should be BladesideSR. This eliminated that error message.
Now code is

	number_of_lots = system.tag.read('[BladesideSR]BS/FurnaceBT{}/Recipe/Control/lot_number_index'.format(self.view.params.BrassTagNum)).value
	for i in range(0, number_of_lots):
		tags = []
		for tag in ['lot_number', 'part_number', 'heat_code', 'number_of_parts']:
			tags.append('[BladesideSR]BS/FurnaceBT{}/Recipe/Lots/{}[{}]'.format(self.view.params.BrassTagNum, tag, i))
		values = [tag.value for tag in system.tag.readAll(tags)]
		
		call = system.db.createSProcCall("ga_cfb_dw_proc.CFB_BF_CREATE_LOT_RECORD","DB")

		call.registerInParam(1,system.db.VARCHAR,values[0])
		call.registerInParam(2,system.db.VARCHAR,values[1])
		call.registerInParam(3,system.db.VARCHAR,values[2])
		call.registerInParam(4,system.db.INTEGER,values[3])
		call.registerOutParam(5,system.db.VARCHAR)
		call.registerOutParam(6,system.db.VARCHAR)

		system.db.execSProcCall(call)

However now I get the following error message…

I know its small so here is error text on second picture.

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
File “function:runAction”, line 18, in runAction
java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()

caused by org.python.core.PyException

Traceback (most recent call last):
File “function:runAction”, line 18, in runAction
java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()

caused by Exception: Error executing system.db.execSProcCall()
caused by SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at “GA_CFB_DW_PROC.CFB_BF_CREATE_LOT_RECORD”, line 38
ORA-00001: unique constraint (GA_CFB_DW_PROC.SYS_C008802) violated
ORA-06512: at “GA_CFB_DW_PROC.CFB_BF_CREATE_LOT_RECORD”, line 26
ORA-06512: at line 1

caused by oracle.jdbc.OracleDatabaseException
Error : 6502, Position : 0, Sql = BEGIN ga_cfb_dw_proc.CFB_BF_CREATE_LOT_RECORD(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ) ; END;, OriginalSql = { call ga_cfb_dw_proc.CFB_BF_CREATE_LOT_RECORD(?,?,?,?,?,?) }, Error Msg = ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at “GA_CFB_DW_PROC.CFB_BF_CREATE_LOT_RECORD”, line 38
ORA-00001: unique constraint (GA_CFB_DW_PROC.SYS_C008802) violated
ORA-06512: at “GA_CFB_DW_PROC.CFB_BF_CREATE_LOT_RECORD”, line 26
ORA-06512: at line 1

Ignition v8.0.14 (b2020062220)
Java: Azul Systems, Inc. 11.0.6

ORA-00001: unique constraint (GA_CFB_DW_PROC.SYS_C008802) violated

Looks like the stored procedure is failing due to some data integrity constraint. Are you able to run the stored procedure with those exact values in workbench? I would be surprised if you can.

1 Like

What do you mean by workbench?

The SYS_C008802 is the index_name in this oracle DB.

In order to add a new row you can’t have the same lot number that is already in a row. All lot numbers have to be unique. I think I got this error message initially when I tried to manually set the four values I was sending in more than once when the values were the same.

Is the loop not incrementing i (or the lot number index and therefore it is trying to put the same index value in twice?

For example I have lot_number[0] set to L11, lot_number[1] set to L12, lot_number[2] to set L13, and lot_number[3] set to L14.

Also here is the stored procedure code:

Looks like I am getting somewhere. I set the lot_number_index to 1 so it is only looking at the first array position (position 0) to grab the values from and it worked. Question now is why is the for loop not correctly indexing through? In otherwords if there are four lots it should look through array index 0 through 3. Why isn’t this range function working correctly?

I would say it is working correctly, just not the way you are expecting.

Functionally, range(0,number_of_lots) is equivalent to range(number_of_lots) either will generate a series of (0,1,...number_of_lots - 1) . So the question is, what did you expect range(0,number_of_lots) to do?

If 0 is not a valid index, then you can do range(1,number_of_lots + 1) this will produce a set of (1,2,...,number_of_lots).

1 Like

Turns out it is! You were correct. The problem was I did actually already have L11 in the table so that is why it wasn’t working. Once I cleared out the table values it worked for 4 lots and 6 lots as long as the lot number was different.

1 Like

Thanks all for the help! A few simple mistakes on my part and it is good to go!

L11 in the table so that is why it wasn’t working

Ah, so that is why you had the unique constraint violation.

1 Like