Home » SQL & PL/SQL » SQL & PL/SQL » CREATE VIEW with "SELECT * FROM" several tables (Oracle 11 / Oracle 12)
CREATE VIEW with "SELECT * FROM" several tables [message #667170] Tue, 12 December 2017 09:24 Go to next message
Rumak18
Messages: 20
Registered: April 2009
Location: Germany
Junior Member
Hi folks,

there is a provider who states that he needs to get several tables from a schema in our database.
I've tried the following which obviously crashed:

CREATE VIEW 5600_VIEW AS SELECT * FROM TABLE1,TABLE2,TABLE3,TABLE4,TABLE5,TABLE6,TABLE7,TABLE8;
Unfortunately he needs to view over 80 tables.
I got the error "ora-00957 duplicate column name".
How can i realize issues like that?
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667171 is a reply to message #667170] Tue, 12 December 2017 09:32 Go to previous messageGo to next message
John Watson
Messages: 7219
Registered: January 2010
Location: Global Village
Senior Member
First, you should be naming the columns that you want to project.
Second, do you want a cartesian product?
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667172 is a reply to message #667171] Tue, 12 December 2017 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
>Unfortunately he needs to view over 80 tables.
So why don't you just GRANT SELECT ON TABLE1 TO PROVIDER; & do it once for every table?
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667173 is a reply to message #667172] Tue, 12 December 2017 10:41 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should be very glad there's duplicate column names otherwise that query may well have maxed out the servers CPU.

John mentioned cartesian product - that's where every row in 1 table is joined to every row in another table. You've got 8 tables cartesianed together. So every row in table 1 will be joined to every row in table2 and every row in table3 and ....... table8.
So the total number of rows it returns is: number of rows in table1 * number of rows in table2 * number of rows in table3 * ..... table8

Unless those tables are tiny that number is going to be gargantuan.

If the tables data is related then you need to write a select/view that specifies the relationships in join or where clauses.
If they aren't related they should be queries seperately and BlackSwan's suggestion is probably what you need.
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667182 is a reply to message #667170] Tue, 12 December 2017 15:17 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
If all tables have the same data types in the same order, a simple UNION ALL of all tables into a view would do the trick, naming the view columns of course.
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667183 is a reply to message #667182] Tue, 12 December 2017 15:52 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
joy_division wrote on Tue, 12 December 2017 13:17
If all tables have the same data types in the same order, a simple UNION ALL of all tables into a view would do the trick, naming the view columns of course.
If all tables have the same data types in the same order, most likely fail data Normalization.

Table name and/or column name should NEVER contain application data.


Re: CREATE VIEW with "SELECT * FROM" several tables [message #667186 is a reply to message #667183] Wed, 13 December 2017 02:53 Go to previous messageGo to next message
Rumak18
Messages: 20
Registered: April 2009
Location: Germany
Junior Member
@BlackSwan:
I will try this and get back to you
So why don't you just GRANT SELECT ON TABLE1 TO PROVIDER; & do it once for every table?
Thank you all for your contributions.
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667205 is a reply to message #667170] Thu, 14 December 2017 08:54 Go to previous messageGo to next message
Rumak18
Messages: 20
Registered: April 2009
Location: Germany
Junior Member
Hi,

great! It worked for me!
GRANT SELECT ON %SCHEMA%.%TABELNAME% TO %NEW_USER%
works as "system".
The new user can also query the tables.
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667206 is a reply to message #667205] Thu, 14 December 2017 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
Neither SYS nor SYSTEM schemas should ever be used for any activity involving application support.
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667213 is a reply to message #667206] Fri, 15 December 2017 01:27 Go to previous messageGo to next message
Rumak18
Messages: 20
Registered: April 2009
Location: Germany
Junior Member
Hi,

why is this so? Especially in this case?
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667228 is a reply to message #667213] Fri, 15 December 2017 07:15 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
https://docs.oracle.com/cd/E11882_01/server.112/e10897/users_secure.htm#ADMQS12003

SYS and SYSTEM Users
The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.

SYS

This account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema.

The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.

SYSTEM

This account can perform all administrative functions except the following:

Backup and recovery

Database upgrade

While this account can be used to perform day-to-day administrative tasks, Oracle strongly recommends creating named users account for administering the Oracle database to enable monitoring of database activity.
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667229 is a reply to message #667228] Fri, 15 December 2017 07:32 Go to previous messageGo to next message
Rumak18
Messages: 20
Registered: April 2009
Location: Germany
Junior Member
Hi,

i do understand this topic , but there is just no other user that can distribute privileges like "GRANT SELECT ON ..." to someone.
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667231 is a reply to message #667229] Fri, 15 December 2017 07:40 Go to previous message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
Rumak18 wrote on Fri, 15 December 2017 05:32
Hi,

i do understand this topic , but there is just no other user that can distribute privileges like "GRANT SELECT ON ..." to someone.

You should use SYS user to make NEW application user who has necessary privileges to manage the application & not use SYS or SYSTEM schemas
Previous Topic: existing state of package - Invalidated
Next Topic: Error When Refreshing Materialized View
Goto Forum:
  


Current Time: Fri Jan 19 16:43:31 CST 2018

Total time taken to generate the page: 0.06049 seconds