Re: Inserting into Oracle views

The problem here is your use of to_char in the select expression.

Looks like you need to use an instead of trigger, specifically designed for manipulating views.

Hth,

Sybrand Bakker, Oracle DBA

<casey_allred_at_my-deja.com> wrote in message news:8tpusf$ho2$1_at_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 - 16:30:57 CST