Menu
Which are the differences between DBMS_UTILITY.EXEC_DDL_STATEMENT and EXECUTE IMMEDIATE?
312
Native dynamic SQL using the EXECUTE IMMEDIATE and OPEN-FOR statements is faster and requires less coding than the DBMS_SQL package.
The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance.
You can put an EXECUTE IMMEDIATE statement with the RETURNING BULK COLLECT INTO inside a FORALL statement. You can store the results of all the INSERT , UPDATE , or DELETE statements in a set of collections. You can pass subscripted collection elements to the EXECUTE IMMEDIATE statement through the USING clause.
Notice the USING clause in the EXECUTE IMMEDIATE to bind the values to the bind variables in the string. Try this, execute immediate ('begin '||oprocstring||' end;'); In other words, wrap the string with a begin/end and it should work.
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.
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
