Oracle: DBMS_UTILITY.EXEC_DDL_STATEMENT vs EXECUTE IMMEDIATE

Which are the differences between DBMS_UTILITY.EXEC_DDL_STATEMENT and EXECUTE IMMEDIATE?

Answer

Fundamentally they do the same thing, which is to provide a mechanism to execute DDL statements in PL/SQL, which isn’t supported natively. If memory serves me well, the EXEC_DDL_STATEMENT was available in the Oracle 7 version of the DBMS_UTILITY package, whereas Native Dynamic SQL (EXECUTE IMMEDIATE) was only introduced in 8.

There are a couple of differences. EXECUTE IMMEDIATE is mainly about executing dynamic SQL (as its NDS alias indicates). the fact that we can use it for DDL is by-the-by. Whereas EXEC_DDL_STATEMENT() – as the suggests – can only execute DDL.

But the DBMS_UTILITY version isn’t retained just for backwards compatibility, it has one neat trick we cannot do with EXECUTE IMMEDIATE – running DDL in a distributed fashion. We can run this statement from our local database to create a table on a remote database (providing our user has the necessary privileges there):

SQL>  exec DBMS_UTILITY.EXEC_DDL_STATEMENT@remote_db('create table t1 (id number)');

I’m not recommending this, just saying it can be done.