Re: Oddness Creating Views?

One last thought. You mentioned that you want to keep the views updatable. Well, Oracle has a simple rule. You can only update 1 table at a time, and 1 table only. A view is called a "simple view" if it is generated from one base table. This type of view is updatable (note exceptions below). A view is called a "complex view" if it is generated from 2 or more base tables. This type of view is NOT updatable.

For simple views, there are some rules when updating them: 1) If the view is a subset of columns from the base table, and you insert into the view, you must not violate any NOT NULL constraints that are in the base table, but may not appear in the view. For example, let's say the base table has three columns (col1, col2, col3), and col3 is specified as NOT NULL. And let's say the view has only col1 and col2. You CANNOT insert into the view, because you will only provide values for col1 and col2, leaving col3 blank, but this violates the NOT NULL constraint on col3 on the base table. You will get an error.

2) You cannot update a row in such a way that the row will subsequently disappear from the view. For example, lets say you had this view:

    CREATE VIEW emp_vu AS
    SELECT last_name, first_name, dept_code     FROM emp
    WHERE dept_code = 10;

You cannot update a row in the table and set DEPT_CODE = 20. If you do this, the row will no longer qualify to be in the view in the first place, and therefore is not allowed. Basically, the row will disappear out of the view before you will even get the chance to COMMIT the changes. This will endanger data integrity. If you try this, you will get an error.

Andrew Simkovsky
OCP DBA Doug Clark wrote in message <9bg8bo$72v$1_at_newstest.laserlink.net>...
>Andrew,
>
>Thanks there's some good things for me to think about there. Just getting
>my brain around shifting MS Access to Oracle in a self taught floundering
>sort of way.
>
>Doug.
>
>"Andrew Simkovsky" <asimkovsky_at_email.msn.com> wrote in message
>news:OAt4dQsxAHA.83_at_cpmsnbbsa07...
>> > How come I can do this:
>> >
>> > select * from pdtable_12_1, pdtable_12_99
>> >
>> > Which returns all the rows in all the tables between 1 & 99 in the
>> > sequence.
>> >
>> > But I cannot do this:
>> >
>> > create view pdtable_12_All as
>> > select * from pdtable_12_1, pdtable_12_99
>> >
>> > which gives the error: "ORA-00957 duplicate column name" and points at
>> > the
>> > '*'
>> When you are just running the query, you are getting back all the
 results,
>> but the structure is not stored anywhere. When you create a view, the
>> structure is stored, and it has to follow the rules of a table, meaning
 no
>> duplicate column names. Obviously the pdtable_12_1 and pdtable_12_99
 both
>> have column names a that are the same. By the way, the queries you are
>> showing are Cartesian Products, which should be avoid at all costs. You
 need
>> to have a WHERE clause in there to do a proper join.
>>
>>
>> > Further I can do this:
>> >
>> > create view pdtable_12_All as
>> > select unit_code from pdtable_12_1
>> >
>> > But cannot do this:
>> >
>> > create view pdtable_12_All as
>> > select unit_code from pdtable_12_1, pdtable_12_99
>> >
>> > which gives the error: "ORA-00918 column ambiguously defined" and
>> > points at
>> > 'unit_code' (the column name).
>> In the first statement, you are selecting a column from a single table.
 In
>> the first statement, you specify a column, but both tables have a column
>> called unit_code, so it doesn't know which one you want. You need to
>> specify it by using table.column_name notation (or use table aliases)
 like
>> this:
>>
>> create view pdtable_12_All as
>> select a.unit_code from pdtable_12_1 a, pdtable_12_99 b
>>
>> Again, you need a WHERE clause in this to avoid the Cartesian Product.
>>
>> Andrew Simkovsky
>> OCP DBA
>>
>>
>>
>>
>>
>>
>>
>>
>
>
Received on Tue Apr 17 2001 - 00:23:08 CDT