|
|
 Blogs
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
Jun
1
Written by:
StevenFeuersteinTW
Tuesday, June 01, 2010 12:29 PM
I recently received an email from Syed asking this very question as follows:
"Could you please let me know why DDL statements are not allowed [natively] in PLSQL? I read somewhere that the reason is that DDL statements will cause objects to be invalidated and then require recompilation. As all objects are already in a compiled [VALID] state, compiling again will be an overhead. But when we execute DDL statements [with EXECUTE IMMEDIATE] objects will still be invalidated and require additional recompilation - the same drawback. So why not allow it in as static SQL?"
There are generally two classes of questions that I prefer to not answer:
- Why did Oracle do X that way?
- Why doesn't Oracle let us do X?
I don't like speaking for Oracle, nor do I like speculating about why Oracle decides to do or not do something (or do it in a particular way).
So I suggested to Syed that he contact the Oracle PL/SQL Product Manager, Bryn Llewellyn. Hey, it's Bryn's job to represent Oracle to the PL/SQL developer community (among other responsibilities, though none more important.  ).
Syed did just that, and Bryn gave me approval to publish his reply. Here it is:
From the Keyboard of Bryn Llewellyn:
The answer is obvious, and not what you think.
First off, it helps to remember that at run time, all SQL issued by a PL/SQL program is "dynamic" in that the sense that the text of the SQL statement is handed over, at run time, from the PL/SQL subsystem to the SQL subsystem for execution and the SQL subsystem processes it in the same way. This is the case even for PL/SQL's embedded SQL (I don't like to call it "static" because of the point I just made). For embedded SQL, whatever parsing of the SQL statement that the PL/SQL subsystem did at PL/SQL compile time (in order to work out how to do the binds and defines, to establish dependencies, and so on -- see below) is re-done at run time.
PL/SQL's embedded SQL has these big advantages:
(1) The text is checked at compile time for correct syntax and (unhelpfully for invoker's rights programs) for correct semantics. Following on from this, dependencies are set up to the referenced objects (but for IR units they might not be helpful).
(2) When the statement includes local PL/SQL variables (which select, insert, update, delete, and merge statements, a.k.a. DML statements, typically do), these are converted to placeholders in the SQL that will be used at run time and appropriate binding code is included in your executable PL/SQL program. Appropriate code is also generated to handle values returned by select statements, and by change-statements that use returning. Embedded SQL makes all this highly usable.
(3) The statement execution code that's generated implements a special soft-parse avoidance scheme. You can think of the benefit as rather like that by setting Session_Cached_Cursors to a non-zero value for an ordinary OCI program -- only quite a lot better.
Non-DML statements cannot benefit from these advantages. Notice that ready-to-go statements that are not select, insert, update, delete, or merge are never cached in the shared pool. Further, especially because the names of the target objects are typically not known until run time, and because only DML statements allow binding, these non-DML statements are almost always composed programmatically -- ideally with the help of the DBMS_Assert functions. In other words, you never see an into or using clause when execute immediate is used for anything except DML.
This means that there would be no real benefit if a scheme were invented to allow non-DML statements to be written in PL/SQL using a new kind of embedded SQL.
|
|