Thursday, July 30, 2015

Smoothing rough edges for productive programming

No language is perfect and while PL/SQL is an incredibly "tight fit" for building applications on top of Oracle SQL (and, of course, Oracle Database), it also is not yet quite perfect.

Maybe in Oracle Database 13(?) - (?) being whichever letter Larry decides will best reflect the main theme of that version....

In any case, in the meantime, and to paraphrase a saying:
You write code with the language you've got, not the language you want.
So the key thing is to maximize your productivity any way you can, all along the way.

And sometimes the steps you can and should take in this area can be very small, but they can still add up.

Case in point: displaying a Boolean value with DBMS_OUTPUT.PUT_LINE.

As anyone who's spent time with PL/SQL knows, DBMS_OUTPUT.PUT_LINE is the procedure provided by Oracle to display text from within a PL/SQL block on your screen.

It accepts a string (and anything that can be implicitly converted to a string) and sends it to the DBMS_OUTPUT buffer. Then when your PL/SQL block completes, the host environment in which you executed that block reads the buffer and shows you the text.

Simple enough.

And that takes us back to:
DBMS_OUTPUT.PUT_LINE accepts a string (and anything that can be implicitly converted to a string)....
As of 12.1, PL/SQL does not support implicit conversion of Booleans (which have just three values: TRUE, FALSE and NULL - well, I guess that's two values and one non-value) into anything at all.

So when I try to display a Boolean using DBMS_OUTPUT.PUT_LINE, all hell breaks loose:














Well, that's a bummer. What's a programmer to do?

What too many of us do is sigh and remember that this happened last week, too, and then spend another minute or so converting the code into this:

DECLARE
   l_boolean BOOLEAN := TRUE;
BEGIN
   IF l_boolean
   THEN
      DBMS_OUTPUT.put_line ( 'true' );
   ELSE
      DBMS_OUTPUT.put_line ( 'false' );
   END IF;
END;

Then we see our value. And then we have wasted our time. Again. And again.

Argh. No. Do not do this. Do not waste your time in this way. Life is too short.

Instead, accept that you will have this experience over and over again unless you take action. In this case, a very small action:
Save that pattern of code somewhere so you can access it quickly and easily the next time you need it.
Now, there are at least two ways to do this.

Save Pattern As Reusable Snippet or Template

If you are using an editor that's a bit smarter than Notepad, it likely allows you to save code snippets, and then grab them as needed.

In SQL Developer, you have choices in this arena: Code Snippet and Code Template.

Code Snippets offer the ability to create sets of code you access via drag-n-drop. Jeff Smith, SQL Developer Product Manager offers a fine explanation of how to use those on his blog.

For this kind of situation, I prefer to use Code Templates, for one simple reason: you can pull the template into your current editor with the auto-complete keystroke (Control-Space by default). This is a much smoother and faster way to suck in small chunks of commonly used code, and keep on coding.

Briefly here are the steps to go through to set up a new code template:

1. Open up SQL Developer Preferences and click on SQL Editor Code Templates under Database:


















2. Then click on Add Template, type in a very short name for your template (just a suggestion :-) ) , and then paste in the code:















Save it, close Preferences, and from that point on, when you are writing code, type (in my case) "b2v", press Control-Space and voila! Your code will appear. Nice.

Save Pattern As Reusable Code

You could also save the pattern as reusable code: encapsulate the entire IF statement inside a procedure (in essence, extending the PL/SQL language), and then call that procedure as needed.

CREATE OR REPLACE PROCEDURE display_boolean (
      boolean_in IN BOOLEAN)
   AUTHID DEFINER
IS
BEGIN
   CASE boolean_in
      WHEN TRUE THEN DBMS_OUTPUT.put_line ( 'TRUE' );
      WHEN FALSE THEN DBMS_OUTPUT.put_line ( 'FALSE' );
      ELSE DBMS_OUTPUT.put_line ( 'NULL' );
   END CASE;
END;
/

Or you could get rather "fancy" and build yourself an entire package to help manage Booleans in a consistent manner in your code:

CREATE OR REPLACE PACKAGE boolean_pkg AUTHID DEFINER
IS  
   FUNCTION bool_to_str (boolean_in IN BOOLEAN)
      RETURN VARCHAR2;

   /* Consistency with existing TO_CHAR functions, though 
      of course you still need to preface it with 
      "boolean_pkg". */

   FUNCTION to_char (boolean_in IN BOOLEAN)
      RETURN VARCHAR2;

   FUNCTION str_to_bool (string_in IN VARCHAR2)
      RETURN BOOLEAN;

   FUNCTION true_value
      RETURN VARCHAR2;

   FUNCTION false_value
      RETURN VARCHAR2;
      
   PROCEDURE put_line (boolean_in IN BOOLEAN);
END boolean_pkg;
/

CREATE OR REPLACE PACKAGE BODY boolean_pkg
IS
   /* Change strings to language appropriate values! */
   c_true    CONSTANT VARCHAR2 (4) := 'TRUE';
   c_false   CONSTANT VARCHAR2 (5) := 'FALSE';

   FUNCTION bool_to_str (boolean_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN
      CASE boolean_in
         WHEN TRUE THEN c_true
         WHEN FALSE THEN c_false
         ELSE NULL
      END;
   END bool_to_str;

   FUNCTION to_char (boolean_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN bool_to_str (boolean_in);
   END;

   FUNCTION str_to_bool (string_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN 
      CASE string_in 
        WHEN c_true THEN TRUE
        WHEN c_false THEN FALSE
        ELSE NULL
      END;

      /* Or you could be "nicer" and support many
         different common values...
      RETURN 
      CASE string_in 
        WHEN string_in IN (c_true, 'T', 'Y', 'YES') THEN TRUE
        WHEN string_in IN (c_false, 'F', 'N', 'NO') THEN FALSE
        ELSE NULL
      END;
      */
   END str_to_bool;

   FUNCTION true_value
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_true;
   END true_value;

   FUNCTION false_value
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_false;
   END false_value;
   
   PROCEDURE put_line (boolean_in in boolean)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (bool_to_str (boolean_in));
   END;
END boolean_pkg;
/

Yes, sure, you can copy and paste this code and use it. Be my guest. It sure isn't rocket science. :-) You can also try it out and download it from LiveSQL.

And that's the whole point. It usually doesn't come down to anything elaborate or sophisticated to avoid this repetitive coding disorder. Just a little bit of discipline, getting things set up.

Oh and then remembering how to access your reusable stuff (the name of your snippet/template, the actual existence of that reusable code). :-)

Thursday, July 23, 2015

Thoughts on PL/SQL, code sharing, and code generation

[June 2016 update: we have now launched a community code generation project: oddgen - for Oracle Data Dictionary Generator. More information available here. Join the project!]

[While I am sure you will be BLOWN AWAY by what I write below, just in case you are already convinced of the need for a flexible code generator for PL/SQL, skip to "Needed: Flexible Code Generator for PL/SQL"]

If you have spent much time in the PL/SQL world, you will have noticed some unusual aspects:

  • Almost no third party libraries or utilities (prominent exceptions: utPLSQL, Logger, Alexandria Library)
  • Minimal code sharing
  • Most applications built from scratch
The cause(s) of this odd reality (odd in comparison to, say, the way JavaScript apps are built)? Likely several, including:
  • Management concerns about putting "someone else's code" in their database
  • PL/SQL community well established before the days of open source, so no tradition of sharing, of free code, etc.
  • How much code can actually be shared for PL/SQL-based applications?
That last point/question is what I'd like to explore a bit in this post.

It's easy to shake our heads at the "sad state of affairs" when it comes to sharing of code in the PL/SQL world, when it comes especially to thinking about the thousands of PL/SQL teams around the world who, again and again, built an application almost from the ground up with minimal code re-use.

It's easy to conclude: "If only those developers knew better." or "If only managers could be less suspicious of open source." and so on.

But I am thinking more and more lately that the reality of minimal code sharing in our community may more to do with the fundamental nature of PL/SQL (and by extension any other database programming language, such as Transact-SQL).

PL/SQL is not a general purpose programming language. 

It is purpose-built to "extend" the SQL set-oriented, declarative data access language with procedural constructs. 

It is optimized both in terms of performance and syntax to allow programmers to easily (relatively speaking, anyway) build secure, high performance and maintainable applications on top of Oracle Database.

So PL/SQL is tightly tied to SQL, which means it is tightly tied to the tables used in each application. Which means that a large percentage of the code you write/need in PL/SQL will be specific to a given set of tables and columns - and those tables change for each application, usually by a whole lot

From this perspective, it makes more sense that there would be relatively little reuse and code sharing. Reuse is driven by patterns: we write the same code over and over, so, well, why not use the same code over and over, right?

Yes and no.

There's no shortage of patterns in PL/SQL code, but most of them have to do with patterns of SQL operations inside a PL/SQL subprogram. Heck, you could even consider any DML statement as a kind of template or pattern in and of itself (with the infamous "TAPI" - table API - a collection of all such templates). But what varies? A lot: the table names, the column names, the WHERE clause predicates, when you need to fetch all rows for a given value (foreign keys), the conditions under which you want to fetch just one row for a given value (primary keys), etc.

Lots of patterns, but little opportunity for "out of the box" reuse.

I am coming around to seeing that this fundamental reality of PL/SQL as a language designed to serve as an interface layer on top of SQL statements is the biggest "obstacle" to code reuse, and building PL/SQL applications on an extensive foundation/framework of code.

How about you? Do you see a flaw in my thinking?

Because if you do not, you might end up in the same place I find myself:

If code sharing / reuse may not be terribly applicable to PL/SQL, then conversely code generation is way more applicable. 

With code generation, you start from a generic template and then you apply that template to a particular database object, such as a table or view or package. The result is generated code that is specific to that object, but follows the pattern.

tapigen2 offers a fine example of such a code generator. In this utility, Oscar has put the template inside his generator, a part of which you can see here:


CREATE OR REPLACE PACKAGE BODY tapi_gen2
AS
   --Global private variables
   g_spec_template   VARCHAR2 (32767);
   g_body_template   CLOB;
   g_vars            teplsql.t_assoc_array;
   g_unque_key       dbo_name_t;


   PROCEDURE load_spec_template
   AS
   BEGIN
      --Spec Template
      g_spec_template :=
         q'^CREATE OR REPLACE PACKAGE tapi_${table_name}
IS
   /**
   -- # TAPI_${table_name}
   -- Generated by: tapiGen2 - DO NOT MODIFY!
   -- Website: github.com/osalvador/tapiGen2
   -- Created On: ${date}
   -- Created By: ${user}
   */


Then when I run the generator, say against the classic HR employees table, I get this and much more:


create or replace PACKAGE tapi_employees
IS
   /**
   -- # TAPI_employees
   -- Generated by: tapiGen2 - DO NOT MODIFY!
   -- Website: github.com/osalvador/tapiGen2
   -- Created On: 21-JUL-2015 12:56
   -- Created By: HR
   */
   --Scalar/Column types
   SUBTYPE hash_t IS varchar2 (40);   
   SUBTYPE employee_id IS employees.employee_id%TYPE;
   SUBTYPE first_name IS employees.first_name%TYPE;
   SUBTYPE last_name IS employees.last_name%TYPE;
   SUBTYPE email IS employees.email%TYPE;
   SUBTYPE phone_number IS employees.phone_number%TYPE;
   SUBTYPE hire_date IS employees.hire_date%TYPE;
   SUBTYPE job_id IS employees.job_id%TYPE;
   SUBTYPE salary IS employees.salary%TYPE;
   SUBTYPE commission_pct IS employees.commission_pct%TYPE;
   SUBTYPE manager_id IS employees.manager_id%TYPE;
   SUBTYPE department_id IS employees.department_id%TYPE;
   ...   

I have a long and very mixed (in terms of success) history with code generators. I've built several TAPI generators over the years, but reached my pinnacle of code generation craziness with a tool called QNXO (quality in, excellence out - terrible name with nary a single hit in Google, and I still got sued for copyright violation. :-) ). 

Later rebranded as the Quest CodeGen Utility (and no longer available), this utility was a design pattern factory. Built around a Code Generation Markup Language (that I designed, and was therefore kind of hokey), you can define whatever pattern you wanted, for whatever target "language" you wanted (generate a PL/SQL TAPI, a Java class, HTML doc, whatever), then point it at a database object and voila! lots of really cool code.

It also offered a bewildering amount of flexibility (specify your naming standards, for example) that made it hard to use. It didn't very far, but I still think it is a great idea.

And I want to resurrect that idea again, but see if we, the PL/SQL community, can be a much better job implementing it. Because I think that this is:


Needed: Flexible Code Generator for PL/SQL

I am convinced that a code generator with all the right features, with just enough but not too much flexibility, could make a powerful difference in the lives of many PL/SQL developers. You would be able to build your applications faster, relying on high quality code that is, in essence, pre-tested. 

You'd still have to write lots of bespoke, custom code, sure - but a much larger percentage of your code base than ever before could be generated - and re-generated as needed (or simply use the generated code as a starting point and modify from there).

I am also convinced that this effort should from the start be an open source project, primarily driven by PL/SQL developers, not Oracle employees (though I and others would certainly help). 

Here are what I see as the key features of such a utility:
  • (external) Template driven - the template (using what language? that is something to be decided. XML? Angular expressions? A variant on my old CGML? Hmmm....) would be separate from the code generator. 
  • Template libraries - anyone can build their own template, share it with others or keep it to themselves. 
  • Flexible naming conventions - in my experience, if developers cannot specify utilization of their own naming conventions, they will resist using the generated code.
I don't think this will be all that hard to do, especially if it is built incrementally and we avoid scope creep. With the right tempting language, most of the complexity could be "buried" in the templates themselves.

Have I convinced (any of) you? Are you also enthused about code generation and see the potential in a database programming language context? Would you like to help on this project (a little, a lot, doesn't matter - every bit helps!)? I figure we need at least two people who would like to take a leadership role, set up the project, and push it along. I will help, but I think I should take a back seat (but not very far back) for a number of reasons.

Well, I'd love to read your comments below, or send an email to steven.feuerstein@oracle.com. I will compile a list of interested parties and organize a conference call to discuss this further. 






Tuesday, July 21, 2015

PL/SQL Brain Teaser: how many ways can you define a cursor in PL/SQL?

According to the PL/SQL User Guide,
cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
So how many ways can you define a cursor in PL/SQL?

Let's include in this ways in which you can get Oracle to define a cursor for you, as well.

Please submit a comment with JUST ONE example of a cursor definition. You can submit multiple ideas, but with multiple comments. 

If you include several in a single comment, I will take only the first one that has not already been suggested

Monday, July 20, 2015

Getting started: Sharing your Oracle Database Developer experience

Last week, I was emailing back and forth with Paul, who is a senior developer/architect at a major financial firm. He is widely respected within the company, a valued resource for many development teams. He has also garnered the respect of the PL/SQL development team.

Yet Paul is not well known outside of his company. He does not use Twitter, does not have a blog.

I encouraged him to share his knowledge publicly and he asked me: "How should I get started?"

My first instinct was to say:
  • Register on the OTN Community!
  • Start a blog!
  • Start a Twitter account!
  • Set up a Facebook page!
  • Join LinkedIn!
  • etc.
etc. But then I reflected back on the effort it's taken me, "Oracle celebrity" me, to build up my Twitter following, get people to read the posts on my blog, and I decided to hold off on the instinctive response and instead ask myself:

How can someone who is technically strong but without name recognition contribute to the community in a way that doesn't feel like a waste of time (as in: "If a tree falls in the forest and no one is there to hear it....")?

So here goes:

1. Sure, go ahead and make sure you are fully social media-augmented. Create a Twitter account (and follow me @sfonplsql and oddly enough @stevefeuerstein). Create your Facebook page. Create your LinkedIn account. Make sure you are registered in the OTN Community. Etc.

Then follow people you respect who are present in these ecosystems. This is an incredibly important thing to do for all Oracle technologists (by which I mean: follow me! :-) ). Compared to many other programming communities, Oracle Database developers (SQL, PL/SQL, etc.) are not present to great numbers on Twitter. This means that the "big names" in this same arena lag behind the counts you will see, say, in the SQL Server world. 

And that sends the wrong signal about the vibrancy and breadth of our community. It's a numbers game and we need all of you to "be a number."

2. Make yourself heard on existing forums and popular accounts. Basically the advice "Go where everyone is and participate". 

Once you follow enough people on Twitter, you will have ample opportunity to read and respond to tweets in your area of experience. Ask questions, add nuance to a tweet, share your specific experiences. Go to StackOverflow and take a shot at answering questions. 

Do you read blogs of your favorite programmers and authors? Enter comments on their posts, adding your experiences, asking questions. Bloggers love getting comments and love answering questions. 

3. [With all proper approvals from your employer] Contribute to or otherwise support open source projects for Oracle Database developers. While open source doesn't play a very big role in our lives now, it can only increase over time. Check out the following:
  • OraOpenSource - "Build great open source products for Oracle."
  • Alexandria Library - "A collection of various utility packages for PL/SQL, as well as links to useful libraries hosted and maintained elsewhere."
Don't expect your life to be changed overnight. It takes time to be noticed in a world in which everyone has a voice, and we all go on and on.

Do you have other suggestions for getting started, building a following, sharing your expertise? Please, then, post a comment!

Thursday, July 16, 2015

Nuances of NOCACHE, a.k.a., Steven eating (partially) his words

I posted this tweet yesterday:
#314b #plsql Avoid gaps in sequence NEXTVALs: use NOCACHE option when creating or modifying sequence. #FrugalProgramming
It was a great success social media-wise. Several Oracle technologists I admire greatly responded to it, and none favorably.

But my twitter stats will improve, so hurray! :-)

Seriously, though, it was a problematic tweet, partly because it was a piece of advice that only makes sense in a fairly narrow context, or as @brynlite pointed out:
I expect to repeat this often, Steven: don't advocate specific solutions until the requirements have been stated
To switch I rather snarkily replied:
Keep on saying it, I guess, Bryn, but I am not sure how the advice can apply in the world of Twitter.
OK, fine, it wasn't my finest moment.

As I was stretching this morning I realized: I should have written a post on my blog explaining the tweet, and then I could refer to that. Or as PL/SQL Challenge SQL Quizmaster @kibeha tweeted:
Reason why tweets aren't well suited to teach - unless always tweeet links to blog / video / etc
So here we go:

At the PL/SQL Challenge, we write quizzes. This happens on a regular, but not frequent basis. In other words, insertions into the QDB_QUESTIONS table (I could explain the difference in our data model between a "question" and a "quiz", but why bother, right?) do not resemble placing orders on Amazon.com.

A reviewer noted that with the QDB_QUESTIONS_SEQ defined with the usual cache of 20, question IDs (which we reference regularly in the reviewing and editing process, since there is no "name" per se for a question) would leap forward 20 at a time.

This gave a distorted view (not presented to our players) of quiz submissions. So he recommended switching to NOCACHE to minimize the gaps in question IDs.

I thought that was a fine and sensible idea, and so applied that change. Then, as is often the case, I used what I learned (or was reminded of) that day for my daily PL/SQL tweet.

Unfortunately, this particular "tip" is not appropriate for many (most, really) scenarios faced by developers when needing to generate unique ID numbers.

My apologies, and thanks to all for the corrective advice.

Tuesday, July 14, 2015

Inline your subprograms - but only within same program unit

Got an email from an expert Oracle Database developer (let's call him Sheldon, in honor of my dad, who taught himself programming - RPG, no less!) yesterday:
Is inlining (via PRAGMA INLINE) even something worth talking about?  I’m getting the feeling that it’s not. I’m trying to come up with an example to show folks the beauty of inlining function calls.  And I’m getting the idea that either oracle is doing something like inlining code all the time or is just really super-efficient on the calls these days.
Well, sure, Oracle Database is really super-efficient. Always. Automagically. Don't have to worry about doing any tuning yourself. Oh, wow, what a wonderful world!

Pinch. Wake up! That was a dream you were having, Steven.

Oracle Database does an awful lot to ensure high performance for our applications, but it's not yet fully automatic or automagic. We can do things to drastically screw up performance. And we can also take actions, both in the way we write our code and in the features we utilize, to improve performance.

Inlining is one of those. First, an explanation of the basic concept, then I will address Sheldon's question.

Suppose that I have written very nicely modularized code, with lots of functions defining APIs to underlying rules and data, with nested subprograms to improve readability, etc. 

If you want to write an application that is easy to debug, maintain and enhance, you simply must modularize your logic.

But there is a price to pay: each time the PL/SQL engine executes the invocation of a subprogram, there is some overhead involved (look up the code, transfer data back and forth, etc.).

The idea behind inlining is that at the time of compilation, the compiler replaces the invocation of a subprogram with that subprogram's implementation. Then at runtime, the cost of executing the subprogram is reduced.

Here's a very crude example. I write this code:

CREATE PROCEDURE my_proc
IS
   n NUMBER;

   FUNCTION nested_func (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      n := nested_func (indx);
   END LOOP;
END;
/

But with inlining enabled, what is actually executed looks more like this:

CREATE PROCEDURE my_proc
IS
   n NUMBER;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      n := indx * 10;
   END LOOP;
END;
/

Simple enough, right?

OK. In Oracle Database 11g, we (not that I was a part of "we" - Oracle Corporation - back when 11.1 was released) added a new PL/SQL optimization level (3) and a new pragma (INLINE) to allow PL/SQL developers to take advantage of inlining. You can turn it on globally via the optimization level or in a very granular fashion with the pragma (which means: command to the compiler).

I won't go into all the details here. Check out the doc or Tim Hall's fine article or Adrian Billington's helpful exploration for lots more information.

So then - back to Sheldon's question. He was unable to verify the performance benefit from inlining. What's the problem? Well, let's read the introduction to Subprogram Inlining from the doc:
One optimization that the compiler can perform is subprogram inlining. Subprogram inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit). 
Note that last section within parentheses:

The invoked and invoking subprograms must be in the same program unit.

What exactly does that mean? 

It means that the function you want to inline must be:
  1. Defined as a nested subprogram within the subprogram that invokes it, or...
  2. Defined within the same package. 
You've already seen an example of the former. But before I move on to an example of the latter (inlining subprograms from same package) or even showing you an example where inlining will not happen, I'd like to cover:

How do I know whether or not inlining is working?

My approach has been to perform timings on various implementations and see if a particular scenario makes things faster. That can be productive, except when the optimization technique is fairly fine-tuned in its applicability (which can be the case with inlining).

Bryn Llewellyn, PL/SQL Product Manager (follow @brynlite), suggests a far superior approach: use compile-time warnings (though in this case, less of a warning than an FYI).

Let's compile the my_proc procedure with inlining enabled (that's what level 3 optimization means) and compile-time warnings enabled:

alter session set Plsql_Warnings = 'Enable:All'
/

alter session set Plsql_Optimize_Level = 3
/

CREATE PROCEDURE my_proc
IS
   n NUMBER;

   FUNCTION nested_func (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      n := nested_func (indx);
   END LOOP;
END;
/

Then I issue a SHOW ERRORS command and see in SQL Developer the following:


Inlining was performed, and once that was done, there were no more calls to NESTED_FUNC, so the  compiler also removed the nested subprogram definition. Cool!

Now let's do the same thing for an invocation of a subprogram that is defined within the same package, but outside of the invoking subprogram:

alter session set Plsql_Warnings = 'Enable:All'
/

alter session set Plsql_Optimize_Level = 3
/

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE test_inlining;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
   FUNCTION private_func (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;

   PROCEDURE test_inlining
   IS
      n              NUMBER;
   BEGIN
      FOR indx IN 1 .. 10000000
      LOOP
         n := private_func (indx);
      END LOOP;
   END;
END;
/

And SHOW ERRORS reveals:



Finally, let's try to inline a subprogram defined outside of the invoking subprogram's program unit.


alter session set Plsql_Warnings = 'Enable:All'
/

alter session set Plsql_Optimize_Level = 3
/

CREATE OR REPLACE FUNCTION my_func (p NUMBER)
      RETURN PLS_INTEGER
IS
BEGIN
   RETURN p * 10;
END;
/

CREATE OR REPLACE PROCEDURE test_inlining
IS
   n NUMBER;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      n := my_func (indx);
   END LOOP;
END;
/

And what warnings do I see now?





Not a single mention of inlining. Even with the optimization level set to 3. That should be a pretty good hint, right?

But let's make sure. I will explicitly request inlining of the call to MY_FUNC (which should not be necessary, given the optimization level, but bear with me). 

CREATE OR REPLACE PROCEDURE test_inlining
IS
   n NUMBER;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      PRAGMA INLINE (my_func, 'YES');
      n := my_func (indx);
   END LOOP;
END;
/

And then I see the following warnings:



Ah....so you can that the compiler tells me that I've made the request - but it was not "done", only "requested". 

As stated in the documentation, since my_func is not defined within the same program unit as the invoking subprogram, inlining does not apply.

So now I've shown you when inlining will take place and how you can verify that the compiler performed as expected. 

Compile-time warnings are really quite cool. Turn them on in your IDE and pay attention to what they say!

Going back to the original question from Sheldon, though: "Is inlining (via PRAGMA INLINE) even something worth talking about?  I’m getting the feeling that it’s not."

In my (admittedly limited) experience, it can be hard to come up with scripts that demonstrate massive performance improvements. It will definitely help most in "extreme" scenarios involving a very large number of subprogram invocations (loops, recursion, etc.).

[2016-09-23] The recommendation from the PL/SQL Development Team is unequivocal on this matter: you should set the PL/SQL optimization level to 3 at the instance level. Give the compiler the maximum number of tools to use to optimize your code. It may not make a big difference, but it will surely help overall.

I could, I suppose, take the position that you should just turn on inlining globally (optimization level) and let the compiler have at it, except that the documentation also states:
If a particular subprogram is inlined, performance almost always improves. However, because the compiler inlines subprograms early in the optimization process, it is possible for subprogram inlining to preclude later, more powerful optimizations. 
If subprogram inlining slows the performance of a particular PL/SQL program, then use the PL/SQL hierarchical profiler (explained in Oracle Database Development Guide) to identify subprograms for which you want to turn off inlining. 
 So I would suggest that you not bump up the optimization level to 3 globally, and instead put in specific, explicit requests to inline precisely in those programs in which the use case fits clearly and you can demonstrate a noticeable improvement.

Monday, July 13, 2015

Will Oracle ruin the PL/SQL Challenge?

I received this email from a person who has played the PL/SQL Challenge quizzes for years and now wanted to delete his account and player history.

Here's part of the reason he gave:
"I get the impression that the PL/SQL Challenge website is getting closer and closer to Oracle corporation (SSO with oracle.com, Oracle advertisements on the start page, …) – I always valued the PL/SQL Challenge as an independent resource, and these changes are IMHO going in the wrong direction."
This took me by surprise, but perhaps it should not have. Since rejoining Oracle last year (after being away for 22 years), it has become more and more clear to me that a noticeable percentage of my fellow Oracle Database developers and DBAs love the technology but don't love the company. 

I suppose that explains why soon after I went back to Oracle, several people came up to me with a sad look on their face, as if they’d lost a good friend: “Why did you join Oracle?” they asked, shaking their heads. 

For me, being back at Oracle means that I will be able to do more, have more impact, help more PL/SQL developers than ever before. I was excited and thought everyone else would be, too. Clearly, I had underestimated the depth of negative feelings.

This refrain (“Why oh why did you join Oracle?”) has surfaced multiple times over the last six months, as I hired well-known and highly-respected experts in the community (for example, Dan McGhan and Connor McDonald).

Now the PL/SQL Challenge has been moved to an Oracle server, and at least one person (which likely means there are a bunch more) is worried that this move will somehow compromise the independence of the website (implying, I suppose, that criticism of Oracle will not be tolerated, that we will just make excuses for any bugs encountered, that the site will turn into a big marketing effort). 

I do not see happening at all with the PL/SQL Challenge, nor with my more general work on the PL/SQL language.

No one at Oracle up my management chain has ever told me to do things differently now that the PL/SQL Challenge is Oracle property. No has told me to censor certain kinds of comments on the PL/SQL Challenge site, or that I must "toe the corporate line". 

Players remain free and encouraged to comment on quizzes, even if that means you will point out a bug or complain about something in the SQL or PL/SQL languages. And I pledge to you that I will approve comments that are critical of Oracle  both company and technology - so long as they are expressed in an appropriate manner and are relevant to the quiz at hand. 

Same thing goes with this blog.

Please, by all means, give it a try! What’s your complaint? What’s your beef with Oracle? 

To my way of thinking, it’s much better for us to hear your concerns and respond to them, hopefully by improving our products and the services we provide to you..

I am excited about moving the PL/SQL Challenge inside Oracle. It is a powerful platform for delivering quizzes, and I have high hopes of seeing it utilized by more of the product teams (weekly quizzes on APEX! ORDS! Database Administration! Java?). I also have high hopes that with the increased visibility that will come from promoting the quizzes on Oracle Technology Network, the Oracle Learning Library and more, many more Oracle technologists will benefit from the quizzes and the community that has grown up around them.

As for Oracle advertisements on the home page, it is true that we have added a couple of images on the right hand side to help you get more information about Oracle Database 12c and Oracle’s Cloud offerings. I don’t see these as ads, but as helping serve the common needs of Oracle and its user base.  Do you think you do not need to learn about 12.1 features or about where Oracle is going in the Cloud?

I did not join Oracle to help it improve its stock price (though that will be fine with me: I still own some). I joined Oracle to help Oracle Database developers utilize our fantastic technology more fully and to ensure that future generations of application developers understand how Oracle Database can help them build powerful, secure, maintainable applications.

We are currently moving forward with several initiatives in this area, including:

1. Oracle Database Developer Choice Awards: Years ago, Oracle gave out annual Excellence Awards to developers, such as the PL/SQL Developer of the Year award (I am honored to have received this twice). But then those awards stopped. We have restarted - and reshaped - those awards. The Dev Choice awards recognize both technical experience and community building, and our users will vote to choose the winners.


2. Project Solver: You will be hearing lots more about this soon. Solver revolves around the idea of constructing a community-curated expertise repository on all things Oracle Database (and eventually beyond) that will help our users solve their problems much more quickly and will high-quality solutions. This is not about Oracle passing down the word from on high (“Steven says this about PL/SQL”), but about ensuring that the thousands of Oracle Database experts all around the world are better able to share their hard-won wisdom and get recognition for their accomplishments. I know that there are many PL/SQL developers who know as much about PL/SQL as I do, and have real-world experiences that go far deeper than my knowledge in specific areas. 

You know who I’m talking about. That's right: you. Wouldn’t it be great to share your expertise, and be recognized for it?

3. More and better multimedia resources: It’s not easy for a well-established and successful organization to change direction, especially as the world around it changes as quickly as is happening in the 21st century. For too long Oracle has relied on whitepapers and slide decks - mostly stiff and corporate - to transfer expertise. Well, hey, it’s a YouTube, Twitter, LinkedIn kinda world, it’s a quirky, social, don’t take yourself too seriously kinda world, and it’s time Oracle caught up. So we are investing in both people and gear to allow us to “play” in this new world. Hopefully at least some of you have enjoyed Chris Saxon’s Magic of SQL video series and my Practically Perfect PL/SQL channel. You’ll see lots more of that sort of thing in the months and years to come.

I am not trying to argue that our users don't have legitimate gripes about some of the things Oracle does. And I am not nearly arrogant enough to think that the projects in which I am involved will lead to widespread change at Oracle. That’s above my pay grade. I do know, however, that executives in the Product Development organization understand the importance of our current and future users, and the need to change how we communicate and partner with you. And they have given me (and others) resources to get started on that change. 

So by all means, keep hold of your skepticism, voice it wherever and whenever, but please also recognize initiatives from Oracle that are addressing the sources of your skepticism. And very specifically, help me and my Oracle Developer Advocates team put users front and center, and give all of us better resources to build better applications on top of Oracle Database.

Monday, July 6, 2015

Kscope15: Reflections on another great ODTUG conference

I recently spent a week in hot, muggy Florida, visiting my mom both before and after Kscope15. Thanks, ODTUG, for organizing the event near my mom's home! Oh, and a BIG thanks for scheduling Kscope16 in Chicago. One less trip on an airplane! Hurray!

Kscope15 followed a predictable (and very pleasant) pattern:
  • an extremely well-organized event (kudos to Executive Director Crystal Walton, Conference Manager Lauren Prezby and the rest of the the amazing Your Conference Connection staff, as well the ODTUG conference organizing committee and board)....
  • gathering together many of the hottest and most engaged Oracle Database development talent....
  • in a really nice location (resort hotel right on the beach).
Many attendees brought their families, so I had lots of kids to play with, including and most rewardingly, little two year old Kingston, who now calls me "Uncle Steven." I love extending my virtual family. :-)

Some highlights from Kscope15 (not meant to be a comprehensive review, but simply reflecting my experience) include:

Launched Oracle Database Developer Choice Awards


We announced the brand-new Oracle Database Developer Choice Awards at the conference. These awards celebrate and recognize technical expertise and contributions in the Oracle Database community. As longtime and new users of Oracle Database move to the Cloud and take advantage of this exciting new architecture, community experts will play a critical role in helping them succeed.


For 2015, awards will be given out in the following technology areas:

  • SQL
  • PL/SQL
  • Oracle REST Data Services
  • Oracle Application Express
  • Database Design

This awards program is different from the Oracle ACE program in a number of ways, the most important of which are:

Nominations come from the community (you).
Panels of ACEs decide on a set of finalists.
Winners are determined by popular vote (that is, by you).

In other words, as much as is possible for a program implemented by Oracle, Oracle will not be driving the process and selecting the winners. You will.

First-ever YesSQL Day at Kscope

Tom Kyte and I held the first ever YesSQL Day at a Kscope: we each had two sessions in which to explore the fundamental value propositions of both SQL and PL/SQL.

Tom did his usual outstanding job of (a) offering context for understanding SQL's historical importance and (b) wowing us with what is now possible in the Oracle SQL language.

I must confess, though, that as I watched Tom's second hour, showcasing the wizardry of SQL, I found myself wondering: do attendees watch Tom and think "Exciting! I can't wait to get back to work to try it!" or something more like "Wow, Tom is brilliant. I don't think I could ever do that."

I sincerely hope the latter former. :-)

I then had two hours to talk about PL/SQL. The original title for my talk was "21st Century PL/SQL", but on Sunday, I asked the Kscope15 organizers to change it to:

PL/SQL Developers: The Most Important Programmers on Earth

That's because, well, I like to feel important, really important.

OK, so that's not all. My managers (at least three levels up the chain) agreed to allow me to build a whole team - the Oracle Developer Advocates - in order to make sure that developers (especially open source, scripting language programmers) understand the fundamental importance and value of the Oracle Database for application development.

But for me to do that, I not only needed to believe it (which I did, instinctively and self-servingly), but to understand why, to be able to step through the argument logically. It's the way I think, and the it's the way I write (which, I think, contributes to the readability of my books and articles).

So I've spent a fair amount of time thinking it through. At this same time (well, actually, starting before I rejoined Oracle last March), I had also been studying evolution, and what we now know about the way that organisms evolve, develop, survive and reproduce in the world. To my great surprise, these two seemingly disconnected trains of thought intersected a few weeks ago.

And PL/SQL Developers: The Most Important Programmers on Earth was the result. I've uploaded my presentation on OTN (it now has the title "Oracle Database Developers: the most important programmers on Earth") if you'd like to check it out. It makes the argument in a bare-bones, slide deck kind of way. I will be recording my thoughts on camera soon for publication on Practically Perfect PL/SQL.

Analytics, Model Clause, Edition-Based Redefinition, and Rethinking "Advanced"

I am, sadly, a very typical PL/SQL in one regard: I have not kept up with the latest regarding SQL. So at this Kscope, I decided to pay a bit more attention to the SQL side of things.

I started out by attending both of Tom Kyte's YesSQL sessions. I didn't follow well the most interesting SQL he presented, but was intrigued by his reminder that analytic functions have been in Oracle Database for a very long time. Yet, they are still tremendously underutilized. The model clause has also been available for years, but few people know of it or are comfortable using it.

Alex Nuijten's session SQL Model Clause: A gentle introduction helped me understand that feature better, but I am not still not sure how soon I will be using it.

I also attended a couple of talks on Edition-Based Redefinition (EBR), a feature introduced in 11.2 that allows us to hot-patch our applications. It is one of those amazing things we've baked into the database, but very few people know about use.

Like analytics and the model cause, EBR is perceived as "advanced" and "complicated" - and so most developers avoid it.

Which has me wondering: maybe we need to rethink what is advanced, and what we should project as advanced.

Perhaps when it comes to teaching SQL, we should include basic analytics from the very start. Perhaps the way that should happen is that instead of starting from: "Use ORDER BY to sort. Use WHERE to filter...." we should pose typical problems and then show SQL can solve them most easily. I expect that many common requirements naturally would lead to analytics.

In this way, we might be able to offer a more holistic view of SQL and how it helps you manage data.

As for EBR, it's just one feature (among many) that gets me reflecting on how Oracle builds a lot of great technology, but we don't do enough to make it easy for users to apply that technology.

Instead we too often find ourselves shaking our heads and wondering: "What is wrong with our users? How can they not be using LAG and LEAD after all this time? Why do we still have to remind them about bulk processing? Why don't they leverage compile-time warnings? Why don't they instrument their code? Why don't they do better error handling?" etc.

I have a friend who has had great trouble finding a satisfying job. In one position after another, things start out great, but then go sour over time. In as gentle a manner as I could manage, I asked: "What is a common element among all these bad experiences? You are. So maybe in addition to being irritated with co-workers and managers, you should ask yourself how your behavior might have contributed to the problems - or at least how you might want to change for your next job to improve the chances of success."

Hey, it's just a thought. Just trying to be logical. OK, fine, get mad at me.

But doing the same thing over and over again, when you see it's not quite working as well as it should....don't you feel an itch, a desire, to change that same thing? I do.

If our users persistently have trouble fully leveraging our products, and we see this over and over again, perhaps we shouldn't wonder what's wrong with our users, but instead wonder about what we need to be doing differently.

But definitely we should not wonder what's wrong with us. :-)

Thanks, ODTUG!

I've been attending ODTUG conferences for a loooong time, and they have uniformly been great experiences.

But there's a dangerously comfortable feeling I feel when at Kscopes: it's like a reunion of some of my very best tech friends from around the world. It's so easy to spend most (all?) of my time catching up with "my people" - and then afterwards realize that there were also attendees I'd never met and could have learned from.

So I am pleased to say that I did meet a number of "next generation" Oracle Database developers (meaning they are at least 10 years younger than me). I was excited by their energy and their smarts, their desire to learn and apply that knowledge to solve their company's problems. I hope to follow up with at least some of them, and help them become the next generation of gurus for tens of thousands of others.

So, thanks ODTUG for the experience and the opportunity and all the friends!