Re: Updateable View with Select Union

First thought -

    which table would you want the new data to go into ?

If you really need to implement this type of thing check "instead of" triggers.

Be warned, though, that this is okay for small inserts- but if you are planning to do batch/array inserts, any trigger turns a fast array process into a slow single-row process.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd
____Denmark May 21-23rd
____USA_(FL)_May 2nd

The three-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Govindan K" <gkatteri_at_yahoo.co.uk> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 20 March 2003 23:08 Subject: Updateable View with Select Union

> Hi list
>
> I need help on this. Trying to create a view with select
> union all as part of it.
>
> SQL> @view01b
> SQL> create table t1 (ind1 varchar2(02) , rundate1 date)
> 2 /
>
> Table created.
>
> SQL> create table t2 (ind2 varchar2(02) , rundate2 date)
> 2 /
>
> Table created.
>
> SQL> insert into t1 values('T1' , sysdate - 100)
> 2 /
>
> 1 row created.
>
> SQL> insert into t2 values('T2' , sysdate)
> 2 /
>
> 1 row created.
>
> SQL> create view v1 as
> 2 select ind1 , rundate1 from t1
> 3 union all
> 4 select ind2 , rundate2 from t2
> 5 with check option
> 6 /
>
> View created.
>
> SQL> select * from v1
> 2 /
>
> IN RUNDATE1
> -- ---------
> T1 10-DEC-02
> T2 20-MAR-03
>
> SQL> desc v1
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> IND1
> VARCHAR2(2)
> RUNDATE1 DATE
>
> SQL> /
>
> IN RUNDATE1
> -- ---------
> T1 10-DEC-02
> T2 20-MAR-03
>
> SQL> insert into v1 values ('T3' , sysdate + 10)
> 2 /
> insert into v1 values ('T3' , sysdate + 10)
> *
> ERROR at line 1:
> ORA-01732: data manipulation operation not legal on this
> view
>
> SQL> set echo off
>
> Any ideas?
>
> TIA
>
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: =?iso-8859-1?q?Govindan=20K?=
> INET: gkatteri_at_yahoo.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
services
> --------------------------------------------------------------------
-
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Thu Mar 20 2003 - 18:12:15 CST