3 Replies Latest reply: Feb 15, 2013 1:20 PM by ranit B RSS

    Creating View And Public Synonym With The Same Name

    960593
      HI,

      I am able to create view and the public synonym with the same name but when I am quering view result is showing .

      Is there any logic behind this .

      CREATE OR REPLACE VIEW emp_dtls as SELECT * FROM emp;

      CREATE PUBLIC synonym emp_dtls for emp;

      SELECT * FROM emp_dtls;

      view result is showing

      Edited by: 957590 on Feb 15, 2013 10:32 AM
        • 1. Re: Creating View And Public Synonym With The Same Name
          JustinCave
          I'm not sure that I understand the problem.

          Would you expect that you could no longer query the view once a public synonym is created on the view? That would defeat the purpose of having a synonym.

          Assuming that your query does not fully qualify the object name, Oracle will first look for an object in the current user's schema. If you are logged in as the owner of the view, that would mean that Oracle resolved the query without ever even checking to see if a synonym existed. If there is no object in the current schema with the specified name, Oracle would then look at public synonyms. Finding one, it would then resolve the query to use the view.

          Justin
          • 2. Re: Creating View And Public Synonym With The Same Name
            Frank Kulash
            Hi,
            957590 wrote:
            HI,

            I am able to create view and the public synonym with the same name but when I am quering view result is showing .
            The result is supposed to show. What do you want to happen?
            If you don't create a public synonyum, does the view do what you want?
            Is there any logic behind this .
            Whenever you have a problem, please post a complete test script that people can run to re-create the problem and test their ideas.
            In this case, include CREATE TABLE and INSERT statements for the base table(s), if necessary. (This will not be necessary if you can show the problem using a view based on dual or scott.emp, for example.) Also include a CREATE VIEW and CREATE PUBLIC SYNONYM statments, CONNect commands (if necessary), some code that uses the view, and the results you want from that code.
            • 3. Re: Creating View And Public Synonym With The Same Name
              ranit B
              957590 wrote:
              HI,

              I am able to create view and the public synonym with the same name but when I am quering view result is showing .

              Is there any logic behind this .

              CREATE OR REPLACE VIEW emp_dtls as SELECT * FROM emp;

              CREATE PUBLIC synonym emp_dtls for emp;

              SELECT * FROM emp_dtls;

              view result is showing

              Edited by: 957590 on Feb 15, 2013 10:32 AM
              You do either -
              SELECT * FROM emp_dtls
              or
              SELECT * FROM emp

              Both will have same data... right???
              So, why are you bothered... and how are you so sure that it is the VIEW data?

              Change the View query and then check.