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.
DBMS_SQL API
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!
Photo courtesy of Oracle.
