Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to alter a table to include modified and created columns. However, I am having trouble with adding the modified column. I get the following error:

SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

I was reading this Oracle document (http://docs.oracle.com/cd/E17952_01/refman-5.5-en/timestamp-initialization.html) to get an example on how to properly write the ON UPDATE syntax

Here is my SQL.

ALTER TABLE FOOBAR
ADD (
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Why am I getting a missing right parenthesis error?

share|improve this question
    
NB I wouldn't specify a default for modified so that it remains null when unmodified. And use a for each row trigger to set :new.modified := systimestamp; . –  Colin 't Hart Jan 16 at 13:22
add comment

1 Answer

up vote 1 down vote accepted

That is the MySQL documentation, not the Oracle Database documentation. There is no such clause in Oracle Datababase. Use a trigger. Here is an example: link

share|improve this answer
1  
Oracle MySQL Database vs Oracle Database? And Googling Oracle <SQL statement> no longer always points to Oracle Database documentation but sometimes MySQL documentation links will be listed first. No wonder people are getting confused. –  Colin 't Hart Jan 16 at 13:27
    
It's annoying :/ Though whacking the version number after Oracle is usually ok... –  FreshPhilOfSO Jan 16 at 13:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.