3

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?

  • 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 '14 at 13:22
  • I wish oracle adds ON UPDATE ... so devs that has no option, and stuck with oracle due to corporate policy, can feel better :). – Espresso Mar 30 at 23:53
6

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

| improve this answer | |
  • 2
    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 '14 at 13:27
  • It's annoying :/ Though whacking the version number after Oracle is usually ok... – Philᵀᴹ Jan 16 '14 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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