8 Replies Latest reply: Oct 9, 2012 2:44 AM by Paul Horth RSS

    How to execute view by sql developer?

    955649
      Hi all,

      Hope doing well,

      sir i am having one view which i created in sql developer and it's showing in connections

      but when i am running this view it's showing result table or view does not exist.

      how to run this view ?

      thanks
        • 1. Re: How to execute view by sql developer?
          BluShadow
          Do you have select permissions on the base tables of the view for the user running the query against it?
          • 2. Re: How to execute view by sql developer?
            955649
            Hi sir,

            if i executing this view like select * from "vwEmpMain"; then it's working.
            else it's not working.
            • 3. Re: How to execute view by sql developer?
              Paul  Horth
              952646 wrote:
              Hi all,

              Hope doing well,

              sir i am having one view which i created in sql developer and it's showing in connections

              but when i am running this view it's showing result table or view does not exist.

              how to run this view ?

              thanks
              Show exactly what you are executing and the exact error message you get.
              • 4. Re: How to execute view by sql developer?
                Veejays.User10302525-Oracle
                Hi,

                if you have created the view with mixed case using double quotes as stated in your post, you will have to execute the query on it using double quotes, also just clicking on the view in sql developer opens the view structure, not sure what issue you are facing.
                 select * from "vwEmpMain";
                Regards,
                Vijay.
                • 5. Re: How to execute view by sql developer?
                  Chanchal Wankhade
                  Hi,

                  In case you can create a view and retrive the data then it should not have any issue with the current user_.

                  If you are quering this view from other user then you need to grant a privileges to the other users.

                  Show error or your logic to posible solution.
                  • 6. Re: How to execute view by sql developer?
                    955649
                    Hi sir,

                    here is my view which i created

                    Create Or Replace Force View "AMPLEX_GRAND1"."vwEmpMain" As
                    SELECT Employee.Emp_ID ,
                    Employee.Emp_FirstName || ' ' || Employee.Emp_LastName Emp_Name ,
                    Comp_Master.Comp_Name ,
                    Dept_Master.Dept_Name ,
                    Desig_Master.Desig_Name ,
                    Category_Master.Cat_Name ,
                    Dept_Master.Dept_Desc ,
                    Category_Master.Cat_Desc ,
                    Desig_Master.Desig_Desc ,
                    Comp_Master.Parent_ID HiredBy ,
                    Employee.Card_ID ,
                    Employee.Comp_ID ,
                    Employee.Dept_Code ,
                    Employee.Cat_Code ,
                    Employee.Desig_Code ,
                    Employee.ADDRESS ,
                    Employee.Phone ,
                    Comp_Master.STATUS Comp_Status ,
                    Employee.DOJ Date_Of_Join
                    FROM Comp_Master
                    RIGHT JOIN Employee
                    ON Comp_Master.Comp_ID = Employee.Comp_ID
                    LEFT JOIN Desig_Master
                    ON Employee.Desig_Code = Desig_Master.Desig_Code
                    LEFT JOIN Dept_Master
                    ON Employee.Dept_Code = Dept_Master.Dept_Code
                    LEFT JOIN Category_Master
                    ON Employee.Cat_Code = Category_Master.Cat_Code;

                    when i am executing like this : select * from vwEmpMain;
                    getting error table or view does not exist.

                    but when i am executing like this: select * from "vwEmpMain";
                    getting value.k

                    why this difference??

                    thanks
                    • 7. Re: How to execute view by sql developer?
                      Paul  Horth
                      Because you created the view as "vwEmpMain"

                      By default, if you don't use quotes, Oracle will store away the name of the view (or table or whatever) in uppercase
                      and will automatically convert it to uppercase when you refer to it without quotes. Therefore it will find it again.

                      If you create it using quotes Oracle keeps the exact upper and lower case name you specified BUT you have to refer to it in
                      EXACTLY the same way when you refer to it.

                      My advice, do not use quotes when creating:

                      create view vwEmpMain
                      ...

                      Then you can refer to it
                      as
                      select * from vwEmpMain
                      select * from VWEMPMAIN
                      select * from vwempmain
                      select * from VwEmPmAiN

                      it won't matter.
                      • 8. Re: How to execute view by sql developer?
                        955649
                        Thank you sir

                        it working fine.

                        thanks