5 Replies Latest reply on Aug 8, 2018 8:44 AM by HarishG_TM

    SQL Functions to_date() or SYSTIMESTAMP not working using DB Adapter in SOA




      I am trying to update the date column in my current project using DB Adapter in SOA 12c. Using 'Execute Pure SQL' option am trying to update the Date column using below example statement:




      update poc.employee set DATE_OF_JOIN=to_date(#param_DOJ,'YYYY-MM-DD') where EMP_ID=#param_EMP_ID


      for 'param_DOJ' element am passing below XPath in XSLT:


      xp20:format-dateTime (adjust-dateTime-to-timezone(current-dateTime(), dayTimeDuration('PT0H')), '[Y0001]-[M01]-[D01]') - Target DB date column expecting UTC date Format.


      And also target DB end for the date column restricted with below date format-string:


      'dd-MMM-yy' or 'dd-MMM-yyyy'


      While trying to insert the date with above format then getting error like 'The input value must be in the iso 8601 date format YYYY-MM-DD’ in SOA server. As suggested by target DB team, using to_date() function we can insert the date into Date column field. Due to that I tried it in the DB Adapter with 'Execute Pure SQL' (mentioned above statement) and tried to test it in SOA server getting below error:





      <bindingFault xmlns="http://schemas.oracle.com/bpel/extension">

      <part name="summary">


      Exception occurred when binding was invoked.

      Exception occurred during invocation of JCA binding: "JCA Binding execute of Reference operation 'Reference_JCA_Update_ConfirmWIP' failed due to: Pure SQL Exception.

      Pure SQL Execute of update poc.employee set DATE_OF_JOIN=to_date(?,'YYYY-MM-DD') where EMP_ID=? failed.

      Caused by java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected


      The Pure SQL option is for border use cases only and provides simple yet minimal functionality.  Possibly try the "Perform an operation on a table" option instead.  This exception is considered not retriable, likely due to a modelling mistake.  To classify it as retriable instead add property nonRetriableErrorCodes with value "-1858" to your deployment descriptor (i.e. weblogic-ra.xml).  To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff.  All properties are integers.


      The invoked JCA adapter raised a resource exception.

      Please examine the above error message carefully to determine a resolution.



      <part name="code">



      <part name="detail">


      ORA-01858: a non-numeric character was found where a numeric was expected








      Kindly please help over here with solution how to pass date with UTC format string using DB Adapter.