Oracle TimeStamp

Question

I've a table:

    Cust_data 
   (    
    "SEQ_NO" NUMBER, 
    "CUST_ID" VARCHAR2, 
    "SERVER_SID" VARCHAR2, 
    "CUST_NAME" VARCHAR2, 
    "EVENT_TYM" TIMESTAMP, 
    "CUST_DOB" DATE, 
    "ADD_TYMSTMP" TIMESTAMP, 
    "STATUS" VARCHAR2
   ) 

and I'm trying to insert timeStamp of format:

2014-01-25T04:05:26.669Z

I've tried using this update statement:

update CUST_DATA set 
ADD_TYMSTMP = TO_TIMESTAMP('2014-01-25T04:05:26.669Z','yyyy/mm/dd'T'hh24:mi:ss'Z'')
where CUST_ID='chan012'

The above update statement gives an error:

ORA-00907: missing right parenthesis

And this this statement works perfectly good.

update CUST_DATA set 
ADD_TYMSTMP = TO_TIMESTAMP('2014-01-25 04:05:26.669','yyyy-mm-dd hh24:mi:ss.ff')
where CUST_ID='chan012'

But the problem is that I need to have this format into the table.

2014-01-25T04:05:26.669Z

I've a csv file to load it into this table with above mentioned time stamp format, its not getting loaded. So I've tried inserting one record its not working either. Can anybody help, thanks in advance.

Answer 1

You need to use double-quotes around the fixed characters, not single-quotes:

update CUST_DATA set 
ADD_TYMSTMP = TO_TIMESTAMP('2014-01-25T04:05:26.669Z',
  'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"')
where CUST_ID='chan012';

This is shown in the documentation; the fixed value shown in the format model list is "text".

In your version the two single-quotes at the end of your model, ... 'Z'') ..., are being treated as an escaped quote, which means you have an uneven number of quotes - your string is not terminated, and would be wrong even if it were. The ORA-00907 is a fairly generic, not always directly helpful, error. It usually just means you've got something fairly fundamental wrong and Oracle has got to the end of the parsing step without being able to figure out exactly what that is. Sometimes you really have missed a closing parenthesis, of course, but you generally have to look a bit deeper when you see this.

You said you're loading this data; if you're using SQL*Loader for that then you can specify the model in the control file:

load data .... into cust_data ...
(
  ...
  add_tymstmp timestamp 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"',
  ...
)

Tested in 11gR2, and with your fixed value in the file, this gives:

select add_tymstmp,
  to_char(add_tymstmp, 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"') as display
from cust_data;

ADD_TYMSTMP                  DISPLAY                      
---------------------------- ------------------------------
25-JAN-14 04.05.26.669000000 2014-01-25T04:05:26.669Z