This discussion is archived
2 Replies Latest reply: Jan 11, 2013 5:56 AM by User477708-OC RSS

ORA-00904: "OCT": invalid identifier

877826 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

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