I am wanting to create a table that has a default value of a user defined function and because this default value is going to be used in a few different places hence I would like to use a function rather than in the table definition.
This is my current code which works fine:
CREATE TABLE DEPT_HIST
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
SQL_ACTV_C CHAR(1) not null,
EFFT_STRT_S TIMESTAMP(6) default SYSTIMESTAMP not null,
EFFT_END_S TIMESTAMP(6) default TO_TIMESTAMP('9999/12/30 00:00:00.000000', 'YYYY/MM/DD:HH24:MI:SS.FF6') not null,
DELT_F CHAR(1) default 'N' not null
);
but I would like to get something similar to this to work:
CREATE OR REPLACE FUNCTION EOT
RETURN timestamp
IS
Result timestamp;
BEGIN
RETURN (TO_TIMESTAMP ('9999/12/30 00:00:00.000000',
'YYYY/MM/DD:HH24:MI:SS.FF6'));
END EOT;
/
select eot from dual;
EOT
---------------------------------------------------------------------------
30/DEC/99 12:00:00.000000000 AM
CREATE TABLE DEPT_HIST
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
SQL_ACTV_C CHAR(1) not null,
EFFT_STRT_S TIMESTAMP(6) default SYSTIMESTAMP not null,
EFFT_END_S TIMESTAMP(6) default EOT not null,
DELT_F CHAR(1) default 'N' not null
);
but I get an error of:
EFFT_END_S TIMESTAMP(6) default EOT not null,
*
ERROR at line 8:
ORA-00984: column not allowed here
Any ideas? I guess I could use a trigger but not exactly what I am after.