How to check with if an Oracle's view exist in the DB ? Before execute a query

Question

I need to know a way to check from a Java Desktop App, if a Oracle's view exist in the current DB before execute a query otherwise I will get a lot of troubles...

thanks in advance

Solution 1

You can always query the Oracle data dictionary. Something like

SELECT COUNT(*)
  FROM all_views
 WHERE view_name = <<the name of the view>>
   AND owner     = <<the owner of the view>>

will tell you whether you have access to a view owned by the specified user with the specified name.

Alternately, you can use a more Java-centric approach. You can create a DatabaseMetaData object from your Connection and call getTables to get a list of all the tables and views that you have access to. You can pass getTables a specific table or view name (or a pattern) to restrict the results.

Solution 2

SELECT count(*)
FROM user_views
WHERE view_name = 'MY_VIEW'

More details in the manual:

http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_5499.htm#i1635848

Solution 3

In case you want see not only if view exist and if view enable for current user but if view VALID or INVALID you can use select from all_objects table

SELECT count(*)
FROM all_objects t
WHERE 
t.object_type = 'VIEW' 
and t.object_name = 'VIEW_NAME'
and t.status = 'VALID'

Solution 4

Thanks to everyone, finally I got a method that solves this issue, thanks for your suggestions, the code is the following:

    public boolean existViewInDB(String viewName) {
    logger.debug("[boolean existViewInDB(String viewName[" + viewName
        + "])]");
    boolean existView = false;
    try {
        String sql =
            "SELECT count(*) FROM user_views WHERE view_name = :viewName";
        SQLQuery query = getSession().createSQLQuery(sql);
        query.setString("viewName", viewName);
        BigDecimal totalOfViews = (BigDecimal) query.uniqueResult();
        existView = (totalOfViews.longValue() > 0);
    } catch (Exception e) {
        logger.error(e, e);
    }
    logger.debug("Exist View [" + viewName + "] ? -> " + existView);
    return existView;
}

This works! :)

View more solutions

Comments

[a_horse_with_no_name]

When using ALL_VIEWS you should also add a condition on the owner of the view. Otherwise you might get a "positive" result but still can't access the view without qualifying the owner.

[Jeffrey Kemp]

Only if the view is owned by the current schema.

[a_horse_with_no_name]

@JeffreyKemp: that's what most of the people want to see. At least the OP did not state that the view should be searched regardless of the owner. Btw: user_views shows the views owned by the current owner, not the current schema. Which - despite an owner an schema are very similar - is not the same thing.

[Jeffrey Kemp]

thanks, you're right, I should have said current owner. However, it's very common for applications to log in using an owner/schema that owns no objects, because of the security issues this entails, so all_views would make more sense for the stated requirement (query access).

[a_horse_with_no_name]

@JeffreyKemp: well, without MadMad666 clarifying how it's done in his/her application we'll never know

[vanchagreen]

Also the last line should be: WHERE VIEW_NAME = 'YOUR VIEW NAME HERE' No brackets needed.