sysdate / to_date and DATE type ORA-00932: inconsistent datatypes?

Using Oracle 8.1.7:

Why does the following SQL fail with an "ORA-00932: inconsistent datatype" error?

create or replace type test_type as object (   test_date DATE
)

create or replace view test_vw
  of test_type
  with object identifier (test_date)
as (
  select

      sysdate -- this also fails with to_date('01-JAN-2001')     from
      dual
)

In comparison, the following view compiles successfully:

create or replace type test_type as object (   test_date VARCHAR2(8)
)

create or replace view test_vw
  of test_type
  with object identifier (test_date)
as (
  select

      to_char(sysdate, 'ddmmyy')
    from
      dual
)

Any ideas?

Thanks,

Jason Received on Thu Sep 20 2001 - 03:38:21 CEST