Create Table DDL With Execute Immediate

There are two ways of writing a Create table DDL query for Execute Immediate statement of Native Dynamic SQL in Oracle Database. We have already discussed one of them in the last tutorial. The first way which we discussed in the previous tutorial is probably the easiest way of writing a SQL query for Dynamic SQL. But that doesn’t stop us from learning further. So let’s move ahead and learn the second one.

So in this tutorial we will learn the second method of writing a CREATE TABLE DDL for Dynamic Execution using Execute Immediate Statement.

As this tutorial is in continuation of the previous one thus I request you to please go through that blog first for a better understanding. That being said, let’s start the tutorial.

Step1: Prepare the CREATE TABLE DDL.

Prepare your SQL query beforehand which you want to execute dynamically. This will reduce the code confusion & also minimize the syntactical error.

CREATE TABLE tut_83 (
                tut_num     NUMBER(5),
                tut_name    VARCHAR2 (50),
                CONSTRAINT cons1_col1_pid_pk PRIMARY KEY (tut_num)
                )

Here is our CREATE TABLE DDL. Except from the changed name and an added primary key constraint this DDL is pretty much similar to the one we created in the last tutorial.

Step 2: Write the PL/SQL Block for Dynamic Execution of the DDL statement.

Now that we have our CREATE TABLE DDL prepared. Let’s write the PL/SQL program for executing it dynamically.

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

Let’s see what we did in this program.

The Declaration Section.

DECLARE
    ddl_qry     VARCHAR2 (150);

In the declaration section of our PL/SQL block we declared a variable with the name ddl_qry. This variable will be holding our CREATE TABLE DDL which we will be running using the Execute Immediate statement.

As per the last tutorial Execute Immediate treats all the SQL statements as a string of VARCHAR2 data type. That is why we had declared our variable ddl_qry as VARCHAR2 data type.

The Execution Section.

Right after the declaration section we have our execution section where all the fun is happening.

BEGIN
    ddl_qry :=  'CREATE TABLE tut_83 ('||
                    'tut_num     NUMBER (5),'||
                    'tut_name    VARCHAR2 (50),'||
                    'CONSTRAINT cons1_col1_pk PRIMARY KEY (tut_num)'||
                ')';
    EXECUTE IMMEDIATE ddl_qry;
END;
/

This execution section consists of only two statements:

1. Assignment statement
2. Execute Immediate statement

The first statement which is the “Assignment Statement” makes this code different from the previous one. Let’s see what these statements are.

Statement 1: Assignment Statement.

ddl_qry :=  'CREATE TABLE tut_83 ('||
                    'tut_num     NUMBER (5),'||
                    'tut_name    VARCHAR2 (50),'||
                    'CONSTRAINT cons1_col1_pk PRIMARY KEY (tut_num)'||
                ')';

This is the only statement which is making this PL/SQL program different from the one which we saw in the previous tutorial.

In this statement we are assigning the CREATE TABLE DDL to the variable ddl_qry. So what is different here?

In the previous method we enclosed our whole DDL statement into a pair of Single Quotes ( ‘ ’ ). Like this

ddl_qry:= 'CREATE TABLE tut_82 (
                tut_num     NUMBER(3),
               	tut_name    VARCHAR2 (50)
               )';

First way of writing a DDL query

 

Whereas here in the second method instead of wrapping the whole query into the single quotes we first divided it into multiple strings of VARCHAR2. This we did by wrapping every single line of the query into single quotes. Thereafter we joined them all using concatenate operator ( || ) so that PL/SQL engine will execute them as a single unit.

You can watch a detailed demonstration of this break and conquer approach for dynamic execution in the video tutorial.

Suggested reading: Concat Function Vs Concat Operator.

Statement 2: Execute Immediate Statement.

Our second statement is the Execute Immediate statement. It executes any SQL statement that returns single row of the result dynamically. In our case this statement is executing a CREATE TABLE DDL query through a PL/SQL block.

There is only two ways of executing a DDL statement through a PL/SQL block in Oracle Database. Either by using DBMS_SQL package or Execute Immediate of Native Dynamic SQL. Read here to know what else you can do using Execute Immediate.

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.

Check if the table is created or not.

There are multiple ways of checking whether the PL/SQL block executed the CREATE TABLE DDL successfully or not. But here I am going to show you the two easiest and popular ways.

Using DESCRIBE Command

The easiest way of checking whether the PL/SQL block has created the table tut_83 successfully or not, is by using DESCRIBE Command. Describe command shows the structure of the mentioned table, only if it exists otherwise it returns an error.

Let’s try that

DESCRIBE tut_83;
Or 
DESC tut_83

If on execution of the above describe command you see the structure of the table tut_83 then it means the above PL/SQL block has successfully created the table. But in case if you get an error that means you messed up somewhere and the execution of the PL/SQL block is not successful.

Using ALL_OBJECTS data dictionary.

You can also query ALL_OBJECTS data dictionary. This helps to find out whether the desired table or any other object you are trying to create is created or not. Like this

SELECT 'We created a table with name '||object_name||' in '
        ||owner||' schema on '||created
FROM all_objects WHERE object_name ='TUT_83';

These are two among many ways for finding out whether the desired object is created successfully.

One more thing before winding up this tutorial. Please do read the previous blog for the better understanding of this as well as the upcoming tutorials.

That is the PL/SQL tutorial on the second way of writing a CREATE TABLE DDL for Execute Immediate statement of Native Dynamic SQL in Oracle Database.

Hope you enjoyed reading. Do make sure to subscribe to the YouTube channel. Because in the next tutorial we will be learning how to drop and modify schema objects dynamically.

Thanks and have a great day!