Obsessed with Oracle PL/SQL

If you work with PL/SQL, you will not want to miss any of these sessions : Sunday, Sep 18, 8:00 a.m. | Moscone West—2010 The Best Oracle Database 12c New Features for Developers and DBAs [UGF2028] Alex Zaballa,  Senior Oracle Database Administrator,  Accenture Enkitec Group This presentation looks at which new features of Oracle Database 12c are the most interesting to DBAs and developers. If you want to get up to speed with the most current release of Oracle Database, this session is for you. The session includes examples to show the functionality of the new features: • Multitenant • In-memory • SQL query row limits and offsets • Invisible columns • Approximate count distinct • PL/SQL from SQL • Session-level sequences • Session private statistics for GTT • Temporary undo • Multiple indexes on the same set of columns • SQL*Loader Express • Limit the PGA • Statistics during loads • Partial indexes for partitioned tables • Full database caching • Oracle Recovery Manager...

Charles Wetherell, Consulting Member of the PL/SQL development team, was kind enough to offer these insights regarding PL/SQL optimization and native code generation. A PL/SQL programmer asked why PL/SQL native code generation was turned off when the PL/SQL optimization level was set to 1. There are four PL/SQL optimization levels:  0. Esoteric for some long-since-passed compatibility issues with release 9 and before  1. Basic code generation with debugging data created  2. Global optimization  3. Automatic inlining of local procedures Each level builds on the level before. Debugging data is not created above level 1. Generally, native code generation is independent of optimization level. Native code generation is turned off at levels 1 (and 0) because it interferes with debugging. In other words, PL/SQL code compiled at optimization levels 0 and 1 is always interpreted when executed. You should never use level 0. That is a blanket prescription. Certainly no n...

Back in June, we opened the "early adaptor" doors to the Oracle Dev Gym, a modern, mobile website on top of the PL/SQL Challenge's quiz platform. We've gotten lots of great feedback and continue to develop the site (with plans to make it generally available in Q1 CY2017). The next feature we will be releasing on the Dev Gym is Classes . Classes will offer us multiple opportunities to present workouts and quizzes to users. For example, on Friday 26 August, I will hold a "live" Dev Gym workout at a Chicago Oracle Group event . We will do this via a Class. Other classes in the future will be tied to monthly webcasts and more. And now my request for help: I'd much rather expose and fix any bugs a week before the event, rather than at the event itself. :-) So I have set up a test class for everyone starting at 18 August 15:00 UTC (10 AM US Central time).  I'd like as many of you as possible to take the quizzes in the two hours that the c...

Received via email recently: I made a recommendation as part of our best PL/SQL best practices as follows.   Always qualify all variable names with the Procedure name.  Example:            insert_row.p_cust_name   instead of  p_cust_name  ​I recollect reading about this is in one of your articles and now I am being asked to justify why I have made this recommendation. Can you please help me convey to my team why this is a good practice? My quick email response was: I recommend that you qualify references to variables inside SQL statements embedded in your PLSQL code. This additional information given to the compiler can help it minimize invalidation of program units when dependent objects (such as tables) are changed.  This feature is known as fine-grained dependency management, which was introduced in Oracle Database 11g. Prior to 11.1, the granularity of automatic dependency management in PL/SQL program unit...

I received this email from a longtime Oracle Database developer: Why shouldn't we  always use clobs instead of varchar2 in PL/SQL? Memory will be allocated once used so there should not be any overhead always to use clobs instead of a varchar2. There must be a reason, however I'm unable to come up with a explanation. If it is true,  you might say the varchar2 is deprecated, ;-) I had to laugh a bit when I saw this - not because I thought his question was funny or silly, but because it reminded me of what I sometimes say in my trainings. I ask students: Why don't we always declare all string variables as: my_variable VARCHAR2(32767); That way, we don't have to worry about a VALUE_ERROR exception if the string gets "too long". And since memory is allocated dynamically (for lengths above 4000), it won't cause excessive memory consumption. And my answer(s) to this question are: This declaration - especially appearing all over the place - will r...

Sten Vesterli published a very thought-provoking post on his blog: Please stop reading this post, and read that one. When you are done, come on back here for my thoughts on Sten's thoughts. OK. You read it. Here we go. First, thanks, Sten, for being such an interesting, wise, sometimes provocative voice in our community. Next, Sten writes: Now, on the one hand, I certainly agree that the vast majority of young developers are currently caught up in the modern version of a Gold Rush, which is: "Build an app using JavaScript, pay no attention to that database behind the curtain." But I can assure you that I still do meet young PL/SQL programmers, regularly, when I am at conferences and doing onsite presentations at companies. So, young person who writes PL/SQL: do not be afraid! You are not alone! And you are super-smart to have made the choice you did. :-) Next, Sten offers this advice to managers: I agree that PL/SQL is a "spec...