Home » SQL & PL/SQL » SQL & PL/SQL » question on views
question on views [message #248988] Tue, 03 July 2007 00:54 Go to next message
srilaxmi
Messages: 16
Registered: June 2007
Location: hyderabad
Junior Member
I have a doubt on views
here i have created a view as

sql>create view emp_view as select * from emp where 1=2;
view created
sql>select * from emp_view;
no rows selected

Till now I understood

now if I try to insert rows into view as
sql>insert into emp_view(empno) values(1234);
1 row inserted

sql>select * from emp_view;
no rows selected

sql>select * from emp;
Now all the contents of emp and the newly inserted record(1234) is also displayed in the emp table.

My question is why cant I see the contents of emp_view by select stmt.



Re: question on views [message #248993 is a reply to message #248988] Tue, 03 July 2007 01:05 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Because no row exists where 1 = 2

Michael
Re: question on views [message #248996 is a reply to message #248988] Tue, 03 July 2007 01:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
srilaxmi wrote on Tue, 03 July 2007 07:54
My question is why cant I see the contents of emp_view by select stmt.


Now, read your statement again:
srilaxmi wrote on Tue, 03 July 2007 07:54
sql>create view emp_view as select * from emp where 1=2;

The view's definition did not change. It still is the same select. That's what a view is: a stored select statement. You have an updatable view: if you insert into the view, you actually insert into the source table. You verified that. Oracle provides a way to avoid these kind of inserts (the ones you insert through the view but you won't be able to see afterwards) through the 'WITH CHECK OPTION'.

Here's a test script:
CREATE TABLE mhe_foo ( col1 NUMBER, col2 VARCHAR2(10))
/

CREATE VIEW mhe_vw1 
AS 
SELECT col1
     , col2
FROM   mhe_foo
WHERE  1 = 2
/

CREATE VIEW mhe_vw2
AS 
SELECT col1
     , col2
FROM   mhe_foo
WHERE  1 = 2
WITH CHECK OPTION
/

INSERT INTO mhe_vw1(col1, col2) VALUES (1, 'One')
/

SELECT *
FROM   mhe_vw1
/
SELECT *
FROM   mhe_foo
/

INSERT INTO mhe_vw2(col1, col2) VALUES (2, 'Two')
/

DROP VIEW mhe_vw1
/
DROP VIEW mhe_vw2
/

DROP TABLE mhe_foo
/
When you run it, you'll see this:
SQL> @orafaq

Table created.


View created.


View created.


1 row created.


no rows selected


      COL1 COL2
---------- ----------
         1 One

INSERT INTO mhe_vw2(col1, col2) VALUES (2, 'Two')
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation



View dropped.


View dropped.


Table dropped.

SQL>
As you can see, the "WITH CHECK OPTION" prevents you from inserting data you can't select afterwards.

MHE
Re: question on views [message #248997 is a reply to message #248988] Tue, 03 July 2007 01:13 Go to previous messageGo to next message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
Because view is just a stored select statement. Whenever you query the view it will just execute the stored select statment.

Your select statement is
SELECT empno FROM emp WHERE 1 = 2


This will never retrieve records.

Regards,
Prabhu
Re: question on views [message #249005 is a reply to message #248997] Tue, 03 July 2007 01:24 Go to previous message
srilaxmi
Messages: 16
Registered: June 2007
Location: hyderabad
Junior Member
Thanks all
Previous Topic: ORA-00600, Errror while creating index
Next Topic: FORCE VIEW
Goto Forum:
  


Current Time: Fri Dec 09 21:04:56 CST 2016

Total time taken to generate the page: 0.12319 seconds