Re: Newbie has Oracle questions…

> Newbie has Oracle questions…
>
> 1. I’ve got a whole bunch of table in a Oracle database I want to
> concatenate into a single view, where I would like to be able to modify
> the data in the view if I wished. All the separate tables have a index
> that is unique for all of the tables. So far I’ve tried the below…
>
> Create view NEW-VIEW as
> Select * from X-A
> Union select * from X-B
> Union select * from X-C
>
> And so on…
>
> I’ve not been able to test whether this locks the records or not yet
> but I expect it to from my MS-Access experience. I’ve seen this done
> on SQL server in the past but can’t remember how?
>
> 2. I’ve tried to link the above created view into an MS-Access database
> using the Oracle ODBC driver. I’ve successfully linked in other tables
> from the same Oracle database but with this view I get the error ORA-
> 01013 and a text message which I can’t remember now I’m at home.
> What’s up with this then?
>
> We’re running Oracle 8.0.5. I would ask our Oracle administrator if we
> had one, so I’m in fumble around mode at the moment.

Oracle, unlike SQL Server and Access is not a toy. What you are trying to do will work if each row of each table is identical but you MUST have a WHERE clause that defines the relationship between the tables. And if you have multiple tables with absolutely identical fields then it begs the question why you don't just put them all into a single table which would be substantially faster.

If the tables are not identical in structure, but substantially the same it is still bad design but you can make the UNIONs work by creating dummy fields like this:

select field1, field2, field3
from table1
union
select field1, null, field3
from table2

Hope this helps.

Daniel A. Morgan Received on Mon Jan 15 2001 - 23:45:03 CST