This content has been marked as final. Show 5 replies
This isn't an exact answer to your question, but it may be helpful.
Execute Immediate is the preferred approach in 9i and above. If you are deciding which to use, go with EXECUTE IMMEDIATE. You never know when the other methods of doing dynamic SQL will be deprecated. Additionally, EXECUTE IMMEDIATE has also been optimized (and will continue to be optimized), so you can generally expect better performance when using it.
As far as the exact differences between EXECUTE IMMEDIATE and DBMS_UTILITY.EXEC_DDL_STATEMENT go, I cannot offer you an exact answer. (Besides the fact that DBMS_UTILITY.EXEC_DDL_STATEMENT is DDL only, of course).
You may or may not find this article helpful: http://okjsp.pe.kr/seq/9789. It does a good job of comparing some of the differences between EXECUTE IMMEDIATE and another legacy way of doing dynamic SQL, (DBMS_SQL.EXECUTE)
I've just recently become aware of DBMS_UTILITY.EXEC_DDL_STATEMENT and was wondering if it works in pretty much the same way as EXECUTE IMMEDIATEWell not quite: exec_ddl_statement has the nice advantage of being able to be called remotely, e.g.:
exec dbms_utility.exec_ddl_statement@remote_db('create table t (a integer)')
You can still use EXECUTE IMMEDIATE with bind variables. No need to avoid it for that reason.
DECLARE l_query VARCHAR2(4000); l_value NUMBER; l_binding_value NUMBER := 1; BEGIN l_query := 'SELECT my_column FROM my_table WHERE my_key= :bind_var_1 '; EXECUTE IMMEDIATE l_query INTO l_value USING IN l_binding_value; DBMS_OUTPUT.PUT_LINE('The result is: ' || l_value); END;
In case of Execute Immediate you can execute DDL/DML statements where as with EXEC_DDL_STATEMENT it is mainly used for the DDL statements. I just came across the following issues or disadvantages of Execute Immediate and they are
You cannot, pass a Boolean, or a NULL literal value, you can however pass a variable of the correct type that has a value of NULL. Execute Immediate cannot run queries whose length is greater than 32 KB.