Inserting into Oracle views

From: <casey_allred_at_my-deja.com>
Date: Wed, 01 Nov 2000 20:36:31 GMT
Message-ID: <8tpusf$ho2$1@nnrp1.deja.com>

It's my understanding that you can insert into an oracle view as long as the view's defining query does not contain one of the following constructs:

set operator
GROUP BY clause
group function
DISTINCT operator
flattened subqueries
nested table columns
CAST and MULTISET expressions

I am using Oracle 8i and have the following table:

Create Table WthrHourly
(

StnID	Varchar2(16) not null,
Concept	Varchar2(16) not null,
Dt	date not null,
Hr0	Number(38,30),
Hr1	Number(38,30),
Hr2	Number(38,30),
Hr3	Number(38,30),
Hr4	Number(38,30),
Hr5	Number(38,30),
Hr6	Number(38,30),
Hr7	Number(38,30),
Hr8	Number(38,30),
Hr9	Number(38,30),
Hr10	Number(38,30),
Hr11	Number(38,30),
Hr12	Number(38,30),
Hr13	Number(38,30),
Hr14	Number(38,30),
Hr15	Number(38,30),
Hr16	Number(38,30),
Hr17	Number(38,30),
Hr18	Number(38,30),
Hr19	Number(38,30),
Hr20	Number(38,30),
Hr21	Number(38,30),
Hr22	Number(38,30),
Hr23	Number(38,30)
)
	Tablespace USER_DATA
 	Maxtrans 255
	Initrans 1
        Pctfree  10
        Pctused  40
        Storage


            (initial       500k

             next          500k
             minextents    1
             maxextents    121
             pctincrease   0 );

Alter Table WthrHourly

    Add Constraint pk_WthrHourly Primary Key     (
    StnID,
    Concept,
    Dt
    )
    Using Index
    Tablespace Index01
 Storage

(initial 100k

             next          100k
             minextents    1
             maxextents    121
             pctincrease   0 );

and I have the following view:

CREATE VIEW Hourly_Abeline_Drybulb AS SELECT TO_CHAR(Dt, 'YYYY') AS

Year, TO_CHAR(Dt, 'MM') AS Month, TO_CHAR(Dt, 'DD') AS Day,
    Hr0, Hr1, Hr2, Hr3, Hr4, Hr5, Hr6, Hr7, Hr8, Hr9, Hr10,
    Hr11, Hr12, Hr13, Hr14, Hr15, Hr16, Hr17, Hr18, Hr19, Hr20,

    Hr21, Hr22, Hr23
FROM WthrHourly
WHERE (Concept = 'Drybulb') AND (StnID ='ABI');

I am trying perform this insert:
insert into Hourly_abeline_drybulb values ('2000','10','31','','','','','','','','','','','','','','','','','','', '','','','','','')

and I get this result:
ERROR at line 1:
ORA-01733: virtual column not allowed here

Is my assumption about being able to insert into views incorrect or am I missing some constraint? I believe the view doesn't contain any of the unallowed constructs. Any assistance would be greatly appreciated. -Casey

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 01 2000 - 14:36:31 CST