Skip to Main Content

Breadcrumb

May 4th

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

Is this answer out of date? If it is, please let us know via a Comment

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