How to dynamically execute DDL statements using EXECUTE IMMEDIATE

Introduction:

   Ø EXECUTE IMMEDIATE statement is used execute the DDL, DML statements at runtime in PL/SQL.

Example:

   Ø In this example we will know about how to create a table in PL/SQL block.

   Ø In SQL we have no trouble to create a table, but in PL/SQL we are unable to create table same as SQL, let show the Example,

Create the table in the PL/SQL Block:

1. Begin

    Create table std_rec( std_id number

                          std_name varchar2(100)

                          std_standard number);

   End;

If you run the block we will get this error,

Error report:

To overcome this error we will use EXECUTE IMMEDIATE:

DECLARE

   v_qry   VARCHAR2 (200);

1. BEGIN

      v_qry :=

         'Create table std_rec (std_id number,

                          std_name varchar2(100),

                          std_standard number)';

    

      EXECUTE IMMEDIATE v_qry;

   END;

 Note:

 Ø Data type of the variable must be VARCHAR.

Ex: v_qry   VARCHAR2 (200);

  Ø Create query must be enclosed with single quotes(‘’) and ended with semi colon(;).

 Output:

PL/SQL procedure successfully completed.

Describe the Table:

Share: