Re: Inline Views

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 01 Feb 1999 15:31:31 GMT
Message-ID: <36b7c81a.8477610@192.86.155.100>

A copy of this was sent to sdgreenwood_at_ybs.co.uk (if that email address didn't require changing) On Mon, 01 Feb 1999 14:18:29 GMT, you wrote:

>I have never used inline views and would like to know more about them.
>
>I can anybody help ?
>
>Thanks in anticipation
>
>SDG
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

an inline view is just a subquery in place of a table in an INSERT, UPDATE, DELETE, or SELECT. If you could have issued a "create view as <subquery>" and then inserted/updated/deleted/selected from it, you can skip the create view and just inline it in the DML.

Consider:

SQL> select * from ( select empno, ename from emp where deptno = 10 );

EMPNO ENAME
---------- ----------

      7782 CLARK
      7839 KING
      7934 MILLER

SQL> delete from ( select empno, ename from emp where deptno = 10 ); 3 rows deleted.

SQL> update ( select empno, ename from emp where deptno = 20 ) 2 set ename = lower( ename );
5 rows updated.

SQL> insert into ( select empno, ename from emp where deptno = 20 with check option ) values ( 1, 'x' );
insert into ( select empno, ename from emp where deptno = 20 with check option ) values ( 1, 'x' )

ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> insert into ( select empno, ename, deptno from emp 2 where deptno = 20 with check option ) values ( 1, 'x', 20 );

1 row created.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Feb 01 1999 - 09:31:31 CST