BizTalk WCF-ORACLEDB error: PL/SQL: ORA-00917: missing comma

by Sandro Pereira | Aug 20, 2018 | BizTalk Community Blogs via Syndication

In the last months I have been working with ORACLE adapter, mainly doing direct insert operations on ORACLE tables and as you might imagine, I found some errors that I find interesting to document. One of these errors was PL/SQL: ORA-00917: missing comma.

The first time I try to directly insert data inside a table – without using any stored procedure, that I normally use in SQL Server or other implementations that I worked with ORACLE – I got the following error:

A message sent to adapter “WCF-Custom” on send port “SEND-PORT-NAME” with URI “oracledb://IP-ADDRESS:PORT-NUMBER/PATH” is suspended.

Error details: Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 2, column 677:

PL/SQL: ORA-00917: missing comma

ORA-06550: line 2, column 1:

PL/SQL: SQL Statement ignored —> Oracle.DataAccess.Client.OracleException: ORA-06550: line 2, column 677:

PL/SQL: ORA-00917: missing comma

ORA-06550: line 2, column 1:

PL/SQL: SQL Statement ignored

at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)

at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)

at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()

at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)

— End of inner exception stack trace —

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

MessageId: {29C0CAD2-1D48-4318-8C86-E4A4E38FBD1C}

InstanceID: {F64C65F2-99F2-410E-A92E-418D146C16C9}

Cause

When you import the Insert (or other operation) schema from a specific table, unlike SQL, that only brings fields for you to fill, ORACLE schema will have:

• Elements (fields) – that are the columns present in that specific table
• and each Element will have an optional “InlineValue” attribute.

The element, as you can imagine is to send the value data that you want to insert in that specify column of the database but what is the InlineValue attribute? And what is this for?

InlineValue

For all simple data records in a multiple record Insert operation, you can choose to override the value of a record by specifying a value for an optional attribute called “InlineValue“. The InlineValue attribute can be used to insert computed values into tables or views such as populating the primary key column using a sequence or inserting system date (using SYSDATE) into a date column. Again, this is an optional attribute and is available for all simple data records in a multiple record Insert operation.

Basically, in other words, it allows you to call ORACLE PL/SQL functions like SYSDATE, TO_DATE or others for that specific column. And you don’t need to insert any data on the element, again, by specifying the InlineValue attribute this will override the value that you insert on that element.

Why you are getting the PL/SQL: ORA-00917: missing comma error?

This error typically occurs when you are mistakenly putting the data to be inserted in the “InlineValue” attribute and not in the elements:

<ns0:PAYMENTSRECORDINSERT xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/STGADMIN/Table/RETAIL_PAYMENTS">
  <ns0:RECORD_UNIQUE_ID InlineValue="12345" />
  <ns0:BOOKING_REF_NUMBER InlineValue="12345" />
  <ns0:SOURCE_SYSTEM InlineValue="TEST" />
  <ns0:PAYMENT_METHOD InlineValue="MONEY" />
  <ns0:CURRENCY_CODE InlineValue="EUR" />
  <ns0:REFERENCE_CODE InlineValue="1234" />
  <ns0:PAYMENT_TRANSACTION_ID InlineValue="1234" />
  <ns0:INTERFACE_STATUS InlineValue="N" />
</ns0:ADMM_RETAIL_PAYMENTSRECORDINSERT>

Solution

The solution, in this case, is very simple: you need to place the data on the existing elements of the schema instead of using the InlineValue attribute of the element:

<ns0:PAYMENTSRECORDINSERT xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/STGADMIN/Table/RETAIL_PAYMENTS">
  <ns0:RECORD_UNIQUE_ID>1234</ns0:RECORD_UNIQUE_ID>
  <ns0:BOOKING_REF_NUMBER>1234</ns0:BOOKING_REF_NUMBER>
  <ns0:SOURCE_SYSTEM>TEST</ns0:SOURCE_SYSTEM>
  <ns0:PAYMENT_METHOD>MONEY</ns0:PAYMENT_METHOD>
  <ns0:CURRENCY_CODE>EUR</ns0:CURRENCY_CODE>
  <ns0:REFERENCE_CODE>1234</ns0:REFERENCE_CODE>
  <ns0:PAYMENT_TRANSACTION_ID>1234</ns0:PAYMENT_TRANSACTION_ID>
  <ns0:INTERFACE_STATUS>N</ns0:INTERFACE_STATUS>
</ns0:ADMM_RETAIL_PAYMENTSRECORDINSERT>

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira