alter and drop table ddl with execute immediate in oracle database by manish sharma

ALTER & DROP Table DDL

As of now, we have learnt all the possible ways of creating a table right inside a PL/SQL block using Native Dynamic SQL. You can check them in the last two tutorials that are tutorial 82 and tutorial 83.

Thanks, Dynamic SQL for giving us the power of executing DDL and DML statements in our PL/SQL block. Ok, calm down all you Stone Age people! I know that DBMS_SQL package is still there but in comparison to dynamic SQL I find it really complex, not to mention its sluggish performance. Anyways, so

What are we learning from this tutorial?

In this tutorial we will learn how to use ALTER & DROP table DDL with Execute Immediate statement of Native dynamic SQL. It means that by the end of this tutorial you will be capable of dynamically modifying and deleting a database object such as a table right through your PL/SQL program.

Why should we learn that?

Some may find the topic irrelevant, but it’s not rather it is a very important topic especially from Oracle Certification and Interview perspective. Also, being a developer we all must have our fundamentals clear because you never know when you will need it.

So, let’s start the tutorial, in case you still have questions feel free to message them on my Facebook, here. Let’s first learn how to modify a table using ALTER TABLE with Execute Immediate of Dynamic SQL.

How to dynamically modify a table?

In order to modify a database object such as a table we use ALTER DDL in Oracle Database. Modifying a table using ALTER DDL through SQL is easy as compared to PL/SQL.

PL/SQL does not support DDL, DML and DQL statements directly. In order to execute mentioned SQL statements we either need to use Dynamic SQL or DBMS_SQL package. Among these two, Native Dynamic SQL (NDS) is the most viable option because of its simplicity and optimized performance. You can read the advantages of NDS over DBMS_SQL package here.

So, the simplest way of dynamically modifying a database object like a table is to use the Alter Table DDL with Execute Immediate statement of Native Dynamic SQL.

Example: Alter Table DDL with Execute Immediate statement.

For the demonstration we will use the same table which we created in the last tutorial. There we created a table with the name tut_83 that had two columns tut_num of NUMBER data type and tut_name of varchar 2 data type.

If you are practicing with me then you must have that table created in your schema. If it is so, then you are all set to go otherwise visit the last tutorial and create that table.

Now let’s say we want to add another column tut_date of date datatype to this table and that too using native dynamic SQL. In order to do that we need to use the ALTER TABLE DDL with Execute Immediate statement.

Step 1: Prepare the DDL statement.

The best practice for writing a Dynamic SQL program is to prepare your SQL statement beforehand.

ALTER TABLE tut_83 ADD tut_date DATE

Here is the ALTER TABLE DDL which we will be using for dynamic execution. This statement will add the column tut_date of DATE data type to the table tut_83.

You can also watch the video tutorial for live demonstration of the concept.

Step 2: Write the Native Dynamic SQL program.

Once you have your DDL statement ready then you are all set to write the PL/SQL block.

SET SERVEROUTPUT ON;
DECLARE
 ddl_qry VARCHAR2(50);
BEGIN
 ddl_qry := 'ALTER TABLE tut_83
        	 ADD  tut_date DATE';
 EXECUTE IMMEDIATE ddl_qry;
END;
/  

On successful execution this PL/SQL program will modify the above mentioned table. You can check the modified structure of your table by using DESCRIBE command, like this

DESCRIBE tut_83;

OR

DESC tut_83;

That is all you have to do. That is how we modify a database object dynamically using ALTER TABLE DDL with Execute Immediate statement. That also concludes the first part of the tutorial now let’s move on to the second and learn how to delete a table using native dynamic SQL.

How to dynamically delete a table?

To delete a database object like a table we use DROP DDL in Oracle Database. Like any other DDL statement this one is also not directly supported by the PL/SQL engine. And as usual we need to use Dynamic SQL to run this DDL also. Let’s see how to delete a table using Execute Immediate of Native Dynamic SQL.

Step 1: Prepare the DDL statement.

The DDL for deleting a table is:

DROP TABLE tut_83

This DDL will delete the table tut_83 from the schema.

Step 2: Write the PL/SQL program for DROP table with Execute Immediate.

SET SERVEROUTPUT ON;
DECLARE
    ddl_qry     VARCHAR2 (100);
BEGIN
    ddl_qry := 'DROP TABLE tut_83';
    EXECUTE IMMEDIATE ddl_qry;
END;
/

So successful execution of this program should drop the table tut_83 which you can easily check by using DESCRIBE command as shown above.  

That is the tutorial on how to modify and delete a database object using Alter & Drop table DDL with Execute Immediate statement of Native Dynamic SQL in Oracle Database. Hope you enjoyed reading it and learnt something new. Please do share this blog on your social media with your friends. You can also connect with me on my Facebook Page for more interesting insights. Thanks & have a great day!