Re: Inserting into Oracle views

Thanks for responding.

I changed my views to this

CREATE VIEW Hourly_Abeline_Drybulb AS SELECT Dt,

    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') WITH CHECK OPTION; and tried the following insert:

insert into Hourly_Abeline_Drybulb values(TO_DATE ('10/31/2000','MM/DD/YYYY'),'','','','','','','','','','','','','','','' ,'','','','','','','','','')

I get this error:
ERROR at line 1:
ORA-01400: cannot insert NULL into
("ERCOTNDAUTO_MASTER"."WTHRHOURLY"."STNID") I thought that with the Check Option I wouldn't have to deal with the filter. I would think that the view would already have knowledge of what was in the original where clause. Also, there is no way for me insert that field because it's not part of the fields returned by the view. I am just trying to avoid using the instead of trigger. Any ideas?

-Casey

In article <973118536.26943.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> 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.
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 02 2000 - 14:51:57 CST