Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Balakrishna.

Asked: July 19, 2016 - 4:02 pm UTC

Last updated: July 20, 2016 - 8:38 am UTC

Version: 12c

Viewed 1000+ times

You Asked

HI TOM,

1)exactly in which situation we need to use these concepts in dynamic sql.
i)"dbms_sql package" in dynamic sql
ii)"Native dynamic sql" in dynamic sql ....
.... show me with an example what is the vary in both the cases?

2) what is difference between "Native dynamic sql" & "dbms_sql" package in dynamic sql?

and Chris said...

The docs cover this well:

Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL package. You must also use the DBMS_SQL package if you want a stored subprogram to return a query result implicitly (not through an OUT REF CURSOR parameter).

http://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS011

The link also has further explanation of the differences, restrictions and examples.

Steven Feuerstein also has a couple of great articles on this:

http://www.oracle.com/technetwork/issue-archive/o64sql-095035.html
http://www.oracle.com/technetwork/issue-archive/2015/15-may/o35plsql-2541606.html

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library