This discussion is archived
4 Replies Latest reply: Jan 30, 2013 3:23 PM by 985940 RSS

ORA-01843: not a valid month

985940 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hiya. I did already try that, but got "ORA-00904: "V_MONTH": invalid identifier" ?
  • 3. Re: ORA-01843: not a valid month
    Frank Kulash Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Worked perfectly, thank you Frank.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points