4 Replies Latest reply: Jul 7, 2009 6:02 AM by Solomon Yakobson RSS

    Error -1861: ORA-01861: literal does not match format string

    710551
      hi

      i am getting the ORA-01861: literal does not match format string. However my code is mentioned below

      runtime_call:='insert into mss_examination (exam_id,examination_name,registration_no,examination_session,roll_no,stream_id,remarks,board,vouchar,examinationyear)values (:exam_id,:examination_name,:registration_no,:examination_session,:roll_no,:stream_id,:remarks,:board,:vouchar,:examinationyear)';

      DBMS_SQL.PARSE(l_cursor, runtime_call, DBMS_SQL.NATIVE);

      DBMS_SQL.BIND_VARIABLE(l_cursor, ':exam_id', xmlstr(1));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':examination_name',xmlstr(2));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':registration_no', xmlstr(3));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':examination_session',xmlstr(4));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':roll_no', xmlstr(5));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':stream_id',xmlstr(6));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':remarks',xmlstr(7));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':board',xmlstr(8));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':vouchar',xmlstr(9));
      DBMS_SQL.BIND_VARIABLE(l_cursor, ':examinationyear', xmlstr(10));

      l_data:=DBMS_SQL.execute(l_cursor);

      Please help.
        • 1. Re: Error -1861: ORA-01861: literal does not match format string
          Toon Koppelaars
          Can you give a DESCRIBE of the table involved?
          Is the error occuring for one of the particular lines of code that you are showing us?
          Wat is the declaration of the xmlstr variable?

          Edited by: Toon Koppelaars on Jul 7, 2009 12:54 PM
          • 2. Re: Error -1861: ORA-01861: literal does not match format string
            Toon Koppelaars
            And: why are you using dbms_sql, you could do this stuff with NDS (Native Dynamic SQL) sort of like this:
            begin
              execute immediate 'insert into table(col1, col2, ...) values(:b1,:b2, ...)'
              using xmlstr(1), xmlstr(2), ....;
            end;
            Or even more simple:
            begin
              insert into table(col1,col2,...) values(xmlstr(1),xmlstr(2),...);
            end;
            • 3. Re: Error -1861: ORA-01861: literal does not match format string
              _Karthick_
              Do you have a date column? If so what is the value you are inserting. If the format of the date is not inline with the format defined in NLS_DATE_FORMAT then you can get this error
              SQL> create table t(dt date)
                2  /
              
              Table created.
              
              SQL> insert into t (dt) values('19111980')
                2  /
              insert into t (dt) values('19111980')
                                        *
              ERROR at line 1:
              ORA-01861: literal does not match format string
              
              
              SQL> alter session set nls_date_format= 'ddmmyyyy'
                2  /
              
              Session altered.
              
              SQL> insert into t (dt) values('19111980')
                2  /
              
              1 row created.
              • 4. Re: Error -1861: ORA-01861: literal does not match format string
                Solomon Yakobson
                You did not provide table mss_examination column datatypes. So let me guess, column examinationyear datatype is DATE. And xmlstr(10) is something like '2009'. Right? If so, then 2009 is implicitly converted to date using default date format which is most likely DD-MON-YY. Obviously they do not match:
                SQL> declare
                  2      i number;
                  3      c number;
                  4  begin
                  5      c := dbms_sql.open_cursor;
                  6      dbms_sql.parse(c,'insert into tmp values(:1)',dbms_sql.native);
                  7      dbms_sql.bind_variable(c,':1','2009');
                  8      i := dbms_sql.execute(c);
                  9      dbms_sql.close_cursor(c);
                 10  end;
                 11  /
                declare
                *
                ERROR at line 1:
                ORA-01861: literal does not match format string
                ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204
                ORA-06512: at "SYS.DBMS_SQL", line 323
                ORA-06512: at line 8
                
                
                SQL> 
                Either change examinationyear datatype to number or use TO_DATE:
                SQL> ed
                Wrote file afiedt.buf
                
                  1  declare
                  2      i number;
                  3      c number;
                  4  begin
                  5      c := dbms_sql.open_cursor;
                  6      dbms_sql.parse(c,'insert into tmp values(to_date(:1,''YYYY''))',dbms_sql.native);
                  7      dbms_sql.bind_variable(c,':1','2009');
                  8      i := dbms_sql.execute(c);
                  9      dbms_sql.close_cursor(c);
                 10* end;
                SQL> /
                
                PL/SQL procedure successfully completed.
                SY.