2 Replies Latest reply: Jan 11, 2013 7:56 AM by User477708-OC RSS

    ORA-00904: "OCT": invalid identifier

    877826
      Hi All,

      i using execulte immediate to create a table as below

      SQL_STR := 'create table test_archive as select * from test_one where date_key between' TO_DATE(20121001,'YYYYMMDD')||' and '||TO_DATE(20121002,'YYYYMMDD');
      execute immediate SQL_STR;

      i am getting below error.

      Eexeduting the varSqlstmt.
      ERROR CODE: ORA-00904: "OCT": invalid identifier

      and it has generated the Query as below ,

      create table test_archive as select * from test_one where
      date_key between 01-OCT-12 and 03-OCT-12

      my requirement is insted of "OCT" it has to Display as "10".

      Note : date_key column date datatype.

      please any one help me in this .

      Thanks
      Sree
        • 1. Re: ORA-00904: "OCT": invalid identifier
          Fran
          ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD';
          SQL> alter session set NLS_DATE_FORMAT='YYYY-MON-DD';
          
          Sesi¾n modificada.
          
          SQL> select sysdate from dual;
          
          SYSDATE
          -----------
          2013-ENE-11
          
          SQL> alter session set NLS_DATE_FORMAT='YYYYMMDD';
          
          Sesi¾n modificada.
          
          SQL> select sysdate from dual;
          
          SYSDATE
          --------
          20130111
          
          SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD';
          
          Sesi¾n modificada.
          
          SQL> select sysdate from dual;
          
          SYSDATE
          ----------
          2013-01-11
          Edited by: Fran on 11-ene-2013 5:52
          • 2. Re: ORA-00904: "OCT": invalid identifier
            User477708-OC
            youre not quoting your string properly either. use 4 single quotes to wrap around where you need a quote in your string..example....

            SQL_STR := 'create table test_archive as select * from test_one where date_key between TO_DATE( || '''' || '20121001,' || '''' || 'YYYYMMDD|| '''' || ')||

            edit: sorry, you get the gist...4 single quotes in a dynamic string to give you 1 quote. I havent tested the above so its probably incorrect if but use that formula and youll get it