Oracle: If Table Exists

Oracle: If Table Exists

You can check if a table exists in Oracle by querying the data dictionary. The data dictionary is a set of tables and views that provide information about the database schema and objects. You can use the "ALL_TABLES" view to check if a table exists by querying it with a specified table name.

Here is an example of how you can check if a table named "MY_TABLE" exists in the current schema: 

FROM all_tables
WHERE table_name = 'MY_TABLE'

The query will return a count of 1 if the table exists and 0 if it does not.
You can also use the "DBA_TABLES" view to check if a table exists in any schema in the database:

WHERE table_name = 'MY_TABLE'

You can also use PL/SQL block to check table exist or not:

    l_count NUMBER;
    SELECT COUNT(*) INTO l_count FROM all_tables WHERE table_name = 'MY_TABLE';
    IF l_count > 0 THEN
        -- Table exists
        -- Table does not exist
    END IF;

Please note that you need the appropriate privileges to query the data dictionary views. Also, when checking for table existence, keep in mind to use upper case letters while checking table names in the query, as table names are stored in upper case in data dictionary views.

Posting Komentar

0 Komentar