Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00984: column not allowed here, when trying to use a UDF as a default

user11975165Nov 10 2010 — edited Nov 10 2010
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.
This post has been answered by Frank Kulash on Nov 10 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 8 2010
Added on Nov 10 2010
2 comments
3,765 views