9 Replies Latest reply on Jul 2, 2015 5:10 AM by Mark Fraser

    ORA-01843: Month not valid...

    Mark Fraser

      Hi all

       

      I'm receiving a ORA-01843 error and I don't understand why...

      • When I manually refresh against the Oracle data source using Desktop I get no errors
      • When I refresh the exact same workbook/ same data source but from Server I get a ORA-01843 error.

       

      This only occurred after 9.0.2 upgrade.

      I have read loads of articles advising using date functions etc.

       

      What I'd really like to know is - why the refresh works on Desktop but not on Server... Help!!

       

      Cheers

      Mark

        • 1. Re: ORA-01843: Month not valid...
          Jeff Strauss

          just a guess.  do you somehow have different database drivers on server than on your desktop?

          1 of 1 people found this helpful
          • 2. Re: ORA-01843: Month not valid...
            Mark Fraser

            Thanks Jeffrey, very good idea, they are on separate machines...

             

            As Desktop is working, should I upgrade/ downgrade the Server Oracle driver to match Desktop's version?

            • 3. Re: ORA-01843: Month not valid...
              Jeff Strauss

              it's worth a quick test especially if the server you're working with is a non-prod. 

               

              The other idea is to try running the tableau desktop physically on the server as it should pickup the same drivers as what server is trying to do (as long as you're running a single instance and not distributed cluster).

              • 4. Re: ORA-01843: Month not valid...
                Eric McDonald

                It may be the regional / date format setup on client and sever machines differ. Databases and particularly Oracle can be quite fussy about dates and will often try and do automatic casts. Make sure you are definitely working with date types (are you using datetime?) and that the formats are correct. For example it is possible to say in oracle:

                 

                where date_type_column = '04/05/2015'

                 

                Oracle will automatically cast the string to a date dependent on the regional Windows setting on the machine, the Windows date/time format, the Oracle client registry setting for NLS_DATE_FORMAT and the NLS regional setting. So it could be 4th May 2015 in the UK and 5th April 2015 in the US.

                 

                if you ever do any Oracle casts make sure you use the full cast syntax:

                 

                where date_type_column = TO_DATE('04/05/2015','DD/MM/YYYY')

                 

                The only format which may work reliably when autocast is YYYMMDD as this is ISO standard but best not to assume.

                 

                Your error shows a date error, it's almost certainly an Oracle problem with dates. Tableau already has it's own Oracle driver best not to install any other Oracle drivers (set a TNS_ADMIN environment variable to define Oracle TNS).

                1 of 1 people found this helpful
                • 5. Re: ORA-01843: Month not valid...
                  Toby Erkson

                  Can you access the Tableau Server (Remote Desktop on to it)?  If so, run the report on the Server...well, assuming you have Desktop installed on it or you can install it.  I always keep the current business version of Desktop on our Servers just in case I need to trouble-shoot possible differences between Desktop & Server.

                  1 of 1 people found this helpful
                  • 6. Re: ORA-01843: Month not valid...
                    Mark Fraser

                    Thank you all

                    Server runs on a VM so I'm just installing a version of Desktop on there (really good suggestion!!!) and will see if that helps with regard to driver differences.

                     

                    @Eric - I have done some manipulation with dates in Oracle (I already have 20+ extracts with multiple dates all working) but you have given me some additional direction/ hints I haven't yet tried, thank you!

                     

                    Will post back with updates

                     

                    Thanks again all

                     

                    Cheers

                    Mark

                    • 7. Re: ORA-01843: Month not valid...
                      Jeff Strauss

                      Mark.  were you able to fix it?

                      • 8. Re: ORA-01843: Month not valid...
                        Mark Fraser

                        Hi Jeffrey

                         

                        Short answer - it's still in progress...

                         

                        Long answer -

                        I have installed Desktop on the VM, and confirmed the extract can't update on either Desktop or Server on the VM but it can on my PC.

                        I then installed the latest Oracle drivers on the VM but it still doesn't work. On my PC I have an older version of the v9 Oracle driver, so the drivers aren't matching. I have asked customer support if they can send/ tell me where to find the older v9 driver so I can create an exact matching setup across both (the PC and VM) in the hope that fixes it! Until that point, I'm not sure what else to try...

                        9.0.3 came out yesterday, and I will upgrade Server (Desktop on my PC was upgraded and the extract still works) but I have an important client demo tonight and can't break anything today!!!

                        Unfortunately it will have to wait until next week now...

                         

                        Thanks again for all your help & suggestions


                        Cheers

                        Mark

                        • 9. Re: ORA-01843: Month not valid...
                          Mark Fraser

                          Hi Eric

                           

                          Thank you, I have some progress because of your suggestion

                          It may be the regional / date format setup on client and sever machines differ. Databases and particularly Oracle can be quite fussy about dates and will often try and do automatic casts.

                          The Windows regional setting where different between my PC and the VM, I have now aligned them.

                          I can now refresh the extract in Tableau Desktop on both my PC and the VM, I couldn't refresh on the VM until the regional settings matched. What I still can't do is refresh the extract via Server on the VM.

                           

                          I'm currently going through the rest of Eric's and your other suggestions, and also some suggestions from technical support, will feedback as I progress.

                           

                          I'm writing this up as fully as I can in the hope some of the content is useful for others with the same problem in the future...

                           

                          Thanks again

                           

                          Cheers

                          Mark