Create Table DDL With Execute Immediate

You must be thinking that what could be the trouble in executing a CREATE TABLE DDL? Right, no trouble as long as you are executing them with SQL. But when it comes to executing them with PL/SQL then it’s kind of tough.

So first, what are DDL statements in Oracle Database?

DDL stands for Data Definition Language. Using DDL statements we can create or modify the database objects as well as the schemas.  These statements are:

• CREATE,
• DROP and
• ALTER.

It is always a good practice to layout your schemas and objects beforehand and create them in the initial stage of your project. If possible then we should do this work using SQL. That could be much faster and way cleaner. But sometimes it is not always possible, in that situation we can take help of features like Native Dynamic SQL in Oracle Database.

Native Dynamic SQL enables us to execute SQL statements like DDL or DML right through our PL/SQL.

Why can’t we execute DDL through a PL/SQL program?

If you are still wondering if executing DDL statement with PL/SQL is that complicated, then let’s try creating a table through a PL/SQL program.

SET SERVEROUTPUT ON;
BEGIN 
    CREATE TABLE tut_82 (
    tut_num NUMBER(3),
    tut_name   VARCHAR2 (50));  
END;
/

I know this approach would have been your first thought if I had asked you to create a table through a PL/SQL program. I mean, this looks perfect, what could possibly go wrong?

There is nothing wrong with this program, the only problem here is that PL/SQL does not directly support DDL statements. To execute a DDL statement using PL/SQL we either need to take help of DBMS_SQL package or the advanced and improved way of the NATIVE DYNAMIC SQL.

You can check this out for yourself. Go ahead and execute the above program. On execution you will get an error which looks something like the one shown in the picture below.

So, what is the proper way of executing a DDL through PL/SQL program?

You can execute a DDL through PL/SQL program either by using DBMS_SQL package or by using the Execute Immediate statement of Native Dynamic SQL. The latter option is the most used one, specially now a days because of its better performance and easy to learn syntax.

How to execute CREATE TABLE DDL using Execute Immediate in Oracle Database?

Step 1: Prepare your DDL beforehand.

Though it is not necessary, but I suggest you to prepare your DDL beforehand, that way you can at least eliminate the chances of getting syntactic error because of the syntax of your DDL statement.

In our case, we want to execute a CREATE TABLE DDL so let’s write a DDL for creating a table first.

CREATE TABLE tut_82 (
    tut_num    NUMBER (3),
    tut_name   VARCHAR2 (50)
) 

This DDL statement will create a table with the name tut_82 with two columns tut_num of NUMBER data type and tut_name of VARCHAR2 data type.

Mind here, do not end your SQL statement (the one which you want to run with EXECUTE IMMEDIATE) with a semicolon.

Step 2: Run your DDL through PL/SQL program using Execute Immediate.

Once you have your DDL prepared next you have to write your PL/SQL program.

SET SERVEROUTPUT ON;
DECLARE
    ddl_qry     VARCHAR2 (150);
BEGIN
    ddl_qry := 'CREATE TABLE tut_82(
                tut_num     NUMBER(3),
                tut_name    VARCHAR2(50)
                )';
    EXECUTE IMMEDIATE ddl_qry;
END;
/

In the above PL/SQL block we declared a variable with the name ddl_qry. We used this variable for holding our DDL statement which we prepared in the step 1.  

Info:
One thing which you have to make sure here is that, the variable which you will be using for holding the SQL statement which you want to run with your EXECUTE IMMEDIATE statement must always be of VARCHAR2 data type and have plenty of data width so that your DDL could easily fit into it.

In the execution section of this PL/SQL block we have two executable statements. These statements are:

Statement 1: Assignment statement

In the first statement we have stored our Create Table DDL into the variable ddl_qry using the assignment operator (In PL/SQL the assignment operator is the combination of colons and equal to sign).

Statement 2: The Mighty Execute Immediate statement

The Second statement is the Execute Statement. To execute a DDL dynamically, you first have to write the reserved phrase ‘Execute Immediate’ followed by the variable name into which you have stored your DDL as we did above.

If you want you can also write your DDL directly after the phrase ‘Reserved Phrase’. It is completely a personal choice. I like to use the former approach where we use Variable for holding the DDL  because first of all it makes your code looks neat and clean moreover it also makes the error tracing a lot easier.

But Manish, I want to learn the other way of writing the DDL statement as well.

Sure, there is nothing wrong in learning something new.

BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE tut_82(
                	tut_num     NUMBER(3),
                	tut_name    VARCHAR2(50)
                )';
END;
/

The alternative approach to the above code is to write the DDL statement right within your Execute Immediate statement. For this you just have to first write the reserved phrase Execute Immediate followed by the DDL statement which you want to execute dynamically.

In either of the ways you just have to take care of a few things while writing the DDL for Execute Immediate.

First: Always enclose your SQL statement into a pair of Single Quotes

Always remember that Execute Immediate treats DDL or DML or any other supported SQL statements which you want to execute dynamically as a string of VARCHAR2 data type and in PL/SQL we enclose any character or VARCHAR2 string into a pair of single quotes. Thus always make sure to enclose your SQL statement which you want run using Execute Immediate into a pair of Single quotes.

Second: Take care of Semi-colon.

In case you are writing a SQL statement for Execute Immediate then you must put the semicolon (;) right outside the single quotes into which you enclosed your SQL statement. On the other hand if you are writing a PL/SQL block for dynamic execution using Execute Immediate then you must put the semicolon at the end of your PL/SQL block right before the closing single quote as well as right outside the closing single quotes.

That is the detailed tutorial on how to run Create Table DDL statement using EXECUTE IMMEDIATE statement of Native Dynamic SQL in Oracle Database. Hope you enjoyed it and learnt something new.

Please make sure to Subscribe to my YouTube channel as in the next tutorial we will learn something new and interesting about the Dynamic SQL. You can also follow me on my Twitter and Facebook.

Thanks and have a great day!