I just started reading a new web site on PL/SQL programming. It is actually hosted by Oracle. Today I read a review of the different ways to execute SQL in PL/.SQL. They are embedded SQL, Native Dynamic SQL, and the DBMS_SQL API.Embedded SQL is easy. You just code up the SQL like normal. There are some caveats like specifying the variable into which you select some data. But it is very natural. Here is a hint. Make sure you define aliases for the table you select from. Then use these aliases to qualify the columns you select.
Native Dynamic SQL is when you run the commands with an EXECUTE IMMEDAITE. You should exercise care to protect against SQL injection. I have written about this before. This style is fast than DBMS_SQL. Note that DBMS_SQL is the old way of executing dynamic SQL. It is not entirely obsolete. If you do not know the return values until run time, this is the only option. But most times you can skip it.
Now let’s talk briefly about native compilation. PL/SQL is interpreted. Normally it gets compiled down to M-code. The M-code is executed by the PL/SQL Virtual Machine (PVM). However for speed purposes you can go native. In the Oracle 9i and 10g days, native compilation would transform your PL/SQL to code in the C programming language. This C code would be compiled by a C compiler on the operating system. The result was a library that could run the code very fast on the machine.
Things change up a little bit for native compilation in Oracle 11g. But let’s leave that for a future blog post.
