native_dbms_sql1.htm ( to be edited )
  • Native Dynamic SQL : DBMS like oracle allows the developer to use a PL/SQL block containing dynamic native SQL . It is not EMBEDDED in any other objects, allowing the user to create a freelance function or procedure, which is only invoked to run.
  • DBMS_SQL : is a complied package, the user doesn't have the access of to address or a pointer . DBMS-SQL provides an interface to dynamic SQL.
In Dynamic SQL, EXECUTE IMMEDIATE  , is used to reduce overhead memory consumptions, and intend to be an alternate choice over  DBMS_SQL.
Dynamic SQL :


Native Dynamic SQL vs DBMS_SQL

Native Dynamic SQL DBMS_SQL
A freelance SQL within PL/SQL, which are easy to access Comes as a package, offers an interface to dynamic SQL
PL/SQL block has built in support for Dynamic SQL therefore more efficient than DBMS_SQL. DBMS_SQL uses a Procedural API so it is generally slower than Native Dynamic SQL.
Supports user defined types. Does not support user defined types.
Does not support Data Type specific to PL/SQL, Booleans,  
Supports FETCH INTO record types Does not support FETCH INTO record types
Since does not provide a "address /pointer" client site code can't be blended Supported in client side code.
Does not support bulk Dynamic SQL, but it can be faked by placing all statements in a PL/SQL block. Supports bulk Dynamic SQL.
Only supports Single row Updates/Deletes with RETURNING clause. Supports Single and Multiple row Updates/Deletes with RETURNING clause.
Does not support SQL statements bigger than 32K Does support SQL statements bigger than 32K
Parse required for every execution Parse once, execute many possible