Run a DDL Command in a PLSQL Block

We always have queries like how do we run DDL commands in a PLSQL block. Here is the answer to this question.

Examples of DDL commands:-

create table xx_mytable
drop table xx_mytable

Above are the 2 examples of DDL. If you put these 2 commands directly in a PLSQL block it wont work.

How do we do it ?

use EXECUTE IMMEDIATE

i.e.


DECLARE
l_table_name VARCHAR2(100);
BEGIN
l_table_name := 'CREATE TABLE XX_MYTABLE (name varchar2(10), id number)';

EXECUTE IMMEDIATE (l_table_name);

exception when others then

dbms_output.put_line('Err :: '|| SQLERRM);

END;