Re: Probably Simple Question - Execute immediate

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 18 May 2001 11:51:40 -0400
Message-ID: <00hagtcgp5jk18232qli6bkm4pnabl6nl4@4ax.com>

A copy of this was sent to John Doe <zuestra_at_hotmail.com> (if that email address didn't require changing) On Thu, 17 May 2001 21:02:03 -0700, you wrote:

>Oracle, Sybase, Ingres & Informix all have an "EXECUTE IMMEDIATE" command with
>slight variations. I get the idea, we immediately parse and excute some dynamic
>SQL, PL/SQL block etc., Makes sense to me. But, do I have a choice? I can't
>just use "EXECUTE" can I? The only way I can think of to *not* parse and
>immediately execute a piece of dynamic SQL is to use dbms_sql and then I can
>parse and then execute a half hour later if I want. In other words, an
>"EXECUTE IMMEDIATE" command syntactically implies to me that there is an EXECUTE
>command with a few variations - like maybe EXECUTE DEFFERED, EXECUTE
>SOMEOTHERTIME.. etc., but they don't seem to exist. Some it seems to be a two
>word command with no variations - is this correct? For example, I can't alter
>database datafile drop.. I can only offline drop, or offline.. So.. after this
>meandering, I guess my question is are there any variations to this syntax? Is
>it faster then dbms_sql.parse dbms_sql.execute etc.,, ?
>
>Little confused
>Thanks,
>D

In higher level languages, using a precompiler like Pro*C for example, there is an EXECUTE.

An execute immediate consists of the following steps:

     open cursor
     parse statement
     bind inputs
     execute
     retrieve outputs
     close

execute immediate is shorthand for all of the above. An execute allows us to

open cursor
parse statement

once, and then later

    bind inputs
    execute
    retrieve outputs

many times, over and over -- skipping the open and parse steps. Later, we'll just

close cursor

In most cases, EXECUTE IMMEDIATE in plsql is faster then the corresponding steps in DBMS_SQL -- however, if you find yourself coding:

    for .....
    LOOP
        execute immediate 'some statement' using ....;     END LOOP; you should consider using DBMS_SQL as there you can:

    dbms_sql.open-cursor
    dbms_sql.parse
    for ....
    LOOP

        dbms_sql.bind_variable
        dbms_sql.execute

END LOOP;
dbms_sql.close

this will increase both performance (in single user mode) AND scalability in multi-user mode. That parse inside the loop used by NDS can be expensive.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Fri May 18 2001 - 10:51:40 CDT

Re: Probably Simple Question - Execute immediate - confused by sytax