Tuesday, December 15, 2015

PL/SQL Brain Teaser: Find all the hard-codings!

We all know that hard-coding is a bad thing (well, maybe not all of us. At one training several years past, I asked the audience "Does anyone think hard-coding is a good idea?" and one person raised his hand. Um, OK).

You know hard-coding: when you say to yourself "This is never going to change." and so you put the "value" directly in your code, over and over again.

I put "value" in quotes, because many developers think simply of hard-coded literal values when they think of hard-coding. But I think there are many more ways that hard-coding can seep into our programs.

So I invite you to help find all the hard-codings in the procedure below.

Here's the rule: you can only identify ONE HARD-CODING in each comment. I will delete a submission with > 1. It'll be more fun that way. Promise!

I will give everyone a couple of days to submit your ideas, then offer my own views on the subject.

Monday, December 7, 2015

LOG ERRORS: Suppress row-level errors in DML (from PL/SQL Challenge)

Last week, several hundred players tested their knowledge of the LOG ERRORS feature, and associated with it, the DBMS_ERRLOG package. Check out the quiz here, but feel free to explore the topic below!

Use LOG ERRORS to suppress row-level errors from within the SQL engine, instead writing information to the error log table. In contrast, if you use SAVE EXCEPTIONS with FORALL, you will suppress statement-level errors, but all changes made to rows identified by that statement are rolled back.

Suppose I execute the following statements:

CREATE TABLE plch_employees
   employee_id   INTEGER PRIMARY KEY,
   last_name     VARCHAR2 (100),
   salary        NUMBER (3)

   INSERT INTO plch_employees
        VALUES (100, 'Sumac', 100);

   INSERT INTO plch_employees
        VALUES (200, 'Birch', 50);

   INSERT INTO plch_employees
        VALUES (300, 'Alder', 200);


   DBMS_ERRLOG.create_error_log (dml_table_name => 'PLCH_EMPLOYEES');

I then write the following incomplete block:

   TYPE two_vals_rt IS RECORD
      lowval   NUMBER,
      hival    NUMBER

   TYPE ids_t IS TABLE OF two_vals_rt;

   l_ids     ids_t := ids_t (NULL, NULL);

   l_total   NUMBER;

   PROCEDURE show_sum IS
      SELECT SUM (salary) INTO l_total FROM plch_employees;
      DBMS_OUTPUT.put_line (l_total);
   l_ids (1).lowval := 290;
   l_ids (1).hival := 500;
   l_ids (2).lowval := 75;
   l_ids (2).hival := 275;



Which of the choices provide a replacement for ##REPLACE## so that after the resulting block executes, "800" is displayed?

When you execute a non-SELECT DML statement against a table and an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. You can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a LOG ERRORS statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. Afterwards, you can query the contents of the table to either move your error information into the application log or take corrective action on the rows that caused problems.

The error logging clause allows you to specify an upper limit for the number of errors that are allowed to be logged for that statement. If this number is exceeded, then the statement terminates with an error and all its changes are rolled back. Alternatively, you can specify REJECT LIMIT UNLIMITED to allow any number of errors to be logged.

If Oracle is able to log all the eventual errors without exceeding the limit allowed, the statement can succeed and the changes that were successful are not rolled back.

The error logging is always performed as an autonomous transaction, so that the logged errors are not rolled back when a DML statement fails and/or is rolled back, thus allowing them to be used for checking and error correction.

When is it a good idea to implement error log tables?

Certainly in the development and test phases of a project, they are an excellent mechanism for catching issues that were not captured as part of the application requirements.

In addition, if you are performing "bulk" changes to a table in which a large number of rows are modified by a single DML statement, you may not want to "lose" all changes to rows when a change to one fails. In this case, LOG ERRORS is the only way to go.

Are error log tables needed in production?

Can you safely say that all accepted requirements were correctly implemented, that the requirements are complete and that nothing was left out? If the answer is Yes, then you don't need those tables (nothing can go wrong). If the answer is No, they may help you debug issues as they pop up.

You can implement error log tables (or equivalents thereof) as part of instrumentation that documents execution of application logic, and in many cases that is probably the best way to go. But, for tables that are subjected to nothing but simple DML, yet have constraints and therefore the ability to cause errors, error log tables may be the easiest and "cheapest" way of documenting when something didn't work out the way we thought it would.

Tuesday, December 1, 2015

SQL%ROWCOUNT: What/how much did my SQL statement do?

This post is courtesy of the PL/SQL Challenge quiz ending 27 November 2015:

If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query.

Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction. Therefore: When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point. When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.

Here's the code for the quiz - see how you do!

And of course sign up to take each weekly quiz as it is released (you can even compete for international rankings).

I execute the following statements (which you can easily run yourself on LiveSQL):

CREATE TABLE plch_flowers
   nm   VARCHAR2 (100) UNIQUE

   INSERT INTO plch_flowers
        VALUES (1, 'Orchid');

   INSERT INTO plch_flowers
        VALUES (2, 'Rose');


Which of the choices result in "RC=1" being displayed on the screen after execution?

   l_id   INTEGER;
   SELECT id
     INTO l_id
     FROM plch_flowers
    WHERE nm = 'Orchid';

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);

   l_id   INTEGER;
   SELECT id INTO l_id FROM plch_flowers;
      DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);

   INSERT INTO plch_flowers
      SELECT id * 3, UPPER (nm) FROM plch_flowers;

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);

   l_id   INTEGER;
   INSERT INTO plch_flowers
        VALUES (3, 'Tulip');

   SAVEPOINT inserted_row;

   INSERT INTO plch_flowers
        VALUES (3, 'Lotus');
      ROLLBACK TO inserted_row;
      DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);

   l_id   INTEGER;

   PROCEDURE insert_and_save
      INSERT INTO plch_flowers
         SELECT id * 3, UPPER (nm) FROM plch_flowers;

   INSERT INTO plch_flowers
        VALUES (10, 'Ambrosia');

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);

Background on SQL%ROWCOUNT

Static or embedded SQL are SQL statements that are written natively into your PL/SQL programs (as opposed to defining them as expressions for execution as dynamic SQL). An implicit cursor is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement. You cannot control an implicit cursor, but you can get information from its attributes. Oracle defines a number of attributes of implicit cursors, whose value can be obtained through the SQL%attribute syntax.

SQL%attribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQL%attribute is NULL. An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another SELECT or DML statement runs. The most recently run SELECT or DML statement might be in a different scope (another subprogram call that has now completed, for example).

To save an attribute value for later use, assign it to a local variable immediately. Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before you can test it. SQL%ROWCOUNT returns NULL if no SELECT or DML statement has run. Otherwise, it returns the number of rows returned by a SELECT statement or affected by a DML statement (a PLS_INTEGER).

 If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query. Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction.

Therefore: When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point. When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.

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,
                 pld.name declared_name,
                 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 s.name = 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, http://www.toadworld.com/sf 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 http://www.toadworld.com/sf:

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 toadworld.com/SF, 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 toadworld.com/SF 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
oracle-developer.net - 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.

Thursday, October 22, 2015

Most important PL/SQL coding standards?

Received this request today via email:
I was at the MOUG Fall Conference in Chicago a few weeks ago and enjoyed your presentation on the result cache. It’s already paying dividends for us. Thanks for coming and sharing. I have a question for you, and maybe you’ve already written about this and can point me toward an article or blog post. We will be revising our coding standards, which are rather loose and largely ignored, and I want to try to promote those that will give us the most benefit. What is your top ten list of the most important coding standards to implement? Thanks for your time, and I hope to see you at OOW. It will be my first trip there.
And I replied:
I love these kinds of requests, because it gives me an opportunity to take a fresh look and publish something on my blog. :-) I don’t think I will be able to get back to you until after OOW, hope that works OK. Please do come up and say hi if you see me!
And then I thought: wait a minute, let's ask all my fellow Oracle Database developers "out there", see what all of you think. 

So here I am, there you are - and I'd love to hear from you:

What do you think are the most important coding standards for PL/SQL developers to follow?

By the way, check out some existing, published standards and frameworks here

Nov 6 update: it's been a busy post-OOW week, so I haven't been able to formulate my complete answer yet. I like lots of the ideas submitted in the comments. But I have come up with nine keywords to drive my "most important." They are:

1. MAXSQL - maximize use of SQL first and foremost
2. SPOD  - single point of definition
3. TRACE - production-available application-level tracing
4. LOG - consistent, encapsulated error logging
5. BULK - avoid row by row
6. OBVIOUS - make your code tell its own story, comment when it can't
7. NESTPROG - use nested subprograms
8. DECLARE - use declarative features of language
9. WARN - use compile-time warnings

November 12 2015: I have published an 8 minute video explaining these Top Nine. Hope you like it!

Tuesday, October 6, 2015

Execute any SQL statement from within a Application Express app? Sure, why not? Um.....

Received this question today:
We are planning to develop  a product with APEX and is it possible to  execute free sql inside an apex application? I mean is it possible to have a SQL execution window inside the APEX application like we execute inside an Oracle SQL developer?

Sure, why not? 

Well, actually, there are all sorts of reasons "why not", right?

But, yes, it is certainly technically possible to do this - and not very difficult. 
  • Create a page in Application Express.
  • Add a Text Area item and give your users lots of room to write lots of SQL. 
  • Add an Execute button.
  • Create a process that fires on that button, and contains code like this:
         EXECUTE IMMEDIATE :P1000_your_sql;

Then your users will then be able to do all sorts of things:
  • Create a new table (!)
  • Truncate an existing table (!!)
  • Set values of columns to NULL (!!!)
  • etc.
They will not be able to:
  • Execute a SELECT and see the results. For that you need an INTO clause.
  • Execute a DML statement that requires bind variables. For that you need a USING clause (or concatenation).
But they will be able to screw up your application really well!

So, seriously, you do NOT want to do that! 

Suppose, however, that you wanted to let a power user execute an ad-hoc single value query and see the result? In that case, something like this might almost be reasonable:

   value_out VARCHAR2(32767);
   EXECUTE IMMEDIATE :P1000_your_sql INTO value_out;


   :P1000_your_sql := value_out;

The INTO clause means that you must execute a single-value, single-row select.

The ROLLBACK ensures that any changes you try to sneak in will be rolled back....well, unless your power user has truly super powers and was able to previously create an autonomous transaction function and then call it in the query.

But if you've got a user who can do that, you've got bigger problems than anything I can address in this somewhat tongue-in-cheek post!

Tuesday, September 29, 2015

YesSQL Celebration 2015: Andy Mendelsohn! Dev Choice Awards!

YesSQL Celebration OOW15
October 26 - 4:00 - 5:30 PM
Park Central Hotel - Metropolitan III Room

Last year, we held our first-ever YesSQL Celebration at OOW14. It was precisely that: a celebration. It started with fascinating stories from Andy Mendelsohn, EVP of Server Technologies (aka, Oracle Database and more), about early days at Oracle, then moved on to presentations from various development teams ("Meet the folks who write this amazing software!"). Highlights include Andrew Witkowski, SQL Architect, in a NoSQL pirate disguise, Mike Hichwa talking about the origins of Application Express, and Mohamed Zait and the optimizer team in a highly amusing and self-produced video.

This year, we will celebrate SQL (and PL/SQL, and related appdev technologies in Oracle Database) at OOW once again, but the event will be slightly different.

The agenda this year is simpler, different and very exciting, because in addition to Andy Mendelsohn as our featured speaker (more time to tell more stories about life at Oracle and the amazing SQL language!), we will be announcing the winners of the inaugural round of Oracle Database Developer Choice Awards: 

Read more about these awards here, but the most important thing to know is that:

You decide the winners - chosen by popular vote...so VOTE!

Please take a few minutes to check out the finalists and vote for your favorites. Encourage your co-workers to do the same.

And if you are attending OOW15, please take a break from the normal craziness of the week, to celebrate with us both the great appdev technologies of Oracle Database and, more importantly, the amazing technologists around the world who share their knowledge and grow the community!