How can I pass null values to a stored procedure

, ,
1

Hello,

My stored proc signature specifies several interger values, which allow null values.

When using pb.db.call to execute my stored proc, when I pass in a pipeline variable into one of these parameters (the pipeline variable has an ‘empty’ value), I get the following exception:

String index out of range: 0
java.lang.StringIndexOutOfBoundsException
java.lang.StringIndexOutOfBoundsException: String index out of range: 0
at java.lang.IndexOutOfBoundsException.(IndexOutOfBoundsException.java:46)
at java.lang.StringIndexOutOfBoundsException.(StringIndexOutOfBoundsException.java:59)
at java.lang.String.charAt(String.java(Compiled Code))
at java.math.BigDecimal.(BigDecimal.java:128)
at com.wm.app.b2b.server.JDBCHelpers.setParameter(JDBCHelpers.java:347)
at com.wm.app.b2b.server.JDBCConnection.call(JDBCConnection.java(Compiled Code))
at pub.db.call(db.java:106)
at java.lang.reflect.Method.invoke(Native Method)
at com.wm.app.b2b.server.JavaService.baseInvoke(JavaService.java(Compiled Code))
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java(Compiled Code))
at com.wm.app.b2b.server.BaseService.invoke(BaseService.java:140)
at com.wm.lang.flow.FlowInvoke.invoke(FlowInvoke.java(Compiled Code))
at com.wm.lang.flow.FlowState.invokeNode(FlowState.java(Compiled Code))
at com.wm.lang.flow.FlowState.invoke(FlowState.java(Compiled Code))
at com.wm.lang.flow.FlowState.invoke(FlowState.java(Compiled Code))
at wm.server.flow.stepFlow(flow.java:183)
at java.lang.reflect.Method.invoke(Native Method)
at com.wm.app.b2b.server.JavaService.baseInvoke(JavaService.java(Compiled Code))
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java(Compiled Code))
at com.wm.app.b2b.server.comm.DefaultServerRequestHandler.handleMessage(DefaultServerRequestHandler.java(Compiled Code))
at com.wm.app.b2b.server.HTTPMessageHandler.process(HTTPMessageHandler.java(Compiled Code))
at com.wm.app.b2b.server.HTTPDispatch.run(HTTPDispatch.java(Compiled Code))
at com.wm.util.pool.PooledThread.run(PooledThread.java(Compiled Code))
at java.lang.Thread.run(Thread.java:498)

Thank you,
Chris

2

Hi Chris,
This problem occurs generally when you integrate HTML pages with webMethods services. This happens becoz the null values sent by webMethods is treated as varchar by database and tries to convert to number.
To overcome this error you have to change the variable types of input variables of stored procedures to VARCHAR2 in oracle. This wont effect your functionality as oracle converts the varchar2 to number automatically.
I have done this and it worked properly.

john