Re: Inserting into Oracle views

Hi

Your table has a NOT NULL constraint on stnid in you underlying table. Unless the underlying table has a before insert trigger on it I don't believe that you could do the insert to the table in this way. IIRC with check option generates a constraint on the view not the underlying table.

I must admit it has been a while since I looked at this so this may not in fact be the case. I would advise you to check your sql and app developer reference carefully.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
<casey_allred_at_my-deja.com> wrote in message
news:8tsk59$os0$1_at_nnrp1.deja.com...


> 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.