Thursday, November 19, 2015

Programmers are Humans, Too - How to Get Crusty Developers to Change

What? You didn't know that? :-)

On a recent blog post, I received this comment:
Thanks for the video. You've answered my question and given us lots to talk over. Now, do you have any advice on getting those resistant to change, crusty, old, developers to buy in to change?
My immediate response was: incentives and fun.

So now it is time to elaborate a bit.

First of all, the hardest part of programming is not learning new features or absorbing the syntax of a programming language. After all, learning such a language is waaaaay easier than learning a human language - primarily because when we write code we are communicating with something that "thinks" quickly but is not particularly "smart." It, the computer however you want to define that these days, does what we tell it to do. Really, it does - no matter how many sci-fi movies you've watched that indicate otherwise.

Since a computer isn't very smart, we have to communicate with it using very formal, stiff syntax. That's a pain, but it also makes things easier.

Anyway, the hardest part of programming is that programming is performed by humans, and we are complicated, contradictory, flawed organisms. Thus, the social processes that envelop the programming tasks are usually a bigger challenge (and opportunity) than focusing on the technology itself (that is, go to another class on Advanced PL/SQL, and so on).

So when it comes to the question of "How do get change-resistant crusty, old programmers to change their behaviors?", the answers are too fold:
  1. Recognize that we are all change-resistant, not just the Wise Elders.
  2. We (humans in general) change when we are incentivized to change.
Appeals like "Do the right thing." or "Stop acting your age." or "Nobody's done that for decades." will not motivate a person to change their ways.

We change when we feel it is in our best interest to do so - or we change without quite realized it, because someone was very clever in how they approached things.

So my suggestions for being clever and incentivizing for change:
  1. Identify the behaviors you want to see changed.
  2. Figure out how to measure the change.
  3. Make it as easy as possible to make the change.
  4. Make the change as fun as possible.
  5. Reward us as and when we go through the transformation.
Let's look at an example. Suppose that we have a problem in our development team that code re-use is pitiful. Instead different members of the team write the same or similar stuff, over and over again.  How can we get the behavior to change? 

1. Identify the behaviors you want to see changed.

I want my team to find and re-use existing subprograms in package APIs, rather than rewrite stuff.

2. Figure out how to measure the change.

How can I tell when people are reusing code? Ah, the beauty of PL/SQL - code is stored in the database and metadata about that code is stored in various data dictionary views. Of most relevance here is PL/Scope and the ALL_IDENTIFIERS view. I can run a query that shows me how many places a particular or all subprograms are invoked.

I've attached a script at the bottom of this post that Jan-Hendrik van Heusden provided to me, which you might find helpful in this area.

I've also posted a PL/Scope helper package on LiveSQL that may help get you started on PL/Scope more generally. 

3. Make it as easy as possible to make the change.

Now, this is a tough one. How do we make our developers more aware of what they can re-use and how? I do not have a simple or "packaged" solution for you, so I will instead offer some ideas:

  • Standardize documentation of program headers so you can extract documentation of those subprograms
  • Build an intranet site (using Application Express!) to display this generated documentation or make it easy to search the code catalog (ALL_SOURCE)
  • Monthly code review: highlight new program units and potential code re-use opportunities
4. Make the change as fun as possible.

Yes, that's right, I used the word "fun" in the context of not just software, from changing software coding behaviors. Hmmmm. Well, how do people have fun? They play games!

And there is a game you can play - individually and as a group - that will help improve your ability to identify repetitions in code and therefore opportunities for reuse. That game is Set. Set is a wonderfully simple and elegant game. I encourage you to buy the actual deck of cards (but, yes, you can play it online and there is an app, of course).

Play this game with your kids (if they are young enough to still want to play games with you) and play it together as a team. It's fun - and it's all about enhancing your pattern analysis skills. 

5. Reward us as and when we go through the transformation.

Oh how we humans like our rewards! They can come in roughly two forms: tangible and intangible.

Examples of tangible rewards: gift cards, promotions, raises, etc.

Examples of intangible rewards: recognition, mostly.

In terms of promoting re-use, I could envision a monthly or quarterly awarding of prizes for (a) who built the code that is reused the most (likely a more senior developer) and (b) who is reusing code most effectively (likely a more junior developer).

How could you tell? Again, PL/Scope would help this.


Well, I hope you find these ideas helpful. No doubt about it, it is way harder to change human patterns of behavior than to pick up new technical skills.

Using PL/Scope to analyse identifier usages in PL/SQL

From: Jan-Hendrik van Heusden
Sent: Tuesday, September 17, 2013 10:49 AM
To: Steven Feuerstein
Subject: Query to analyse identifier usages in PL/SQL

Hi Steven,

I was working in a project where quite some obsolete code was being removed, 
and I wanted to know if certain methods, variables were actually used yet. 
I first made sure that everything was compiled with the appropriate settings (IDENTIFIERS:ALL).

Then I created the attached query, I thought you might be interested in it. 
Note that this query does of course not show all possible usages; 
it does not list usages in views or over database links, and it definitely 
does not list usage in external souces (sql files, queries made from external applications etc.).

Nevertheless I found it very useful for what it does (usage of PL/SQL 
identifiers within PL/SQL). Other PL/SQL developers may be interested, 
so feel free to use or publish it. You may also want to change things like 
the sorting order, feel free to do so.

Best regards, 
Jan-Hendrik van Heusden

  SELECT pl_id.declaring_owner,
            WHEN     pl_id.usage = 'DECLARATION'
                 AND pl_id.declared_type NOT IN ('PACKAGE', 'TRIGGER')
               COUNT (
                     WHEN pl_id.usage NOT IN ('DECLARATION', 'DEFINITION')
               OVER (PARTITION BY pl_id.signature)
            WHEN     pl_id.usage = 'DECLARATION'
                 AND pl_id.declared_type NOT IN ('PACKAGE',
               COUNT (CASE
                         WHEN pl_id.usage NOT IN ('DECLARATION',
               OVER (PARTITION BY pl_id.signature)
            WHEN     pl_id.usage = 'DECLARATION'
                 AND pl_id.declaring_object_type != 'TRIGGER'
                 AND pl_id.declared_type NOT IN ('PACKAGE', 'TRIGGER', 'LABEL')
               COUNT (pl_id.external_call) OVER (PARTITION BY pl_id.signature)
            WHEN UPPER (
                    SUBSTR (pl_id.referring_source_text,
                            LENGTH (pl_id.declared_name))) =
                  REGEXP_SUBSTR (
                     SUBSTR (pl_id.referring_source_text,
                             pl_id.referring_column - 1),
               || SUBSTR (pl_id.referring_source_text,
                          LENGTH (pl_id.declared_name))
               || REGEXP_SUBSTR (
                     SUBSTR (
                        pl_id.referring_column + LENGTH (pl_id.declared_name),
    FROM (WITH pl_declaration
               AS (SELECT decl.*,
                             WHEN TYPE IN ('FUNCTION', 'PROCEDURE')
                                DENSE_RANK ()
                                   OVER (PARTITION BY decl.owner,
                                         ORDER BY decl.line, decl.col)
                     FROM all_identifiers decl
                    WHERE decl.usage = 'DECLARATION' -- Leave out calls to Oracle internals
                          AND decl.owner != 'SYS')
          SELECT pld.owner declaring_owner,
                 pld.object_type declaring_object_type,
                 pld.object_name declaring_object_name,
                 pld.line declaring_line,
                 pld.TYPE declared_type,
                 i.owner referring_owner,
                 i.object_type referring_object_type,
                 i.object_name referring_object_name,
                    WHEN    i.object_name != pld.object_name
                         OR i.owner != pld.owner
                 i.line referring_line,
                 i.col referring_column,
                 (SELECT s.text
                    FROM all_source s
                   WHERE     s.owner = i.owner
                         AND s.TYPE = i.object_type
                         AND = i.object_name
                         AND s.line = i.line)
            FROM pl_declaration pld
                 JOIN all_identifiers i ON i.signature = pld.signature) pl_id
   WHERE     -- leave out obvious references to a package, these are always redundant
             -- because you can not just refer to a packagewithout referring
             -- to a subprogram, constant etc.; these are included in the query result anyway
             NOT (pl_id.declared_type = 'PACKAGE' AND pl_id.usage = 'REFERENCE')
         -- leave out obvious self references to a type, these are always redundant
         AND NOT (    pl_id.declared_type = 'TYPE'
                  AND pl_id.usage = 'REFERENCE'
                  AND pl_id.declaring_owner = pl_id.referring_owner
                  AND pl_id.declaring_object_name = pl_id.referring_object_name
                  AND pl_id.declaring_object_type = 'TYPE'
                  AND pl_id.referring_object_type = 'TYPE')
         AND (   pl_id.declaring_owner IN (USER,
                                           SYS_CONTEXT ('userenv',
              OR pl_id.referring_owner IN (USER,
                                           SYS_CONTEXT ('userenv',
ORDER BY pl_id.declaring_owner,
         CASE pl_id.declaring_object_type
            WHEN 'PACKAGE' THEN 10
            WHEN 'PACKAGE BODY' THEN 20
            WHEN 'TYPE' THEN 30
            WHEN 'TYPE BODY' THEN 40
            WHEN 'FUNCTION' THEN 50
            WHEN 'PROCEDURE' THEN 60
            WHEN 'TRIGGER' THEN 70
            ELSE 900
         CASE WHEN pl_id.declared_type LIKE 'FORMAL%' THEN 20 ELSE 10 END,
         CASE pl_id.declared_type
            WHEN 'PACKAGE' THEN 10
            WHEN 'PACKAGE BODY' THEN 15
            WHEN 'TYPE' THEN 20
            WHEN 'TYPE BODY' THEN 20
            WHEN 'FUNCTION' THEN 20
            WHEN 'PROCEDURE' THEN 20
            WHEN 'CURSOR' THEN 30
            WHEN 'REFCURSOR' THEN 40
            WHEN 'CONSTANT' THEN 50
            WHEN 'NESTED TABLE' THEN 60
            WHEN 'VARRAY' THEN 70
            WHEN 'RECORD' THEN 80
            WHEN 'VARIABLE' THEN 90
            ELSE 500
         CASE pl_id.usage
            WHEN 'DECLARATION' THEN 10
            WHEN 'DEFINITION' THEN 20
            WHEN 'CALL' THEN 600
            WHEN 'ASSIGNMENT' THEN 600
            WHEN 'REFERENCE' THEN 900
            ELSE 500
         pl_id.external_call ASC NULLS FIRST,

No More PL/SQL Obsession by Steven Feuerstein on ToadWorld

A month ago, I received this email:
Hi Steven, just to inform you that your "PL/SQL Obsession" page is out of work, answers "Page Not Found"  Maybe Dell has blocked you?
So I thought I would post a note on my blog to clarify matters.

For many years, Quest and then Dell "hosted" my online PL/SQL resources at

I had lots of fun with that page, and am especially grateful to Steve Hilker for helping me keep it current and useful for thousands of PL/SQL developers.

We came up with what I still feel is the best way to describe my relationship to PL/SQL:

PL/SQL Obsession

And for more than a year after I joined Oracle (in March 2014), Dell and I agreed that we should keep the PL/SQL Obsession site intact, with all of my content, even that which they did not own.

But all things - good or otherwise - must come to an end. It's confusing to have me working at Oracle but still seeming to be "at" Dell inside ToadWorld.

So we, ahem, refactored the PL/SQL Obsession page so that it continues to offer PL/SQL resources, but is not tied to me personally, and we turned back on the redirect from, so that you will no longer see a Page Not Found error.

So the bottom line is that you will still find it handy to head over to for PL/SQL resources, but you will find the most current reflection of my PL/SQL activities at:

PL/SQL Home Page - Best starting point
PL/SQL Challenge - PL/SQL Quizzes (and more)
My Books on PL/SQL - published by O'Reilly Media

And of course there are lots of other great resources for Oracle Database developers at:

PL/SQL and EBR Blog - by our distinguished product manager, Bryn Llewellyn
PL/SQL on OTN - with content and the very active discussion forum
ORACLE-BASE - by the irrepressible Tim Hall - fantastic articles and scripts by Adrian Billington
PL/SQL Scripts on LiveSQL - 24x7 access to Oracle Database 12c + code library
Ask Tom - you can still ask, but questions are now answered by Chris Saxon and Connor McDonald of the Oracle Database Developer Advocates team

Thursday, November 12, 2015

Content Management for Oracle Database Developers - what do you need?

Yes, yes, I know: another post with little or nothing to do with PL/SQL. My apologies. But remember, I do offer
So please don't say "But what have you done for me lately?" :-)

'Cause then I would feel bad that this post is about me asking you for something.

I am involved here at Oracle with both content generation (see above) and working on community-oriented apps that make it easier for you to access expertise and resources on Oracle Database developer-related topics, including:
As many of you are probably aware, content is great stuff ("Content is King"), but and especially for technology-related content, it can get "stale" - what is accurate today is misleading tomorrow; what is good advice today is a dead end tomorrow. 

In addition, you need to have really good metadata about your content to make it easy for users to find what they need quickly (you say "Google", I say "Well, maybe, sometimes that's good enough...").

So I am working on a project to strengthen our content metadata and (eventually) engage our community to help us ensure that resources available to that community are as accurate and useful as possible.

To that end, I thought I would ask you
  • What metadata should we keep track of?
  • What features could we, should we add to our community content applications/sites to improve their quality?
Current Ideas on Content Metadata
  • Author: who produced the content
  • URL of content
  • Related to which product(s)?
  • Which specific feature(s) within a product?
  • Minimum version to which it applies
  • Maximum version after which it no longer applies
  • Expertise level (beginner, intermediate, advanced)
  • Link to product documentation
What else can you think of?

Additional Features?

What features could we, should we add to our community content applications/sites to improve their quality?

Thanks in advance for any spare brain cells you devote to this!


Wednesday, November 4, 2015

Reflections on Oracle Open World 2015

Oracle Corporation is a massive enterprise - over 130,000 employees, big shiny headquarters, hundreds of products (software and hardware), etc. - and Oracle Open World reflects that massiveness. I suppose there may be some people who can ingest all of that and make sense of it (besides our top executives, for whom that is in part their job), but I tend to focus lots more narrowly in the Oracle Database world and the developer community that thrives around that flagship product.

My Personal Highlight: Announcing Winners of the 2015 Devvys

So first and foremost the highlight of OOW15 was our YesSQL event, in which we announced the winners of the 2015 Devvys - the Oracle Database Developer Choice Awards. Andy Mendelsohn was kind enough to make room in his busy schedule to stop by, share some stories (this year, he focused on Big Data SQL, and how that evolved so smoothly out of existing technologies) and let everyone know how important application developers are to the continuing success of Oracle Database.

Here's Andy with the winners and finalists of the Devvys, who attended OOW15:

Yes, they are that excited and it was that much fun. Many of the winners and finalists could not attend OOW15, but all were saluted at the event:

Here's Michelle Kolbe (winner in the Database Design category) showing the beautifully engraved chunk of crystal all winners will be receiving in the next month:

We got approval for the Dev Choice Awards program in April 2015. We launched the program in June, at Kscope16, and gave out the first round of awards four months later. Whew. My head is still spinning. Many of you likely work at a large corporation, so you may have some idea of what it takes to move that quickly. I am very pleased with the outcome - and extremely grateful to Todd Trichler and Laura Ramsey for all their hard work. 

Now it's time to evaluate the program and figure out how to make it so much better in the future (for example, in case any of you were wondering: no down voting). Oh, yes, and publish a video of the program and event. We're working hard on that now.

But it's true - there was more to OOW15 than the Devvys. :-)

PL/SQL (and SQL) Going Strong

Even I, a human fully obsessed with the PL/SQL language, can accept that this technology plays a very small part in the overall "picture" of Oracle Open World. Still there were a number of excellent presentations, enthusiastically received.

Bryn Llewellyn, Distinguished Product Manager for PL/SQL and Edition-based Redefinition, rolled out a powerful, new talk: Why Use PL/SQL? in which he offered "unassailable arguments" for PL/SQL's place in the world of applications built on Oracle Database, summed up as the Thick Database Paradigm. I urge you to read the whitepaper that forms the foundation for his talk and put his arguments to use when discussing with management and UI developers the architecture for your next application. Bryn even offered up some graphics!

Bryn also gave a talk titled Transforming one table to another: SQL or PL/SQL?, in which he examines Tom Kyte's mantra, summarized as "If you can do it in SQL, do so; only if you can’t, do it in PL/SQL", and then concludes that at least for some scenarios, "It is better to implement the core logic of the algorithms in PL/SQL, even when a single insert... select approach is feasible. My preference is inevitably subjective – and reflects some rather subtle considerations. Your opinion might be different." As Bryn points it, his conclusion involves some subtlety. He is not suggesting you neglect your SQL skills (or the application of them) and write lots and lots of PL/SQL. He is advocating for a careful decision, at least when the SQL solution becomes extremely complicated, and by implication hard to maintain.

Besides YesSQL and the Devvys, my main involvement at OOW15 was to run a panel on PL/SQL Optimization for Performance and Maintainability. Bryn and I were joined by Martin Buechi, Kim Berg Hansen, and Marcelle Kratochvil. The room was full (I'd like to say "packed," but in all honesty there were a few unoccupied seats) and the questions engaging. And, of course, with the very last question, suddenly a half dozen people wanted to speak at once - and we were out of time. Sigh...

My impression is that attendees enjoy a break from 45 minutes of "download" (listening to an often sped-up delivery of a talk) to one in which they can ask questions and listen to some back-and-forth from the panelists.

This impression was reinforced by a similar panel on SQL optimization later the same day. Attendees and panel members all were busy asking, answering, discussing and - when all was said and done - all but fighting to get their hands on the remainder of the special K.I.S.S. (Keep It Simply SQL) t-shirts Connor brought with him from Perth:

I greatly enjoyed watching Connor (who filled the last opening on our Developer Advocates Team). I hadn't caught any of his presentations in the past, because they were more database administration, rather than development, focused. Here he is answering one a question (sorry about my shaky hand):

The Bigger Picture

Two kinds of bigger:

1. Community: stepping away from the technology, one of the things I enjoy most about OpenWorlds is the opportunity to chat with members of the developer community. This year, I worked especially hard at making time to meet with lots of ACEs and ACE Directors to get their feedback on our various community-oriented initiatives (I was also very pleased to finally meet Solomon Yakobson, a Grand Titan on the OTN SQL and PL/SQL Forum with 289,155 points, and someone who has helped me out a lot over the years). We still have lots to do, but it does seem like many of you "out there" are noticing a difference. I hope so, 'cause we sure are trying hard!

2. Oracle technology: obviously, while Oracle Database remains the flagship product of Oracle Corporation, it is also "just" one component in an ever-growing array of offerings. Just as obviously, Oracle is in the midst of a far-reaching "pivot" to the Cloud. This isn't the first time Oracle's undergone a big change and it likely is not going to be the last. Given the pace of change in the technology world, however, there is more pressure on Oracle than ever before to move fast and get it right. My impression overall, both as a person watching the OOW keynotes (view from the "outside") and as an insider with some albeit limited knowledge of all the various projects, is that we are very effectively leveraging our existing foundation, especially Oracle Database, while we build the necessary new layers simultaneously.

It is, of course, too soon to tell if we will one day be the Cloud Monster some in the company say we will be, but I feel quite confident that we will be able to provide a solid, competitive migration path for our enormous installed base, as well as create an inviting platform for developers on which to create new apps.