Geek asks and answers

How to check if an Oracle view exists in the database? Before executing the request

I need to know how to test the Java Desktop application if the Oracle view exists in the current database before executing the query, otherwise I will get a lot of trouble ...

early

+3
source share
7 answers

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

    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;
}

It works!:)

+1
source

You can always query the Oracle data dictionary. Sort of

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

will tell you if you have access to the view belonging to the specified user with the specified name.

Java . DatabaseMetaData Connection getTables, , . getTables ( ), .

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

:

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

+1

, , , view VALID INVALID select all_objects

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

, , , , , .

0
source

You can use user_views for all views that belong to you or ALL_VIEWS, for all views that you have access to. I would use all_views

SELECT COUNT(*)
FROM ALL_VIEWS
WHERE VIEW_NAME = '[YOUR VIEW NAME']
0
source

Just request it. If this does not exist or your session does not have the necessary privileges, Oracle will raise a suitable exception.

0
source

More articles:


All Articles