Examples of Checking Table Existence in PL/SQL
Example 1: Using USER_TABLES Data Dictionary View
DECLARE
v_table_name VARCHAR2(50) := 'employees';
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM USER_TABLES
WHERE TABLE_NAME = v_table_name;
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' exists in the current schema.');
ELSE
DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' does not exist in the current schema.');
END IF;
END;
- v_table_name: This variable holds the name of the table we want to check for existence.
- v_count: This variable stores the count of tables with the specified name in the current schema.
- SELECT COUNT(*) INTO v_count: This SQL statement selects the count of tables with the specified name from the USER_TABLES data dictionary view.
- IF v_count > 0 THEN: This conditional statement checks if the count is greater than 0, indicating that the table exists.
- DBMS_OUTPUT.PUT_LINE: This procedure outputs a message indicating whether the table exists or not in the current schema.
Output Explanation:
This PL/SQL block declares a variable v_table_name to hold the name of the table to be checked.
It queries the USER_TABLES data dictionary view to count the number of tables with the specified name in the current schema. Based on the count, it prints a message indicating whether the table exists or not.
Example 2: Using DBA_TABLES Data Dictionary View (for privileged users)
DECLARE
v_table_name VARCHAR2(50) := 'employees';
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM DBA_TABLES
WHERE OWNER = USER AND TABLE_NAME = v_table_name;
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' exists in the database.');
ELSE
DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' does not exist in the database.');
END IF;
END;
- v_table_name: Same as in the first approach, this variable holds the name of the table we want to check for existence.
- v_count: Same as in the first approach, this variable stores the count of tables with the specified name.
- SELECT COUNT(*) INTO v_count: Similar to the first approach, this SQL statement selects the count of tables with the specified name, but from the DBA_TABLES data dictionary view.
- WHERE OWNER = USER AND TABLE_NAME = v_table_name: This condition ensures that the table belongs to the current user/schema.
- IF v_count > 0 THEN: Same as in the first approach, this conditional statement checks if the count is greater than 0, indicating that the table exists.
- DBMS_OUTPUT.PUT_LINE: Similar to the first approach, this procedure outputs a message indicating whether the table exists or not in the entire database.
Output Explanation:
This PL/SQL block is similar to Example 1 but queries the DBA_TABLES data dictionary view (which requires privileged access) to count the number of tables with the specified name in the entire database.
It prints a message indicating whether the table exists or not based on the count.
How to Check If a Table Exist in PL/SQL?
In PL/SQL (Procedural Language/Structured Query Language), it’s often necessary to determine whether a particular table exists in the database schema before attempting any operations on it. This article explores methods and techniques to check for the existence of a table in PL/SQL, providing examples and explanations for each approach.