Stored Procedure Error

kfells · February 14, 2020, 2:32pm

Hello,
I am getting the following error while executing a stored procedure call.
Can not insert Null into the identity column. The procedure call worked before, but I am not sure how to fix the issue.

Traceback (most recent call last):
  File "<event:actionPerformed>", line 51, in <module>
  File "<module:shared.notification>", line 46, in add
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:492)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.lang.reflect.Method.invoke(Unknown Source)


java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()

	at org.python.core.Py.JavaError(Py.java:495)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:492)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:427)
	at org.python.core.PyObject.__call__(PyObject.java:387)
	at org.python.core.PyObject.__call__(PyObject.java:391)
	at org.python.pycode._pyx17.add$1(<module:shared.notification>:49)
	at org.python.pycode._pyx17.call_function(<module:shared.notification>)
	at org.python.core.PyTableCode.call(PyTableCode.java:165)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:301)
	at org.python.core.PyFunction.function___call__(PyFunction.java:376)
	at org.python.core.PyFunction.__call__(PyFunction.java:371)
	at org.python.core.PyFunction.__call__(PyFunction.java:361)
	at org.python.pycode._pyx16.f$0(<event:actionPerformed>:56)
	at org.python.pycode._pyx16.call_function(<event:actionPerformed>)
	at org.python.core.PyTableCode.call(PyTableCode.java:165)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1275)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:623)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:168)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.access$000(ActionAdapter.java:40)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter$ActionRunner.run(ActionAdapter.java:280)
	at java.awt.event.InvocationEvent.dispatch(Unknown Source)
	at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.awt.EventQueue.access$500(Unknown Source)
	at java.awt.EventQueue$3.run(Unknown Source)
	at java.awt.EventQueue$3.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.Exception: Error executing system.db.execSProcCall()
	... 40 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: ORA-01400: cannot insert NULL into ("IGNITION"."NOTIFICATIONS"."ID")
ORA-06512: at "IGNITION.ADD_NOTIFICATION", line 41
ORA-06512: at line 1

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:327)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:301)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:258)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:884)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._call(ClientDBUtilities.java:203)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:490)
	... 38 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("IGNITION"."NOTIFICATIONS"."ID")
ORA-06512: at "IGNITION.ADD_NOTIFICATION", line 41
ORA-06512: at line 1

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)
	at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.execute(SRConnectionWrapper.java:853)
	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:230)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.CallSProc.call(CallSProc.java:42)
	at sun.reflect.GeneratedMethodAccessor95.invoke(null)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:483)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:209)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:816)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1156)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1088)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:119)
	at org.eclipse.jetty.server.Server.handle(Server.java:517)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:306)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:242)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:245)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:75)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:213)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:147)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
	at java.lang.Thread.run(Thread.java:745)

Ignition v7.8.1 (b2016012216)
Java: Oracle Corporation 1.8.0_131

pturmel · February 14, 2020, 2:34pm

Seems pretty obvious–a primary key column cannot be null. You aren’t showing your code that calls this stored procedure, but I recommend adding some logging of the values you are sending.

kfells · February 14, 2020, 2:35pm

The primary key is the identity column “ID”. For some reason its not auto-incrementing as it did before.

kfells · February 14, 2020, 2:43pm

@pturmel
Please see code below.

create or replace PROCEDURE ADD_NOTIFICATION
(
P_REQUEST_EMP_ID IN NOTIFICATIONS.REQUEST_EMP_ID%TYPE,
P_LINE_ID IN NOTIFICATIONS.LINE_ID%TYPE,
P_CATEGORY_ID IN NOTIFICATIONS.CATEGORY_ID%TYPE,
P_SEVERITY_ID IN NOTIFICATIONS.SEVERITY_ID%TYPE,
P_LINE_COMPONENT_ID IN NOTIFICATIONS.LINE_COMPONENT_ID%TYPE,
P_REQUEST_NOTE IN NOTIFICATIONS.REQUEST_NOTE%TYPE,
P_CALL_SUPERVISOR IN NUMBER,
P_CALL_MECHANICAL IN NUMBER,
P_CALL_ELECTRICAL IN NUMBER,
P_CALL_QUALITY IN NUMBER,
P_ID OUT NOTIFICATIONS.ID%TYPE
)
IS
SUPERVISOR_TIME TIMESTAMP;
MECHANICAL_TIME TIMESTAMP;
ELECTRICAL_TIME TIMESTAMP;
QUALITY_TIME TIMESTAMP;
BEGIN
IF P_CALL_SUPERVISOR = 1 THEN
SUPERVISOR_TIME := CURRENT_TIMESTAMP;
ELSE
SUPERVISOR_TIME := Null;
END IF;
IF P_CALL_MECHANICAL = 1 THEN
MECHANICAL_TIME := CURRENT_TIMESTAMP;
ELSE
MECHANICAL_TIME := Null;
END IF;
IF P_CALL_ELECTRICAL = 1 THEN
ELECTRICAL_TIME := CURRENT_TIMESTAMP;
ELSE
ELECTRICAL_TIME := Null;
END IF;
IF P_CALL_QUALITY = 1 THEN
QUALITY_TIME := CURRENT_TIMESTAMP;
ELSE
QUALITY_TIME := Null;
END IF;
INSERT INTO NOTIFICATIONS (REQUEST_EMP_ID, LINE_ID, CATEGORY_ID, SEVERITY_ID, LINE_COMPONENT_ID, REQUEST_NOTE, CALL_TIME_SUPERVISOR, CALL_TIME_MECHANICAL, CALL_TIME_ELECTRICAL, CALL_TIME_QUALITY)
VALUES (P_REQUEST_EMP_ID, P_LINE_ID, P_CATEGORY_ID, P_SEVERITY_ID, P_LINE_COMPONENT_ID, P_REQUEST_NOTE, SUPERVISOR_TIME, MECHANICAL_TIME, ELECTRICAL_TIME, QUALITY_TIME)
RETURNING ID
INTO P_ID;
COMMIT;
END ADD_NOTIFICATION;

pturmel · February 14, 2020, 3:28pm

You're going to have to fix this in the DB. It isn't an Ignition problem. Your SP code doesn't accept or attempt to insert that ID column, so there's nothing Ignition can do to address this problem.

kfells · February 14, 2020, 4:10pm

Thanks for your help @pturmel