As a DBA there are times when you need to determine if a table, view, sequence or any other object exists withing your database structure. Since there are a few versions of your software either in production or out in the field.
As an example lets say you have a database in the production and you are working on a new release. Programmers need changes are made to the database schema to support new requirements, such as adding a new column to a table or altering the length of a column. As a result you need to write update scripts to be run against the database currently in production. Since the changes are minor running updates on database is much better than rebuilding saving time and money.
Using PL/SQL blocks you can query the data dictionary to determine if the table exists. If it is already there simple issue the alter table command via DBMS_UTILITY.EXEC_DDL_STATEMENT to modify the table accordingly. If the table does not exist, create the table with the new table structure.
Here is an example of the code that checks to see if the column is NULLABLE, if it is not it sets the column as NULL.
CONNECT SYSTEM/sys+password — connect to the system
WHERE OWNER = ‘YOUR_SCHEMA_NAME’
AND TABLE_NAME = ‘YOUR_TABLE_NAME’
AND COLUMN_NAME = ‘YOUR_COLUMN_NAME’;
IF L_NULLABLE = ‘N’ THEN
DBMS_UTILITY.EXEC_DDL_STATEMENT(‘ALTER TABLE YOUR_SCHEMA_NAME.YOUR_TABLE_NAMEMODIFY (YOUR_COLUMN_NAME NULL)’);
This script checks for a column but you can just as easily check to a table (DBA_ALL_TABLES), a view (DBA_ALL_VIEWS) or a sequence (DBA_SEQUENCES).
As always you need to test, test, and test again to ensure the scripts are doing what you intend them to do. Especially when you will be running them against a production database.