What is a DDL Script, and why it is useful

This article explains what DDL scripts are and how to use them. The script can contain various PL/SQL DDL commands used to create database objects and establish the relations between them. The abbreviation of the DDL statement is Data Definition Language. We can perform the following tasks using DDL commands.

1. Create, alter and drop the schema or database objects.
2. Grant and revoke the access of the user on all or specific database objects and schema.
3. Configure the database auditing.
4. Analyze the information of the index, table, and cluster.

Now, let’s go over the list of DDL commands.

Benefits of using DDL Scripts

The DDL scripts have the following benefits.

1. The execution of the script can be automated.
2. The scripts can be useful when you want to create an empty database whose schema structure is identical to the existing database. You can generate the script of the objects, rename the objects and execute the script.
3. The scripts are handy if you drop an object containing no data. For example, if you accidentally drop any index, view, stored procedure, or function. If the script of the dropped object is available, you can easily recreate the dropped object.
4. The scripts of the database objects can be used in database migration. For example, if you migrate the database objects from Oracle to SQL Server, you can generate the DDL script of the objects, make the required changes and execute it on the SQL Server database.
5. DDL Scripts are reusable. The script is used to alter a specific object, and the same script can be used to alter a different object. All you have to do is change the object names in the script. It is much more convenient than using GUI tools.

Things to be considered while creating a DDL script.

The database management and development tools always help us create a script of any existing object. For example, Toad for oracle helps export the script for all databases. Those scripts can be used to create an empty or blank database. Not every company uses sophisticated database development and management tools. The DBAs and Developers must write a script to create DDL objects, so if you are writing a script to re-create any specific object or all objects of the database, you should consider the following items.

1. If you have automated the process to run the script, verify the access of the user who is running the script. The script execution will fail if the user who is executing the script does not have appropriate permission on the table/schema/database.
2. If you create the table using DDL script, make sure that tables are created in the proper sequence. For example, if you add a table with foreign keys, make sure you add a parent table to avoid errors.
3. When you are adding any constraint in a column, always check the column that exists on the table on which you are creating the constraint.
4. If you create a stored procedure/triggers/functions using DDL Script, make sure that the table and views exist on the schema or the database. Similarly, if you move the table to a different tablespace, ensure it exists in the database.
5. If you change a data type of the large table using the DDL script, you can use export-import methods. First, you should create a separate table with a new datatype, import the data from the old table to the new table and then rename the old table with dummy name and change the name of the new table to the original name. For example, if you are changing the datatype of tblPatient, then you can use the following sequence.
   1. Create tblPatient_New table with the new datatype.
   2. Import data to the tblPatient_New(New Table) table from the tblPatient (Old table) table.
   3. Rename tblPatient to tblPatient_Old.
   4. Rename tblPatient_New to tblPatient.

Now, let’s understand how to generate a DDL script using the Toad for Oracle. To demonstrate, I have created a sample pluggable database named eltechDB. I have created a user named eltechapp that contains the required permission to export the DDL Script of any table.

The database contains the following objects.

Query

select object_type, count(object_type)"Count of object" from all_objects where owner='ELTECHAPP' group by object_type order by object_type desc;

Output

Let us see how to generate the DDL scripts using Toad for Oracle.

Generate DDL Scripts using Toad for Oracle.

Open the Toad for Oracle and configure the connection parameters as shown.

Once connected, Click on Database>Click on Export>Select Export DDL.

An export DDL tab opens. Here, we can choose to export the script of all database objects or specific database objects. To select the objects, click on Add (Image 1). A dialog box (Image 2) opens. We are exporting the script of all user tables of the ELTECHAPP schema, therefore, select Tables from the Load object type drop-down box and select ELTECHAPP from the schema drop-down box. Click on Load Rows.

All tables created in the ELTECHAPP schema will be loaded. We want to create one script that contains the DDL of all tables. Also, we want to open it in a SQL editor of Toad, so select Editor -as script option and click on execute button.

Once the process is executed successfully, you can view the DDL script of all tables in a query editor.

As you can see, the DDL script creates all tables created by the eltechapp user.

Summary

In this article, we learned about DDL statements and commands. We learned the following topics.

1. What the DDL commands are and their usage.
2. The benefits of using DDL script and best practices and points that should be taken care of while executing DDL script.
3. How to generate the DDL scripts of the database objects using the Toad for Oracle.

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.