[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Table and view with same name

Posted on 2014-01-20
13
Medium Priority
?
1,234 Views
Last Modified: 2014-01-23
If I have same name on table and view in oracle, which one is accessed first and why ?
0
Comment
Question by:d27m11y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39795464
>>> same name on table and view

within a single schema that is not possible.
0
 

Author Comment

by:d27m11y
ID: 39795485
Can we create table and MV on the same name in same schema  ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39795507
no
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:d27m11y
ID: 39795559
So, can we create any tables and views/MVS or any other db objects with same name in the same schema...
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39795567
Hi!

As previous posts points out you can't have the same name for table and view or two tables or two views in the same schema.

So if you get two rows by executing this query
select table_name from all_tables where table_name = '<yourtable>'
then you will have to add the column owner to the select
select owner,table_name from all_tables where table_name = '<yourtable>'
to determine which table you want to use and select from.
The database does not make that decision for you. And of course you (your login id has to have select privileges on that table to access the data.

Regards,
    Tomas Helgi
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39795597
To add to the list of no's.

Don't think of them as their individual types like table, view, etc...

As you mentioned above:  Think of them as "objects".  Within a single schema, object names are unique.

I suggest you just try it.  Log into a user and issue the two following statements:
create table myobject(col1 char(1));
create view myobject as select * from dual;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39795689
I'm afraid I have to disagree with both of the previous posts...

yes, you CAN have multiple objects in the same schema with the same name.

However, there are restrictions on the types of objects that can share a namespace.

For example...
SQL> create table samename(n number);

Table created.

SQL> create view samename as select * from user_tables;
create view samename as select * from user_tables
                                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> create index samename on samename (n);

Index created.

SQL> select object_name,object_type from dba_objects where object_name = 'SAMENAME';

OBJECT_NAME                         OBJECT_TYPE
----------------------------------- -----------------------
SAMENAME                            INDEX
SAMENAME                            TABLE

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39795694
For more information on the rules of naming objects, specifically which object types share a namespace  see the SQL Reference'

http://docs.oracle.com/cd/E16655_01/server.121/e17209/sql_elements008.htm#SQLRF51129


Within a namespace, no two objects can have the same name.

The following schema objects share one namespace:

    Tables

    Views

    Sequences

    Private synonyms

    Stand-alone procedures

    Stand-alone stored functions

    Packages

    Materialized views

    User-defined types

    User-defined operators

Each of the following schema objects has its own namespace:

    Indexes

    Constraints

    Clusters

    Database triggers

    Private database links

    Dimensions
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39795698
So, given above, you can now continue my example above...

create trigger samename
before insert or update or delete on samename
begin
null;
end;

ALTER TABLE samename ADD CONSTRAINT samename UNIQUE (N);


etc
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39795707
The real question is not so much "can" you do it, but rather "SHOULD" you do it?

And the answer is usually NO.  There is no advantage to naming two different objects with the same name,  it simply creates ambiguity.

Having said that,  if you create a unique or primary constraint on a table and there isn't already another index available to support it,  the constraint will, by default, create an index of the same name.

Is this good or bad?  I don't know.  Most people that use oracle regularly are probably used to it even if it does violate their best practices for all other object types.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 39796114
just create a synonym  and point it to whatever object it has to point

create table data_table (x number);

create view data_view as select * from data_table;

create synonym data for data_view;
or
create synonym data for data_table;
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39796139
to keep it simple and short, it depends on which object types can have the same names and which cannot in the same schema. I guess you should have understood the answer from the posts above. I got curious as to why made you ask this question and what is your requirement ?

It is possible that at times if you can put down your requirement, may be one of the experts can give the ideal solution to suit your requirement.

I guess you might have asked this to just understand from technical point/interview questions etc..

Thanks,
0
 

Author Comment

by:d27m11y
ID: 39805248
I was asked this question in interview and I said we may not be able to create it. Looks like I was wrong in answering that question.

Thank you for your help and valuable suggestions..
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question