ORA-00907 while trying to create a table with automatic column

Question

I'm attempting to create a table with an automatic column, the value of which is computed using a function I've defined. However, when I try to create the table I keep getting ora-00907: Missing right parenthesis. Can anyone help?

Here is the CREATE code:

CREATE TABLE NEW_EMP2 (
SSN CHAR(9), 
EMP_NUM2 CHAR(5) automatic as newemp2id(SSN), 
Fname VARCHAR2(15), 
Lname VARCHAR2(15), 
Bdate DATE
)

Here is the code for the function newemp2id:

CREATE OR REPLACE FUNCTION newemp2id (i_ssn NCHAR) RETURN NCHAR
IS
BEGIN
RETURN 'E'||(1000+SUBSTR(i_ssn,6,4));
END

Any help on this would be greatly appreciated, thanks!

UPDATE: I'm using Oracle Express Edition on a Windows Vista machine, in case that makes any difference.

Answer 1

What's an automatic column supposed to be? Did you mean a purely computed i.e. virtual column? Then your statement should look like this:

CREATE TABLE NEW_EMP2 (
SSN CHAR(9), 
EMP_NUM2 CHAR(5) GENERATED ALWAYS AS ( newemp2id(SSN) ) VIRTUAL, 
Fname VARCHAR2(15), 
Lname VARCHAR2(15), 
Bdate DATE
)

And your functions need to declared deterministic:

CREATE OR REPLACE FUNCTION newemp2id (i_ssn NCHAR) RETURN NCHAR DETERMINISTIC 
IS
BEGIN
RETURN 'E'||(1000+SUBSTR(i_ssn,6,4));
END

If I'm not mistaken, virtual columns were introduced with Oracle 11g.

Answer 2

Oracle Express is Oracle 10g.

According to the manual (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2095331) there is no "automatic" keyword and Oracle 10 has never supported "computed columns"

Oracle 11g supports virtual columns, but they are created using GENERATED ALWAYS, not even Oracle 11g has an automatic keyword

Why do you think this should work in Oracle?

Answer 3

I hadn't heard of the syntax prior to this, but all I could find is this PDF for Oracle RDB. RDB was/is a separate product for Oracle databases... Confirmed - not supported on 10g

Use a BEFORE INSERT trigger instead, because I don't believe the syntax you're using is valid for Oracle Express (10g effectively) - there's no mention in the CREATE TABLE or ALTER TABLE documentation.

I'm not fond of using triggers, I'd prefer to have a single stored procedure for inserting into given table(s) & only allow anyone to use the procedure rather than direct table access...

CREATE OR REPLACE TRIGGER newemp2_before_insert
BEFORE INSERT
    ON new_mep2
    FOR EACH ROW
BEGIN

    -- Update created_by field to the username of the person performing the INSERT
    :new.emp_num2 := newemp2id(new.ssn)
END;

Though frankly, this is overcomplicated when it could be handled in a view:

CREATE VIEW vw_emp AS
  SELECT t.ssn,
         'E'||(1000+SUBSTR(i_ssn,6,4)) AS emp_num2
    FROM NEW_EMP2 t