This discussion is archived
8 Replies Latest reply: Oct 9, 2012 12:44 AM by Paul Horth RSS

How to execute view by sql developer?

955649 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you sir

    it working fine.

    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points