I just learned about the EXECUTE IMMEDIATE command.

It's very interesting so I thought I'd share the info...

-It's used in 8i as a replacement to the DBMS_SQL package...
-Uses something called "NATIVE DYNAMIC SQL"
-It helps compact code and make it easier to read and maintain.
-Executes faster. (code is in the PL\SQL engine) where as calls to DBMS_SQL must
"context switch between SQL and PL\SQL engines..."
-According to Oracle executes 30-60% faster...

Here is an example...

l_sql:='insert into mytable values(:col1,:col2)';
--Step 1 open cursor
csr_handle:=DBMS_SQL.OPEN_CURSOR();
--Step 2 Parse!
DBMS_SQL.PARSE(csr_handle,l_sql,DBMS_SQL.NATIVE);
--Step 3
-Step 3: Bind variables
DBMS_SQL.BIND_VARIABLE(csr_handle,':col1',col1value);
DBMS_SQL.BIND_VARIABLE(csr_handle,':col2',col2value);
-Step 4: Execute
DBMS_SQL.EXECUTE(csr_handle);
-Step 5: Close
DBMS_SQL.CLOSE_CURSOR(csr_handle);


This can be rewritten as...

l_sql:='insert into mytable values(:col1,:col2)';
EXECUTE IMMEDIATE l_sql USING col1value,col2value;

Cool huh!?

I plan to rewite all my stored procedures to use it...