3 Replies Latest reply on May 27, 2010 2:45 PM by gdarling - oracle

    ASP.NET, IIS CALLING ORACLE PROCEDURE THROUGH OLEDB , DATE ERROR

    737852
      We are having one oracle procedure is there when it is executed in toad or sql plus
      its executing without errors But when executed the same procedure in asp.net
      it is "executing fine, WITHOUT THROWING ERRORS".But internally some date format error is coming
      ,wE debugged and found the issue


      SELECT PTD_RATE ,PTD_MAX_PREM_LC_1, PTD_MIN_PREM_LC_1, PTD_MAX_SI_LC_1, PTD_MIN_SI_LC_1, PTD_RATE_EFT, PTD_DFLT_SI_FC, PTD_SI_CURR_CODE, NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL FROM PGIM_PROD_TARIFF_DATA WHERE PTD_PCVR_SYS_ID = 21779 AND PTD_NUMERIC_VAL6 <= '48' AND PTD_NUMERIC_VAL7 >= '48' AND TO_DATE('25-MAY-09','DD-MON-YY') BETWEEN PTD_EFF_FM_DT AND NVL(PTD_EFF_TO_DT,TO_DATE(TO_DATE('25-MAY-09') +1,'DD-MON-YY'))
      this is the query generated DYANAMICALLY in toad while calling that procedure same procedure is creating the following

      SELECT PTD_RATE ,PTD_MAX_PREM_LC_1, PTD_MIN_PREM_LC_1, PTD_MAX_SI_LC_1, PTD_MIN_SI_LC_1, PTD_RATE_EFT, PTD_DFLT_SI_FC, PTD_SI_CURR_CODE, NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL FROM PGIM_PROD_TARIFF_DATA WHERE PTD_PCVR_SYS_ID = 21779 AND PTD_NUMERIC_VAL6 <= '48' AND PTD_NUMERIC_VAL7 >= '48' AND TO_DATE('2009-05-25 00:00:00','DD-MON-YY') BETWEEN PTD_EFF_FM_DT AND NVL(PTD_EFF_TO_DT,TO_DATE(TO_DATE('2009-05-25 00:00:00') +1,'DD-MON-YY'))
      Here i am not passing any date to that procedure ,simply i am passing a sys id and the procedure is not returning any value

      i am attaching the codes here
      //CALLING///
      bool isLoaded=true;
      isLoaded = objDB.fnCallCoverProcedure("Pkg_online_Ren_Process.RENEWAL_PROCESS_MAIN", strSID);
      where
      the function defenition is
      //DEFENITION //
      public bool fnCallCoverProcedure(string paramProcedure, string param1)
      {         
      OleDbCommand cmd = new OleDbCommand();
      try
      {

      dbCon.Open();

      cmd = new OleDbCommand(paramProcedure, dbCon);
      cmd.Connection = dbCon;
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
      cmd.Parameters.Add("POL_SYS_ID", OleDbType.Integer, 15).Value = param1;
      cmd.ExecuteNonQuery();
      return true;

      }
      catch (Exception e1)
      {
      return false;
      }
      finally
      {

      cmd.Dispose();
      dbCon.Close();
      }


      }

      --

      NOTE THAT THE QUERIES GENERATED IN DIFFERENT ENVIRONMENTS. THE SECOND QUERY IS FROM ASP.NET(ITS THROWING ERROR INTERNALLY,CHECK DATE FORMAT) I KNOW ITS BECAUS OF DATE FORMAT, WE CANT FIX IT.SAME PROCEDURE IS USED BY OTHER APPLICATIONS ALSO.
      I AM REALLY SCREWED.


      Why oracle is taking the client culture?
      or why IIS OR ASP is sending an invalid culture to Oracle. Why this can be client independent?? Please some one provide a solution


      is there any configuration for OLEDB? locally

      my database is in another PC.