Disable Table Names Starting with TMP or BAK in Oracle Database

In certain scenarios, you may want to enforce a naming convention in your Oracle Database to prevent tables from being created with names starting with specific prefixes, such as TMP_ or BAK_. This article demonstrates how to implement a database trigger to achieve this.

Initial Setup

Let’s begin by creating a table with a name that starts with TMP_ as an example:

CREATE TABLE tmp_test (fname VARCHAR2(20));

Creating the Trigger

After enabling the trigger shown below, no tables can be created in the database with names starting with TMP_ or BAK_:

CREATE OR REPLACE TRIGGER NO_TMP_TABS_TRIG
BEFORE CREATE
ON DATABASE

DECLARE
    x USER_TABLES.TABLE_NAME%TYPE;
BEGIN
    SELECT ora_dict_obj_name
    INTO x
    FROM DUAL;

    IF SUBSTR(x, 0, 4) = 'TMP_' OR SUBSTR(x, 0, 4) = 'BAK_' THEN
        RAISE_APPLICATION_ERROR(-20099, 'TABLE NAMES CANNOT START WITH TMP% OR BAK%');
    END IF;
END NO_TMP_TABS_TRIG;

This trigger examines the name of the table being created and raises an error if the table name starts with TMP_ or BAK_.

Testing the Trigger

Let’s test the trigger by attempting to create a table with a name starting with TMP_:

CREATE TABLE tmp_test (fname VARCHAR2(20));

Error Output

The trigger prevents the table from being created and raises the following error:

Error starting at line : 15 in command –
CREATE TABLE tmp_test (fname VARCHAR2(20))
Error at Command Line : 15 Column : 1
Error report –
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20099: TABLE NAMES CANNOT START WITH TMP% OR BAK%.

Conclusion

By implementing the NO_TMP_TABS_TRIG trigger, you can enforce naming conventions in your Oracle Database, ensuring that no tables are created with names starting with specific prefixes. This can help maintain consistency and prevent the accidental creation of temporary or backup tables with undesired names.