How to call oracle stored procedure

I currently have several SQL queries I am executing with a mariadb database stored locally on the same machine running ignition.

They now want to use an external oracle database along with stored procedures that I will call/execute within Ignition. I can’t seem to find any direct information on how to call Oracle procedures and how to pass in parameters for the queries. Also I have most of these queries within project scripts, but I also have one query as a named query, and then two other queries in a report as data sources, and not sure I can call them the same way within each instance.

Below are a couple of the queries in a script:

	txId = system.db.beginTransaction(timeout=10000)
	# Create furnace cycle record
	sql = """INSERT INTO rpt_furnace_cycles (PartID, OperationID, Alloy, Username, FurnaceBTNum, r_LagTime, r_ReqTotNegTime, r_ReqTotPosTime,
			 r_ReqProcessTime1, r_ReqProcessTemp1, r_ReqTempTol1, r_ReqProcessTime2, r_ReqProcessTemp2, r_ReqTempTol2, r_ReqProcessTime3,
			 r_ReqProcessTemp3, r_ReqTempTol3) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
	tags = []
	for tag in ['part_number', 'operation_number', 'alloy', 'username']:
		tags.append('[BladesideHT]BS/FurnaceBT{}/Recipe/Lots/{}'.format(furnace_brass_tag_num, tag))
	for tag in ['LagTime', 'ReqTotNegTime', 'ReqTotPosTime', 'ReqProcessTime1', 'ReqProcessTemp1', 'ReqTempTol1', 'ReqProcessTime2',
				'ReqProcessTemp2', 'ReqTempTol2', 'ReqProcessTime3', 'ReqProcessTemp3', 'ReqTempTol3']:
		tags.append('[BladesideHT]BS/FurnaceBT{}/Recipe/Setpoints/in/{}'.format(furnace_brass_tag_num, tag))
	params = [tag.value for tag in system.tag.readAll(tags)]
	params.insert(4, furnace_brass_tag_num)
	furnace_cycle_id = system.db.runPrepUpdate(sql, params, tx=txId, getKey=1)
	system.tag.write('[BladesideHT]BS/FurnaceBT{}/Recipe/Lots/furnace_cycle_id'.format(furnace_brass_tag_num), furnace_cycle_id)

	# Get number of jobs and create records for each
	number_of_jobs ='[BladesideHT]BS/FurnaceBT{}/Recipe/Control/lot_number_index'.format(furnace_brass_tag_num)).value
	for i in range(0, number_of_jobs):
		# Job table record
		sql = 'INSERT INTO rpt_jobs VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE ProcessNum=VALUES(ProcessNum), HeatCode=VALUES(HeatCode), NumParts=VALUES(NumParts)'
		tags = []
		for tag in ['job_number', 'process_number', 'heat_code', 'number_of_parts']:
			tags.append('[BladesideHT]BS/FurnaceBT{}/Recipe/Lots/{}[{}]'.format(furnace_brass_tag_num, tag, i))
		params = [tag.value for tag in system.tag.readAll(tags)]
		system.db.runPrepUpdate(sql, params, tx=txId)
		job_number = params[0]
		# Mapping table record
		sql = 'INSERT INTO rpt_jobs_furnace_cycles VALUES (?, ?)'
		params = [job_number, furnace_cycle_id]
		system.db.runPrepUpdate(sql, params, tx=txId)

Here is a query within the reports data source:

SELECT rpt_jobs.JobID, rpt_jobs.ProcessNum, rpt_jobs.HeatCode, rpt_jobs.NumParts
FROM rpt_jobs
JOIN rpt_jobs_furnace_cycles ON rpt_jobs.JobID=rpt_jobs_furnace_cycles.rpt_jobs_JobID
WHERE rpt_jobs_furnace_cycles.rpt_furnace_cycles_id={FurnaceCycleID}

and here is the named query using one parameter ‘job_id’ as an input parameter:

SELECT, rpt_furnace_cycles.FurnaceBTNum, rpt_furnace_cycles.e_RecipeDownloaded_ts AS cycle_start, 
rpt_furnace_cycles.e_DoorOpenedUnload_ts AS cycle_end
FROM rpt_jobs_furnace_cycles
JOIN rpt_furnace_cycles ON
WHERE rpt_jobs_furnace_cycles.rpt_jobs_JobID=:job_id

Any general or specific help would be greatly appreciated!

Within the Python Scripting you can use the system.db.execSProcCall method to run and execute a stored procedure. While I have not done it on an oracle server, I have done it successfully on MSSql servers and MySQL servers so it should be pretty much the same.