4 Replies Latest reply: Jan 30, 2013 5:23 PM by 985940 RSS

    ORA-01843: not a valid month

    985940
      I have a strange problem. I've created a stored procedure that executes a INSERT INTO SELECT statement.

      I can get it to work well using specific dates, but when I try and use a variable I get the above error.. Here's the code:
      do_updates(:P6_DATE);  -- variable populated from select list, list contains values in format JAN-13, FEB-13 etc
      
      create or replace procedure "do_updates" (v_month IN varchar2) IS
          sql_stmt varchar2(4000);
      begin
      sql_stmt := q'{INSERT INTO mydata (date_rec, user_id) select date_rec, user_id
          FROM sourcetable
          WHERE date_rec >= to_date('||v_month||', 'MON-YY') AND date_rec < add_months(to_date('||v_month||', 'MON-YY'),1) }';
      EXECUTE IMMEDIATE sql_stmt;
      COMMIT;
      END;
      but it works if I put the dates in manually, like this:
      Any ideas?
      WHERE date_rec >= to_date('JAN-13', 'MON-YY') AND date_rec < add_months(to_date('JAN-13', 'MON-YY'),1) }';
        • 1. Re: ORA-01843: not a valid month
          sb92075
          pearlyred wrote:
          I have a strange problem. I've created a stored procedure that executes a INSERT INTO SELECT statement.

          I can get it to work well using specific dates, but when I try and use a variable I get the above error.. Here's the code:
          do_updates(:P6_DATE);  -- variable populated from select list, list contains values in format JAN-13, FEB-13 etc
          
          create or replace procedure "do_updates" (v_month IN varchar2) IS
          sql_stmt varchar2(4000);
          begin
          sql_stmt := q'{INSERT INTO mydata (date_rec, user_id) select date_rec, user_id
          FROM sourcetable
          WHERE date_rec >= to_date('||v_month||', 'MON-YY') AND date_rec < add_months(to_date('||v_month||', 'MON-YY'),1) }';
          EXECUTE IMMEDIATE sql_stmt;
          COMMIT;
          END;
          but it works if I put the dates in manually, like this:
          Any ideas?
          WHERE date_rec >= to_date('JAN-13', 'MON-YY') AND date_rec < add_months(to_date('JAN-13', 'MON-YY'),1) }';
          try below instead

          WHERE date_rec >= to_date(v_month, 'MON-YY')
          AND date_rec < add_months(to_date(v_month, 'MON-YY'),1) }';
          • 2. Re: ORA-01843: not a valid month
            985940
            Hiya. I did already try that, but got "ORA-00904: "V_MONTH": invalid identifier" ?
            • 3. Re: ORA-01843: not a valid month
              Frank Kulash
              Hi,

              What is the value of :p6_date that causes the error?
              Chances are, you're missing quotes in the dynamic SQL statement.

              Can you reproduce the error in SQL*Plus, using a bind variable?

              There's no reason to use dynamic SQL here. You can use PL/SQL variables anyplace where you can use an expression, so you can simply say:
              CREATE OR REPLACE PROCEDURE do_updates (v_month IN varchar2) 
              IS
                  month_start       DATE  := TO_DATE (v_month, 'MON-YY');
              BEGIN
                  INSERT INTO mydata (date_rec, user_id) 
                  SELECT              date_rec, user_id
                  FROM      sourcetable
                  WHERE     date_rec >=             month_start
                  AND       date_rec <  ADD_MONTHS (month_start, 1);
                  COMMIT;
              END  do_updates;
              You could also use v_month directly in the INSERT statement, but then you'd have to do the exact same TO_DATE call 2 times.
              • 4. Re: ORA-01843: not a valid month
                985940
                Worked perfectly, thank you Frank.