| error on inserting into a view [message #8332] |
Sun, 10 August 2003 07:48  |
hrishita
Messages: 13
Registered: August 2003
|
Junior Member |
|
|
could anyone tell me where i went wrong---
i created a view taking a tables data .when i am trying to insert into the view it gives error "virtual column not allowed here"
create view vv(c1,c2,s) as select col1,col2,'R' from empp;
where empp structure is
SQL> desc empp;
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER(5)
COL2 NUMBER(3)
following is the error when i am trying to insert
SQL> insert into vv values (6,6,'l');
insert into vv values (6,6,'l')
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
|
|
|
|
|
|
| Re: error on inserting into a view [message #8345 is a reply to message #8337] |
Mon, 11 August 2003 21:53  |
hrishita
Messages: 13
Registered: August 2003
|
Junior Member |
|
|
I was able to create view vv as create view vv(c1,c2,s) as select col1,col2,'R' from empp;
after which i am trying to insert a record into the view
create view vv(c1,c2,s) as select col1,col2,'R' from empp;
then i get the error
create view vv(c1,c2,s) as select col1,col2,'R' from empp;
|
|
|
|
| Re: error on inserting into a view [message #8347 is a reply to message #8345] |
Tue, 12 August 2003 03:58 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
A view is a stored query. When you enter
SELECT * FROM vv;
Oracle translates that into
SELECT * FROM
( select col1, col2, 'R' from empp );
which can be simplified to
select col1, col2, 'R' from empp;
The third column displays, by the view's definition, the constant literal 'R'. It is not stored anywhere on the database, but just displayed whenever you query the view.
If you insert a row into empp containing values (6, 6), next time you query the view you will see a new row containing (6, 6, 'R').
|
|
|
|
| Re: error on inserting into a view [message #8357 is a reply to message #8347] |
Tue, 12 August 2003 21:26 |
hrishita
Messages: 13
Registered: August 2003
|
Junior Member |
|
|
I read that INSERT can be used on a view defined on a single table and hence i was trying to insert (6,6,'l') into the view.
According to your explanation its not possible to insert inot a view.
|
|
|
|
| Re: error on inserting into a view [message #8359 is a reply to message #8357] |
Wed, 13 August 2003 00:28 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
You can insert into a view but there are limitations, because the contents of the view are not stored anywhere; the view is just a query. What you are actually doing is inserting into the base table via the view definition. You can do
insert into vv (c1, c2) values (6, 6);
I don't see how else it could work...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
