EXISTS function in Oracle

There is a very simple information, and nothing greater, but still this could be useful and can save you and your time when you need it.


People who have worked in Transact-SQL in SQL Server will feel the importance of missing EXISTS function when working on Oracle or any other databases.  Using EXISTS function, we can check if a table exists in database, column exists in table, or row or value exists in a table.  The EXISTS can also be used as a logical operator in WHERE clause.


We had a requirement to check the existence of table before we attempt to create a table in an Oracle database.  I did spend some time to find any similar methods in Oracle, but it was not successful.  Then I tried a few approaches, some worked and some did not; some threw syntax errors.  I am providing the solution which I found to be useful and functional and if you are going to have any such or similar requirements in future, this information can be useful.


One of the simple ways to achieve this is to check the system table where metadata for all database objects is stored.  In Oracle, we can get this metadata from ALL_OBJECTS table.  Since metadata for objects of types TABLE, VIEW, FUNCTION, INDEX, TRIGGER etc are stored here, to check the existence of a table we will have to have to filter out the data based on the OBJECT_TYPE column of ALL_OBJECTS table.  Otherwise, if there is any other object than the type we want, the result can be misleading.


For example, to retrieve the metadata for a table STUDENT, we can query the ALL_OBJECTS as below:


SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'STUDENT' AND OBJECT_TYPE = 'TABLE'


Now, putting this query into our work as below, unfortunately, will not work, but will throw syntax error:


IF ((SELECT COUNT(*) FROM ALL_OBJECTS WHERE LOWER(OBJECT_NAME) = LOWER('TableName') AND OBJECT_TYPE = 'TABLE') > 0)
//CREATE TABLE SCRIPT HERE
END IF;


In SQL Server, the above statement will work without any error as this is a valid T-SQL statement, but not in Oracle.  The better and easy solution is to write a function which returns TRUE or FALSE.  I wrote the following function and it served my purpose:


CREATE OR REPLACE
FUNCTION IsTableExists(TableName IN VARCHAR2) RETURN BOOLEAN
AS
      record_count INTEGER;
      result_boolean BOOLEAN;
BEGIN
      SELECT COUNT(*) INTO record_count FROM ALL_OBJECTS
      WHERE LOWER(OBJECT_NAME) = LOWER(TableName)
      AND OBJECT_TYPE = 'TABLE');
      IF record_count = 1 THEN
        result_boolean := TRUE;
      ELSE
        result_boolean := FALSE;
      END IF;
      RETURN result_boolean;
END;


Similar to this, you can check the existence of column in a table from USER_TAB_COLUMNS table and existence of constraint from USER_CONSTRAINTS table.  With the metadata stored in USER_TAB_COLUMNS table, we can conclude data types for columns, which columns are nullable, column length, etc.