Oracle: DBMS_UTILITY.EXEC_DDL_STATEMENT vs EXECUTE IMMEDIATE

Question

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

Solution 1

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.

Solution 2

I realize I am 9 years late to the reply but there is one additional difference.

dbms_utility.exec_ddl_statement will not execute anything but DDL. If you try to do say an insert, it will not do it. It will also not return an error either so you won't know that you did not insert.

-- drop table kevtemp1;

create table kevtemp1 (a integer);

insert into kevtemp1 values (1);
commit;

begin
    insert into kevtemp1 values (2);
end;
/
commit;

begin
   DBMS_UTILITY.EXEC_DDL_STATEMENT('insert into kevtemp1 values (3)');
end;
/
commit;


select * from kevtemp1;

Comments

[APC]

Not sure why somebody voted to close this as "off topic". It's a question about programming languages, what's off topic about that?