3 Replies Latest reply: May 13, 2014 5:13 AM by fac586 RSS

    ORA-01843: not a valid month

    user3645169

      Hello,

       

      When I execute following statement in APEX (SQL Workshop > SQL Commands):

      SELECT REGION, AUT, SUBDOMAIN, JAN2013, TO_DATE('01/01/2013', 'MM/DD/YYYY') FROM VCOUNT

       

      I get my results (as to be expected)

      REGION  AUT     SUBDOMAIN     JAN2013     TO_DATE('01/01/2013','MM/DD/YYYY')

      APAC     A          Accounting           4              01/01/2013

      APAC     A          CMS                    4              01/01/2013

      APAC     A          CRM                    1              01/01/2013

       

      However if I execute this statement within Oracle SQL/Developer, I get this:

      Error starting at line : 1 in command -

      SELECT REGION, AUT, SUBDOMAIN, JAN2013, TO_DATE('01/01/2013', 'MM/DD/YYYY') FROM VCOUNT

      Error report -

      SQL Error: ORA-01843: not a valid month

      01843. 00000 -  "not a valid month"

      *Cause:   

      *Action:

       

      "Why bother to execute this in SQL/Developer?" you might ask.

      Well this "select" is part of an insert statement into a table that works perfectly when executed using  (SQL Workshop > SQL Commands)

      but fails when executed from within an Apex application

       

      VCOUNT is defined as a view

      CREATE OR REPLACE FORCE VIEW  "VCOUNT" ("REGION", "AUT", "SUBDOMAIN", "TELLER", "JAN2013") AS

      SELECT t.region, t.aut, t.subdomain, t.teller, V20130101.TELLER JAN2013

      FROM VTOTALS T, V20130101

      WHERE

      T.REGION = V20130101.REGION(+) AND

      T.AUT = V20130101.AUT(+) AND

      T.SUBDOMAIN = V20130101.SUBDOMAIN(+);

       

      Both VTOTALS and V20130101 are views themselves.

       

      Any ideas someone?

        • 1. Re: ORA-01843: not a valid month
          fac586

          user3645169 wrote:

           

          Hello,

           

          When I execute following statement in APEX (SQL Workshop > SQL Commands):

          SELECT REGION, AUT, SUBDOMAIN, JAN2013, TO_DATE('01/01/2013', 'MM/DD/YYYY') FROM VCOUNT

           

          I get my results (as to be expected)

          REGION  AUT     SUBDOMAIN     JAN2013     TO_DATE('01/01/2013','MM/DD/YYYY')

          APAC     A          Accounting           4              01/01/2013

          APAC     A          CMS                    4              01/01/2013

          APAC     A          CRM                    1              01/01/2013

           

          However if I execute this statement within Oracle SQL/Developer, I get this:

          Error starting at line : 1 in command -

          SELECT REGION, AUT, SUBDOMAIN, JAN2013, TO_DATE('01/01/2013', 'MM/DD/YYYY') FROM VCOUNT

          Error report -

          SQL Error: ORA-01843: not a valid month

          01843. 00000 -  "not a valid month"

          *Cause:   

          *Action:

           

          "Why bother to execute this in SQL/Developer?" you might ask.

          Well this "select" is part of an insert statement into a table that works perfectly when executed using  (SQL Workshop > SQL Commands)

          but fails when executed from within an Apex application

           

          VCOUNT is defined as a view

          CREATE OR REPLACE FORCE VIEW  "VCOUNT" ("REGION", "AUT", "SUBDOMAIN", "TELLER", "JAN2013") AS

          SELECT t.region, t.aut, t.subdomain, t.teller, V20130101.TELLER JAN2013

          FROM VTOTALS T, V20130101

          WHERE

          T.REGION = V20130101.REGION(+) AND

          T.AUT = V20130101.AUT(+) AND

          T.SUBDOMAIN = V20130101.SUBDOMAIN(+);

           

          Both VTOTALS and V20130101 are views themselves.

           

          Any ideas someone?

          Re: 2. How do I ask a question on the forums?

           

          What are the definitions of the VTOTALS and V20130101 views and their underlying tables? Is any string/date conversion involved?

           

          I suspect that one of the views is performing a string/date conversion and that SQL Developer is processing a different number of rows than the APEX SQL Workshop, resulting in it hitting a row containing bad data that cannot be converted using the format mask involved.

           

          I'm also immediately suspicious of the data model given the names of the V20130101 view and JAN2013 column...

          • 2. Re: ORA-01843: not a valid month
            user3645169

            definition of vtotals

            CREATE OR REPLACE FORCE VIEW  "VTOTALS" ("REGION", "AUT", "SUBDOMAIN", "TELLER") AS

              select s1.region, s1.aut, s1.subdomain, count(*) TELLER

            from solution s1

            group by s1.region, s1.aut, s1.subdomain

            order by 1,2,3;

             

            Definition of v20130101

            CREATE OR REPLACE FORCE VIEW  "V20130101" ("REGION", "AUT", "SUBDOMAIN", "TELLER") AS

              select s1.region, s1.aut, s1.subdomain, count(*) TELLER

            from task t1, solution s1

            where

            t1.task_start < '01/01/2013'

            and t1.task_end > '12/31/2012'

            and t1.solution_id = s1.solution_id and t1.parent_id = 1

            group by s1.region, s1.aut, s1.subdomain

            order by 1,2,3

            This last one is probably causing all the trouble?

            • 3. Re: ORA-01843: not a valid month
              fac586

              user3645169 wrote:

               

              definition of vtotals

              CREATE OR REPLACE FORCE VIEW  "VTOTALS" ("REGION", "AUT", "SUBDOMAIN", "TELLER") AS

                select s1.region, s1.aut, s1.subdomain, count(*) TELLER

              from solution s1

              group by s1.region, s1.aut, s1.subdomain

              order by 1,2,3;

               

              Definition of v20130101

              CREATE OR REPLACE FORCE VIEW  "V20130101" ("REGION", "AUT", "SUBDOMAIN", "TELLER") AS

                select s1.region, s1.aut, s1.subdomain, count(*) TELLER

              from task t1, solution s1

              where

              t1.task_start < '01/01/2013'

              and t1.task_end > '12/31/2012'

              and t1.solution_id = s1.solution_id and t1.parent_id = 1

              group by s1.region, s1.aut, s1.subdomain

              order by 1,2,3

              This last one is probably causing all the trouble?

              Yes. Assuming that the TASK.TASK_START and TASK.TASK_END columns are of type DATE then this should be:

               

              CREATE OR REPLACE FORCE VIEW  "V20130101" ("REGION", "AUT", "SUBDOMAIN", "TELLER") AS
                select s1.region, s1.aut, s1.subdomain, count(*) TELLER
              from task t1, solution s1
              where
              t1.task_start < date '2013-01-01'
              and t1.task_end > date '2012-12-31'
              and t1.solution_id = s1.solution_id and t1.parent_id = 1
              group by s1.region, s1.aut, s1.subdomain
              order by 1,2,3
              
              
              

               

              Always compare dates with dates, strings with strings, numbers with numbers etc, performing explicit conversion where required.

               

              If the TASK.TASK_START and TASK.TASK_END columns are not of type DATE then your data model is wrong (which it probably is anyway given the existence of such a view...)