Column DEFAULT Clause - Sybase SQL Anywhere to Oracle Migration

The DEFAULT clause allows you specifying the default value for a column. The default value is assigned if you omit the column or specify DEFAULT keyword in a INSERT statement.

Quick Example:

  -- Create a table 
   CREATE TABLE teams
   (
     id int,
     name VARCHAR(80) DEFAULT 'N/A'
   );
 
   -- Insert default value 'N/A' to name column   
   INSERT INTO teams(id) VALUES (1);
   INSERT INTO teams VALUES (2, DEFAULT);
 
   -- Insert default values to all columns  
   INSERT INTO teams DEFAULT VALUES;
 
   -- Insert NULL, not default (!) to name column
   INSERT INTO teams VALUES (2, NULL);

For more information, see DEFAULT Clause in Sybase SQL Anywhere.

Conversion summary:

denotes syntax or functional differences, or more strict restrictions
no syntax conversion required
feature is not supported

	Sybase SQL Anywhere	Oracle
Default Clause	DEFAULT default_value	Functions, expressions etc. conversion
Insert Default Value	Omit column, specify DEFAULT keyword
Insert All Defaults	DEFAULT VALUES
NULL Value Inserts NULL

Last Update: Sybase SQL Anywhere 12.0 and Oracle 11g

Both in Sybase SQL Anywhere and Oracle, you can define a DEFAULT clause containing a string literal or number:

Oracle:

  -- Create a table 
   CREATE TABLE teams
   (
     id int,
     name VARCHAR2(80) DEFAULT 'N/A'
   );

Then to insert a default value, you can omit the column or specify the DEFAULT keyword in a INSERT statement. Note that specifying NULL explicitly in INSERT inserts NULL, not the default value:

Oracle:

   -- Insert default value 'N/A' to name column   
   INSERT INTO teams(id) VALUES (1);
   INSERT INTO teams VALUES (2, DEFAULT);
 
   -- Insert NULL, not default (!) to name column
   INSERT INTO teams VALUES (2, NULL);

Oracle does not support DEFAULT VALUE keyword to insert default values to all columns:

Oracle:

  INSERT INTO teams DEFAULT VALUES;
  -- ERROR at line 1:
  -- ORA-00926: missing VALUES keyword

Besides string literals and number, DEFAULT clause often contains special values, functions and expressions that need to be converted to Oracle syntax.

Typical DEFAULT clauses in Sybase SQL Anywhere and their conversion to Oracle:

Unlike DEFAULT CURRENT TIMESTAMP that is set by INSERT statement only, DEFAULT TIMESTAMP is set by both INSERT and UPDATE statements in Sybase SQL Anywhere (More...):

Sybase SQL Anywhere:

   CREATE TABLE products
   (
      name VARCHAR(90),
      added TIMESTAMP DEFAULT CURRENT TIMESTAMP,
      updated TIMESTAMP DEFAULT TIMESTAMP,
      price NUMERIC(7,2)
   );
 
   -- Insert a row with default values for added and updated columns
   INSERT INTO products(name, price) VALUES ('Orange', 1.39);
 
   -- Now update the price (updated column will be also modified)
   UPDATE products SET price = price - 0.3 WHERE name = 'Orange';

Table content after UPDATE:

In Oracle, you have to either use a BEFORE UPDATE trigger to update the column, or set the new value explicitly in UPDATE statement

Oracle:

   CREATE TABLE products
   (
      name  VARCHAR2(90),
      added  TIMESTAMP(6) DEFAULT SYSTIMESTAMP,
      updated  TIMESTAMP(6) DEFAULT SYSTIMESTAMP,
      price  NUMBER(7,2)
   );
 
   -- Define a trigger to update updated column
   CREATE OR REPLACE TRIGGER tr_products_updated 
     BEFORE UPDATE ON products FOR EACH ROW
   BEGIN
     :NEW.updated := SYSTIMESTAMP;
   END;
   /
 
    -- Insert a row with default values for added and updated columns
   INSERT INTO products(name, price) VALUES ('Orange', 1.39);
 
   -- Now update the price (updated column will be also modified)
   UPDATE products SET price = price - 0.3 WHERE name = 'Orange';

If you do not want to use a trigger, you can update the column in UPDATE statement explicitly:

    -- Now update the price and updated columns
   UPDATE products  
   SET price = price - 0.3, updated = SYSTIMESTAMP 
   WHERE name = 'Orange';

Sybase SQL Anywhere

• Reference and Articles
• DEFAULT Clause

Oracle

• Reference and Articles