Home > IT Answers > Development > execute immediate
Paulina
0 pts.
 execute immediate
Architecture/Design, SQL
Can anyone remind me what EXECUTE IMMEDIATE does, and what are the pros and cons of using it versus regular dynamic SQL refcursor fetch. Thanks.

Software/Hardware used:
ASKED: June 3, 2004  12:59 PM
UPDATED: June 22, 2004  10:21 AM
RELATED QUESTIONS
Answer Wiki:
Oracle: EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package. Note: EXECUTE IMMEDIATE will not commit a DML transaction carried out and an explicit commit should be done.
Last Wiki Answer Submitted:  June 3, 2004  1:50 pm  by  Galvtx   0 pts.
All Answer Wiki Contributors:  Galvtx   0 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Some additional info:
You have to use refcursor fetches for multirow queries.
Execute immediate is for single row queries or multirow dml.
Native dynamic sql cannot be used for statements more than 32K in length (varchar2 limitation), the dbms_sql can parse statements assembled in dbms_sql’s varchar2s array type. If you are into huge sql statements you still have to use dbms_sql. I use dbms_sql.varchar2s to create some complex views that may excedd 32K in length.

NOCOPY
 0 pts.