Oracle Training

Oracle DBA Training: Native Dynamic SQL, Embedded SQL and the DBMS_SQL API

sql codesDBMS_Sql API and native dynamic SQL codes.


There are many ways for a developer using PL/SQL to use the procedural language to execute SQL statements.  You can use native SQL vs. embedded SQL, or you can use an API.  You can opt for bulk or non-bulk.  You can use ref cursor as opposed to using implicit cursor.  And that is just some of it.  For a beginner, you need Oracle DBA training to find out which ones to use and which ones would slow down your applications or database.

What’s more, new variants and statements have been introduced into PL/SQL over the years, and some of the things that have worked for you before might not be the best solution now.  So if you are doing SQL using PL/SQL, what are the best ways to go about it?  You would need a full Oracle DBA Training to know all the best practices.

For today, let us focus on what embedded SQL, native dynamic SQL and DBMS_SQL API are.

Embedded SQL

Embedded SQL allows SQL statements within a PL/SQL statement.  You can do this with the following SQL statements:

  • Commit
  • Delete
  • Insert
  • lock table
  • merge
  • rollback
  • savepoint
  • select
  • set transaction
  • update

It is easy to use and gives rise to a PL/SQL identifier instead of an SQL placeholder.  An embedded SQL is dynamic SQL.

Native dynamic SQL

Native dynamic SQL, on the other hand, is implemented as a feature of PL/SQL.  You should use this when you have SQL statements that relate to contents that are not known until runtime.  The application would be creating a string for that particular statement and then submits it to your server.  With native dynamic SQL, you can create tables and other DDL commands not supported by PL/SQL, and it allows you to earmark objects that will only be available at runtime.

This method is generally slower than static SQL.  This is why you should avoid using native dynamic SQL unless you really have to.

Another reason for avoiding a lot of native dynamic SQL is that your code does not really undergo object validation and syntax checking before it is run.  So it might be riddled with errors and it will still compile and run.


On the other hand, the DBMS_SQL API gives you a platform to use dynamic SQL to parse DDL statements and DML statements using PL/SQL.  It is generally more powerful than native dynamic SQL in that it supports SQL statements that are bigger than 32 kilobytes. It can also perform some tasks that native dynamic SQL cannot.

Looking to learn more about native dynamic SQL, embedded SQL or DBMS_SQL API?  Call Four Cornerstone today and ask about our Oracle DBA Training.  Our team of expert Oracle-certified trainers will be able to teach you everything you need to know about PL/SQL, SQL and the best practices, as well as the worst practices in database administration.

What’s more, you can easily have a customized Oracle DBA training from one of the world’s best Oracle trainers!

Contact us now!

Photo courtesy of Oracle.

Cloud Blog

4 Ways To Benefit from…

One of the benefits you get when you work with cloud applications is that you often have quarterly updates that are packed with features. This...

Keep Reading

Artificial Intelligence

Data and Analytics: Cross the…

  Artificial intelligence is a manna sent from digital heaven. That’s how blessed your business can get if you immerse into the AI of things....

Keep Reading

Business Intelligence Blog

How Brand Names Survive in…

  The age of digital marketplace has made it possible for unknown and smaller companies to compete with better-known and well-established brands. Take for example...

Keep Reading

Artificial Intelligence

The Phenomenon That Is Artificial…

  Artificial intelligence is when a machine does cognitive functions that are more associated with humans, such as thinking, learning, problem solving, and reasoning. As...

Keep Reading


Digital Transformation in Banking: Shift…

  The start of a new decade is already shaping up to be exciting for financial services, especially for banks as they continue to compete...

Keep Reading

Business Intelligence Blog

Data Science and Its Economic…

It would seem that artificial intelligence is the focus of businesses that are looking to future proof their organizations and stay competitive. And why not?...

Keep Reading

Data Blog

Ensure Efficient Data Science and…

In an ever-expanding landscape called Internet of Things and the exploding development of artificial intelligence, we are bombarded with complex methods of integrating data science...

Keep Reading

Business Intelligence Blog

Cybersecurity: The Top 5 Expectations…

  If you think about it, cybersecurity is closely tied to human rights, privacy, freedom, and even basic safety. As a whole, we have become...

Keep Reading

Business Intelligence Blog

Tech Security Should Be Easy…

  IT professionals know that tech security is important in everything that they do. And that there are a lot of products, tools, innovations, and...

Keep Reading

Business Intelligence Blog

The Top 6 Success Stories…

Nearly nine out of 10 digital transformation initiatives fail. Digital transformation projects fail for a variety of reasons. It can be because of a lack...

Keep Reading

Live Chat | Emergency