8 Replies Latest reply: Apr 2, 2014 1:56 AM by jprosperi RSS

    Problem when i use a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client

    jprosperi

      When I use a parameter of type OracleDbType.Date and its value is a string with a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client gives me an error, but if it worked Oracle.DataAccess.Client.
      Now only works if the date format is "MM / dd / yyyy".
      How I can continue using the format "dd / MM / yyyy" with Oracle.ManagedDataAccess.Client?

      thanks

        • 1. Re: Problem when i use a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client
          BluShadow

          I don't know ODP.NET, but I don't think you should be supplying strings to a date datatype, it should be supplied as a date or explicitly converted to a date before assigning it.

          If you don't, then the string will be implicitly converted based on various session settings, which could vary from client to client etc.

          • 2. Re: Problem when i use a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client
            Alex.Keh .Product.Manager-Oracle

            If the error you receive is that GetClientInfo, GetThreadInfo, or SetThreadInfo is not supported, these methods have not yet been ported to managed ODP.NET. An alternative way to customize the date format with managed ODP.NET is below.

            =================

                       OracleConnection con = new OracleConnection("User Id=scott;Password=tiger;Data Source=orcl;");

                        con.Open();

                        OracleGlobalization sessionGlobalization = con.GetSessionInfo();

                        sessionGlobalization.DateFormat = "DD / MM / YYYY";

                        con.SetSessionInfo(sessionGlobalization);

                        OracleCommand cmd = con.CreateCommand();

                        cmd.CommandText = "select TO_CHAR(hiredate) from emp";

                        Console.WriteLine("Hire Date ({0}): {1}", sessionGlobalization.DateFormat, cmd.ExecuteScalar());

            • 3. Re: Problem when i use a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client
              jprosperi

              Hi, thank for your answer. I tried your solution and the exception still the same. You can see below the code example and the exception. Exception Message: La cadena de entrada no tiene el formato correcto.

               

                  Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click

                      Dim parametros(0) As OracleParameter
                      Dim fechaEjemplo As Date
                      Dim glob As Oracle.ManagedDataAccess.Client.OracleGlobalization

                      Try

                          fechaEjemplo = New Date(2014, 3, 28) 'Sample, day > 12

                          Using cnn As New Oracle.ManagedDataAccess.Client.OracleConnection

                              cnn.ConnectionString = "POOLING=False;USER ID=xxx;PASSWORD=xxx;DATA SOURCE=xxx"
                              cnn.Open()

                              glob = cnn.GetSessionInfo()
                              glob.DateFormat = "DD/MM/YYYY"
                              cnn.SetSessionInfo(glob)

                              parametros(0) = New OracleParameter
                              parametros(0).Direction = ParameterDirection.Input
                              parametros(0).OracleDbType = OracleDbType.Date
                              parametros(0).Value = fechaEjemplo.ToString("DD/MM/YYYY")
                              parametros(0).ParameterName = "valor"

                              Using cmd As Oracle.ManagedDataAccess.Client.OracleCommand = New Oracle.ManagedDataAccess.Client.OracleCommand

                                  cmd.CommandText = "testodp12"
                                  cmd.Parameters.Add(parametros(0))
                                  cmd.CommandType = CommandType.StoredProcedure
                                  cmd.Connection = cnn
                                  cmd.ExecuteNonQuery()

                              End Using

                              cnn.Close()

                          End Using

                      Catch ex As Exception
                          MsgBox(ex.ToString, MsgBoxStyle.Critical, "ERROR")
                      End Try
                  End Sub


               

              Exception StackTrace

               

                 en System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

                 en System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)

                 en Oracle.ManagedDataAccess.Types.DateTimeStringUtilities.FromString(String stringRep, Int32& year, Int32& month, Int32& day, Int32& hours, Int32& minutes, Int32& seconds, Int32& nanos, String& region, Boolean expectNoRegion, Boolean expectNoNanos)

                 en Oracle.ManagedDataAccess.Types.OracleDate.FromString(String datStr)

                 en Oracle.ManagedDataAccess.Types.OracleDate..ctor(String datStr)

                 en OracleInternal.ServiceObjects.OracleParameterImpl.SetDateInBytes(Object paramValue, Byte[]& dateByteArray)

                 en OracleInternal.ServiceObjects.OracleParameterImpl.SetDateInBytes(Object paramValue)

                 en Oracle.ManagedDataAccess.Client.OracleParameter.PreBind_Date()

                 en Oracle.ManagedDataAccess.Client.OracleParameter.PreBind(OracleConnectionImpl connImpl, ColumnDescribeInfo cachedParamMetadata, Boolean& bMetadataModified, Int32 arrayBindCount, ColumnDescribeInfo& paramMetaData, Object& paramValue, Boolean isEFSelectStatement)

                 en OracleInternal.ServiceObjects.OracleCommandImpl.InitializeParamInfo(ICollection paramColl, OracleConnectionImpl connectionImpl, ColumnDescribeInfo[] cachedParamMetadata, Boolean& bMetadataModified, Boolean isEFSelectStatement, MarshalBindParameterValueHelper& marshalBindValuesHelper)

                 en OracleInternal.ServiceObjects.OracleCommandImpl.ProcessParameters(OracleParameterCollection paramColl, OracleConnectionImpl connectionImpl, ColumnDescribeInfo[] cachedParamMetadata, Boolean& bBindMetadataModified, Boolean isEFSelectStatement, MarshalBindParameterValueHelper& marshalBindValuesHelper, Boolean& bAllInputBinds)

                 en OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int32 lobPrefetchSize, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Boolean isFromEF)

                 en Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()

                 en TestLogParametros.Form1.Button4_Click(Object sender, EventArgs e) en C:\Users\jprosperi\Dropbox\EdT\Pruebas\TestLogParametros\Form1.vb:línea 312

               

               

               

              Regards

              • 4. Re: Problem when i use a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client
                Alex.Keh .Product.Manager-Oracle

                Did you use the TO_CHAR function call in your stored procedure? That may be the difference between my code and yours.

                • 5. Re: Problem when i use a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client
                  jprosperi

                  my store procedure has a date type parameter and not use the to_char function, only i use the parameter to do a query. The query compare parameter value with a date field of a table to return a cursor

                   

                  regards

                  • 6. Re: Problem when i use a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client
                    jprosperi

                    My procedure

                     

                    PROCEDURE "LISTAIMPUESTOS" (

                      "FECHA" IN DATE,

                      "DATOS" OUT SYS_REFCURSOR)

                     

                      IS

                     

                      BEGIN

                     

                          OPEN DATOS FOR

                        SELECT TIMCOD, TIMDES, PRCVAL, PRCVIG

                          FROM TBLCLIMP A, TBLPORVIG B

                         WHERE TIMCOD = PRCCOD

                            AND PRCVIG = (SELECT MAX(PRCVIG)

                                            FROM TBLPORVIG

                                           WHERE A.TIMCOD = PRCCOD

                                                AND PRCVIG <= FECHA);

                      

                      END;

                    • 7. Re: Problem when i use a date in the format "dd / MM / yyyy" using Oracle.ManagedDataAccess.Client
                      Alex.Keh .Product.Manager-Oracle

                      I would recommend you use the TO_CHAR function. At this point, the SetThreadInfo API isn't supported by managed ODP.NET.