execute immediate

pts.
Tags:
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • NOCOPY
    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.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following