Setting the default value to the current user in Oracle

Question

I am trying to create a new table in Oracle 11g where the default value for a column is the currently logged in user. I need to do this is for logging purposes.

CREATE TABLE tracking (
  pk NUMBER(19,0) PRIMARY KEY, 
  description VARCHAR2(50),
  created_by VARCHAR2(128) DEFAULT CURRENT_USER
);

How can I write the DEFAULT CURRENT_USER section so it will take the current Oracle user as the default value? I know I could use a trigger, but I shouldn't have to...

Answer 1

You need to use USER not CURRENT_USER:

CREATE TABLE tracking (
  pk NUMBER(19,0) PRIMARY KEY, 
  description VARCHAR2(50),
  created_by VARCHAR2(128) DEFAULT USER
);

SQL Fiddle

The maximum length of a user is 30, so you could reduce this and I would increase the size of your DESCRIPTION column unless you're very sure that everything will come in at less than 51 characters.

Answer 2

Try user instead:

CREATE TABLE tracking (
  pk NUMBER(19,0) PRIMARY KEY, 
  description VARCHAR2(50),
  created_by VARCHAR2(128) DEFAULT USER
);

By the way, I also think created_at for the datetime is another useful default column.