5 Replies Latest reply: Oct 25, 2010 11:24 AM by 789895 RSS

    EXECUTE IMMEDIATE or DBMS_UTILITY.EXEC_DDL_STATEMENT

    KevinFitz
      Hi ,

      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 IMMEDIATE. I'm guessing that EXECUtE IMMEDIATE offers more flexibility when putting together the command that you want to execute but is that the only real difference? Any opinions gratefully accepted,

      regards,

      Kevin.
        • 1. Re: EXECUTE IMMEDIATE or DBMS_UTILITY.EXEC_DDL_STATEMENT
          571043
          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)

          Bob
          • 2. Re: EXECUTE IMMEDIATE or DBMS_UTILITY.EXEC_DDL_STATEMENT
            Corrado Labinaz
            Hi Kevin,

            I agree with rcdev.
            You should use EXECUTE IMMEDIATE.
            Revert to DBMS_UTILITY when
            1. need to use bind variables in you dynamic sql
            2. need to DESCRIBE the query (get columns list) without actually execute it

            Hope it helps,
            Corrado
            • 3. Re: EXECUTE IMMEDIATE or DBMS_UTILITY.EXEC_DDL_STATEMENT
              MichaelS
              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 IMMEDIATE
              Well 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)')
              • 4. Re: EXECUTE IMMEDIATE or DBMS_UTILITY.EXEC_DDL_STATEMENT
                571043
                Hey Corrado,

                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;
                • 5. Re: EXECUTE IMMEDIATE or DBMS_UTILITY.EXEC_DDL_STATEMENT
                  789895
                  Hi,

                  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.

                  cheers

                  VT