7 Replies Latest reply: Jan 28, 2013 4:15 PM by 986173 RSS

    call view from view

    986173
      I want to create a view that can reference to several other views which extract data from other views. All the views involved are created by me. It seems working fine if I use a third party software (Navicat), but if I use Microsoft Excel or from Web, I am given an error saying the view does not exist. I suspect if Oracle does not allow cross references of user views. Is that true?
        • 1. Re: call view from view
          sb92075
          983170 wrote:
          I want to create a view that can reference to several other views which extract data from other views. All the views involved are created by me. It seems working fine if I use a third party software (Navicat), but if I use Microsoft Excel or from Web, I am given an error saying the view does not exist. I suspect if Oracle does not allow cross references of user views. Is that true?
          no

          did you start a new session after all the views were created?


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: call view from view
            986173
            What do you mean by "a new session"?

            I used external apps to access my view, such as Excel and Web apps.

            Thanks.
            • 3. Re: call view from view
              Srini Chavali-Oracle
              Pl identify the account you use to connect from the external app to the database. Does this account have privileges to access the views and any other required objects in the database ? Pl also post the exact error code and message that you get

              HTH
              Srini
              • 4. Re: call view from view
                sb92075
                983170 wrote:
                What do you mean by "a new session"?

                I used external apps to access my view, such as Excel and Web apps.

                Thanks.
                we don't know what you have, what you see, or what you do.

                since we don't know what you do, it is not possible to say what you are doing wrong.

                It could be error of commission or error of omission.

                SQL is SQL regardless of client that invokes it.

                How do I ask a question on the forums?
                SQL and PL/SQL FAQ
                • 5. Re: call view from view
                  986173
                  I forgot to mention is: all this happened after our Oracle was upgraded from 10g to 11g. Also, I find the problem is not limited to "call view from view" situation. Let me describe the problem again:

                  We used to be able to create view and grant permission to another user to use the view. But after upgrade from 10g to 11g, even we still can use navicate to create view and grant privileges, but nothing is actually committed on the server. As the result, we get "table or view not exit" error when calling the view created after the upgrade.

                  Thanks.
                  • 6. Re: call view from view
                    Mark Malakanov (user11181920)
                    most likely Excel creates SQL query with table name in double-quotes. Try to specify table and column names in upper-case.
                    • 7. Re: call view from view
                      Mark Malakanov (user11181920)
                      ...we still can use navicate to create view and grant privileges, but nothing is actually committed on the server.
                      It is an issue with "navicate". Contact their support.

                      Or use SQL*Plus or SQL Developer. the latter is free.