Thursday, December 11, 2014

Three New Members of the Oracle Database Evangelist Team

A long, long time ago....I announced that I had been given the honor of assembling a team of evangelists, whose job would be to promote Oracle Database as an application development platform.

In other words, make sure that current and future users fully leverage all the amazing features for developers that are baked into Oracle Database, such as SQL, PL/SQL, Oracle Text, Oracle Spatial, edition-based redefinition and more.

I am very pleased to announce that my team has now swelled dramatically from one person (me) to four, with one more to come on board in early 2015.

I will make a more "formal" announcement of our team and our plans in Q1 2015, but for now, I did want to share the joyful feeling I feel.

Drum roll, please....


Todd Trichler, Community Manager

Todd got his start at Oracle working with Partners in Alliances. For over a decade he has been focused on technology outreach, working closely with development to drive "grass-roots" engagements in both Oracle and open source developer communities. He is passionate about family and building community, loves meeting new people and learning new technologies, while sharing what he's learned with others.









Natalka Roshak, SQL Evangelist


It all started with an innocuous student job as a data analyst. It wasn't long before Natalka was firmly hooked on SQL. Since then she's been a developer, a DBA, a RAC guru, and sometimes all of the above at once. She's excited to have an opportunity to share her passion for and knowledge about SQL with others, especially those new to relational technology.








Dan McGhan,  Javascript/HTML5 Evangelist

Dan suffers from Compulsive Programming Disorder, which is believed to be linked to his balding. Having started his development career in the land of MySQL and PHP, he was only too happy to have stumbled upon Oracle Application Express. Since then, he’s dedicated his programming efforts to learning more about Oracle and web based technologies in general. These days he enjoys sharing the passion he's developed for JavaScript and HTML5 with others.

Dan shared his decision to join Oracle on his blog. Read more here.

Tim Hall: PL/SQL presenter extraordinarie

If it's PL/SQL, I'm interested. And if it's PL/SQL involved in winning some sort of award, well, I am downright excited.

So I was very pleased indeed to see that Tim Hall of Oracle-Base fame won the UK Best Speaker award at UKOUG 2014 for his talk on Improving the Performance of PL/SQL Function Calls from SQL.

Tim is an engaging writer and speaker, but of course that's not while he won the award. It was because the topic he chose was so incredibly important and exciting.

Still, I thought it would at least be polite to say to Tim:

Congratulations, Tim! Keep up the great work!

Tim is one of those Oracle experts (and an ACE Director to boot) who is incredibly generous with his time and knowledge, as anyone who has spent any time on Oracle-Base.com will know. He can also be quite hilarious, as you can tell from his recent blog post on UKOUG 2014.

Here are links to the slide deck, demo scripts and an article all focused on improving the performance of PL/SQL function calls from SQL:

Slides: http://oracle-base.com/workshops/efficient-function-calls/EfficientFunctionCalls.ppt
Demos: http://oracle-base.com/workshops/efficient-function-calls/demos.zip
Article: http://oracle-base.com/articles/misc/efficient-function-calls-from-sql.php


Tuesday, December 2, 2014

SQL is Dead (NOT), Long Live SQL!

I participated in an Oracle Academy Ask the Experts webcast in November 2014, with the title "The Code Under the Covers, aka the Database Under the App."

The objective of the webcast was to encourage professors to teach, and students to learn, SQL (the Structured Query Language) and PL/SQL (Procedural Language extensions to SQL). In preparing for the talk, I found myself thinking about Platonic Idealism. Strange, eh?

So I thought I'd share some of these thoughts on my blog. I hope you find them interesting enough to share and comment.

First, it is very much worth noting that while SQL is "old" - the first commercial SQL database was released by Oracle waaaay back in 1979 -  it's not going anywhere. In fact, it is getting more important than ever, more entrenched, and more widely used.

And I think that part of the reason for the stickiness of SQL has to do with the aforementioned Platonic Idealism, or to be more precise, it has to do with how different the underlying philosophy of SQL is from that of Platonic Idealism.

Platonic Idealism is summarized on Wikipedia as follows:

"A particular tree, with a branch or two missing, possibly alive, possibly dead, and with the initials of two lovers carved into its bark, is distinct from the abstract form of Tree-ness. A Tree is the ideal that each of us holds that allows us to identify the imperfect reflections of trees all around us."

Surely you remember that from your Uni philosophy course? I did, sort of. And then the following idea came to me a few weeks ago: 

Wait a minute! Platonic idealism sounds an awful lot like like object-orientation. You have a class, which is the "perfect" and "ideal" thing. Then you have instances of that class and sub-classes of that class, all of which may vary in some way from the ideal. 

Don't they seem quite similar? The Platonic Ideal and the Class. It was even mentioned at Wikipedia

"The language for much of the article talks about 'instantiations', inherence, forms, etc... Sounds very much like inheritance/etc... from computer science. Perhaps this is deliberate, perhaps written by a comp. sci. person, perhaps it's totally my perception."

So here's the thing: I don't believe that humans see the world according to Platonic Idealism. In other words, while object orientation may have some value inside computer systems, I don't think that humans exist in the world in an O-O fashion.

When I stand in the forest down the street from my house and look at the trees, I don't see them as variations from some ideal Tree. There is no such thing in the world or in my head. Instead, I see lots of discrete entities that share characteristics.

Here's another way to put it:

I ingest data through my senses (see the different kinds of bark on the trees, hear the winds rustling through the leaves, taste the air blowing off the river), and my brain identifies patterns. It then uses those patterns to develop strategies for surviving, reproducing and thriving. 

So I can look at an invasive buckthorn (which I cut down by the hundreds each week) and a big, old oak tree and think to myself: "They are both trees." Which really means that they can be grouped together by common attributes.

They are, in short, a set.

I believe that humans naturally think about sets of things in the world as a basic, evolved mental strategy for getting by in the world. 

And if that is true, it is very easy to see why SQL was such a remarkable breakthrough back in the 70s (kudos to Codd, Date, IBM research labs, and Ellison). And why it played (and plays) such a critical role in the Information, Internet, Mobile and Internet of Things Eras.

SQL synchs up so well with how our brain naturally operate that it is hard to imagine another data language that is different enough to supplant it. A much more likely scenario is that the SQL language will be changed to meet new requirements (as will the underlying SQL engines, such as Oracle Database, MySQL and so on).

I'm not really arguing that SQL is "forever." I expect that at some point, the whole computing paradigm will shift in ways we can't even imagine today, and SQL then becomes irrelevant somehow.

But as long as we write code the way we do today, still build apps the way we do, still need databases to hold data, we'll find ourselves relying on an ever-more-powerful SQL language to manipulate our data.

SQL is Dead (NOT), Long Live SQL!

Tuesday, November 25, 2014

Feedback on my Planning for Trouble Oracle Magazine article

Received feedback today from James S on my latest Oracle Magazine article, Planning for Trouble. I will respond here (there is no mechanism for comments/discussion at the Oracle Magazine page). If you haven't yet read it, I suggest you do so before proceeding with this post.

Comment 1. This is regarding exception handling.  Let’s say the client calls procedure A, and A calls B, B calls C. Are you suggesting we add a “When others” + error logging logic in each level? Does it mean one error will be logged three times? (because it is captured and re-raised each time).

Good point, James. I make the argument in the article that you should trap the exception as close as possible to where it was raised, so that you can log the values of local variables that may help you diagnose the problem. 

[Here's reinforcement of that point (and other great points about doing effective execution tracing) - they call it contextual logging: http://www.stackify.com/smarter-errors-logs-putting-data-work

But if you apply this rule to A calls B calls C, and C raises an exception, then you could well have three rows inserted into your error log. 

So, first, ask yourself: is this a problem? Generally more info is better than less, but of course you can overwhelmed by too much data.

If you decide that you really don't want that, then you could get "fancy" and in your logging routine set a flag in a package that indicates "already logged". Then in outer blocks, your logger could check the flag and only insert into the log table if it's not already logged.

Does anyone do this now?

Comment 2. I don’t think there’s an issue with the Miguel way (only capture at level A). If you don’t know what will happen, just let it happen. The outer layer or the client can log it and hide it and display a friendly information on the UI.

Well, I guess I was not able to convince James of the value of logging local variable values. What do you think? Is this valuable to you? Am I missing something?

Comment 3: And I don’t think “ORA-00942: table or view does not exist” is a good example. If you try to access a non-existing table your code won’t compile at all (unless you use dynamic sql). And I think Oracle should tell us what is missing. Prior to 10g when you insert a string longer than column width it doesn’t  tell you which column goes wrong! Now the information makes more sense, it not only tells you the column name but also the column width and actual length of value.

Well, your "unless" certainly indicates it could be a runtime issue for logging, and we certainly do write lots of dynamic SQL, these days. As for "I think Oracle should tell us what is missing", I can only tell you that Oracle is following well-accepted guidelines for keeping databases secure. That's specifically for this error. Obviously, as you point out, in other cases, Oracle does give us very excellent context-specific information in the error message. 

Comment 4: Regarding the “assignment in the declaration section”: do we have a choice for constants? It has to be in the declaration right?

You are correct. If you are declaring a constant, it must be done in the declaration section. So you need to assess the risk: am I assigning a value that could have wildly different sizes or format? If so and if I need to trap the exception inside that block and log the problem, then I need to remove the CONSTANT keyword. if you are assigning a literal, then you should consider it part of your job as a disciplined professional to READ YOUR CODE and make sure you are not making a blooper as in:

c_number CONSTANT NUMBER(3) := 1234;

Wednesday, November 19, 2014

Planning for trouble: comments on my latest Oracle Magazine article.

In my November/December 2014 article for Oracle Magazine, Planning for Trouble, I urge developers to realize that regardless of best intentions, not everything related to our apps is under our control, and we need to assume that trouble might be coming our way.

I received today, the following comments from Gary Malandro, which I thought you might enjoy reading:

Enjoyed your article in Oracle Magazine, and I have a few comments.

1.       You mentioned “Documents that spell out naming conventions…fit very nicely inside desk drawers”.  On our team, we have a number of policies that include the requirements for a technical design document, a software change request, source control, and some regarding style.  First thought upon hearing that is probably what-a-load-of-bureaucratic-nonsense.  Well, there are reasons for creating and enforcing these standards.  Compliance for one.  Another is we had code going into production systems that performed poorly, contained logic errors, was difficult to understand, already existed, no exception handling, etc.  With standards, we are able to peer review designs and code to make sure it is understood.  These are necessary when doing reviews otherwise it is a matter of style between developers – what 1 developer thinks is cool another finds difficult to understand.  Anyway, since we’ve implemented these policies our defect rate to test and error rate to production has improved considerably.  Besides PLSQL, we also develop in Java and .Net.  I’ve heard all the excuses as to the evil of standards and documentation (and probably said them many times myself), but the people who say this typically have to rework their code multiple times.  So pay now or pay later. 

2.       In my previous role I was supporting/developing a large volume of PL/SQL code.  I created a separate package (XXSA_DEBUG) which contained procedures to output data in various ways (a very, very, very poor man’s log4j).  In procedures I would include a way to get a debug variable and could either decide to call the output package, or just directly call it and let it decide where/how to output.   Production access is limited, so being able to change the debug flag using an application, or even running with a stub program, greatly aided when troubleshooting was required.

3.       Another thing we enforce is exception handling with clear messages.  If it’s something going to a log file or table (aimed for the developer) it should contain the procedure name and where the error occurred.  This allows any developer to trace where the error occurred.  Too many times we had log files loaded with “Failed”, and no explanation.  And thousands of lines of code that contained “Failed” for the output message. 

Thanks, Gary, for taking the time to share your experience. I would be very happy to hear from others!

Thursday, November 13, 2014

FORALLs and COMMITs

Received this note today from Debbie B:

I attended your Turbo Charge PL/SQL seminar today. I still have a question about where to put COMMIT. I’m using 10g. See (pseudo) code below.

Say I have 50,000 records and the LIMIT is 100. If an exception is thrown:
  • Do I need a COMMIT in the WHEN clauses so successful DML gets committed, then loop processing will continue?
  • I was thinking each DML needed it’s own BEGIN EXCEPTION END block so I would know if the error happened due to the insert or the update and could log the appropriate error. Is this wrong?

  OPEN v_cursor;
  LOOP
    FETCH v_cursor BULK COLLECT INTO data_array LIMIT i_limit;
    EXIT WHEN data_array.COUNT = 0;

    BEGIN
      FORALL i IN 1.. data_array.COUNT SAVE EXCEPTIONS
        INSERT INTO some_table VALUES data_array (i);
        COMMIT;
    EXCEPTION
        WHEN dml_errors THEN
             log_error;
             COMMIT;
             /* Don't RAISE, so execution will continue */
        WHEN OTHERS
             log_error;
             COMMIT;
             /* Don't RAISE, so execution will continue */
    END;

    BEGIN
      FORALL i IN 1.. data_array.COUNT SAVE EXCEPTIONS
        UPDATE some_table SET…
        COMMIT;
    EXCEPTION
        WHEN dml_errors THEN
             log_error;
             COMMIT;
             /* Don't RAISE, so execution will continue */
        WHEN OTHERS
             log_error;
             COMMIT;
             /* Don't RAISE, so execution will continue */
    END
  END LOOP;
  COMMIT;
  CLOSE v_cursor;

So many commits, so little time. 

Before addressing the specifics of this code, let's consider a more general question:

When and where should I put commits in my code?

Answer: How the heck should I know? Or, perhaps a little more politely: What is your transaction?

When you commit, you are really saying: I have finished making all changes needed for this transaction. Now it's time to save them, all together, all at once.

When we write back-end code that acts as an API to front end (user-driven) code, we usually don't have any commits. That's because we leave it up to the user to decide when they are done and ready to save their changes.

When we write code implementing backend processes not controlled by the user, then we do usually need to explicitly commit (and rollback). But again the primary question is very application-specific: what constitutes a transaction? 

Of course, the real world is messy - even when we clean it up, shear off rough edges, and make that real world fit into our cyber world.

For example, sometimes we have to do "incremental commits" - save changes to N rows at a time, to avoid rollback segment errors.

That may be the reason that Debbie has put COMMIT statements after each FORALL and after each logging of an error. 

But do you need a COMMIT in that exception section to ensure that successfully executed statements are not rolled back? NO! The failure of a SQL statement in your block will not force the rollback of previously completed statements.

As for putting each FORALL inside its own block, sure, that makes a lot of sense - if you want to make sure that both FORALLs always execute for each iteration of the loop. I would, however, put each inside its own nested subprogram (see my rewritten version of your code below).

I probably would not, however, use the same exceptional handling strategy for ORA-24381 (which your code implies was associated with the dml_errors exception) and for WHEN OTHERS. If something else went wrong in FORALL execution that was not caught by SAVE EXCEPTIONS, you should consider it "catastrophic" and stop the processing.

And, by the way, so far as I can tell, that final COMMIT before the CLOSE statement? Totally unnecessary - if you keep all those other COMMITs. You've already covered every path out of the loop!

OK, here's my offering of an alternative implementation. It's still kinda pseudo-codish because I lack the full details of your requirements, but:
  • Just one COMMIT at the end
  • Nested subprograms for each FORALL
  • No handling of unanticipated errors.

IS
   failure_in_forall   EXCEPTION;
   PRAGMA EXCEPTION_INIT (failure_in_forall, -24381);

   PROCEDURE bulk_inserts (data_array_in your_type)
   IS
   BEGIN
      FORALL i IN 1 .. data_array_in.COUNT SAVE EXCEPTIONS
         INSERT INTO some_table
              VALUES data_array_in (i);
   EXCEPTION
      WHEN failure_in_forall
      THEN
         log_error;
   END;

   PROCEDURE bulk_updates (data_array_in your_type)
   IS
   BEGIN
      FORALL i IN 1 .. data_array.COUNT SAVE EXCEPTIONS
         UPDATE some_table
            SET x = data_array (i);
   EXCEPTION
      WHEN failure_in_forall
      THEN
         log_error;
   END;
BEGIN
   OPEN v_cursor;

   LOOP
      FETCH v_cursor BULK COLLECT INTO data_array LIMIT i_limit;

      EXIT WHEN data_array.COUNT = 0;

      bulk_inserts (data_array);
      bulk_updates (data_array);
   END LOOP;

   CLOSE v_cursor;

   COMMIT;
END;



Wednesday, October 29, 2014

Dinodate, PL/SQL as Scripting Language Liberator, watch the show!

As I mentioned in a previous post, I had an awful lot of fun with Christopher Jones doing our PL/SQL: The Scripting Language Liberator talk at OOW.
PL/SQL: The Scripting Language Liberator: While scripting languages go in and out of favor, Oracle Database and PL/SQL persist, managing data and implementing business logic. This session walks through a web application to show how PL/SQL can be integrated for better logic encapsulation and performance; how Oracle's supplied packages can be used to enhance application functionality and reduce application complexity; and how to efficiently use scripting language connection and statement handling features to get better performance and scalability. Techniques shown in this session are applicable to mobile, web, or midtier applications written in languages such as JavaScript, Python, PHP, Perl, or Ruby on Rails. Using the right tool for the right job can be liberating.
Once word got out around Oracle that we would be unveiling DinoDate at our talk....

The powers-that-be knew that this was a talk that must be recorded. So they recorded it and you can watch it here.

This session is part of a broader effort to make sure our users understand and leverage Oracle Database as an application development platform, not "just" a storage space for your data.



Tuesday, October 28, 2014

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables). 

All was going well when they hit the dreaded:
ORA-04030: out of process memory when trying to allocate 2032 bytes 
They asked for my help. 

The error occurred on this SELECT:
SELECT  *
   FROM header_tab trx
   WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)
      AND ((trx.column_ntab MULTISET
            EXCEPT DISTINCT generated_ntab2) IS EMPTY)
The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality.

We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score.

Then my eyes snagged on the use of DISTINCT and IS EMPTY.

They simply needed to make sure that there was nothing in column_ntab that was not in generated_ntab2. Having two of something left was the same result as having one of something left - not empty.

So I suggested that they could drop the DISTINCT and see what happened.

What happened is that the ORA-04030 stopped occurring.

The lead developer also took a closer look at how generated_ntab2 was constructed and discovered that they were already ensuring that there were no duplicates.

The DISTINCT was definitely not needed and so the problem, at least for now, was resolved.

They are not 100% confident that the error still might not occur, especially as data volume increases. But at least they've bought themselves the time to fully analyze their algorithms and explore a 100% SQL solution.

Tuesday, October 21, 2014

The PL/SQL Whisperer

[oreiginally published at FeuerThoughts in December 2011]

I spent two days in Berlin, training 25 developers at an event sponsored by DOAG. Then I headed over to the Netherlands to spend a couple of days with 37 developers at an AMIS-sponsored training.

But on Tuesday, after I completed the first day of training without the assistance of a microphone, my voice said "Bye, bye!" I woke up Wednesday morning to discover I had lost the ability to speak above a whisper. DOAG hustled, did what was necessary, and brought in a portable microphone/speaker system. AMIS made certain to have the same ready to go on Thursday. 

And so for three straight days, I whispered about new features of PL/SQL in 11g and much more besides. The attendees were very good natured about this less than optimal situation. One person said it made the whole class more exciting - it was as though I was giving away secrets, that no one should hear- except for the very special people in attendance.

Several students in the Dutch class even found themselves whispering to me when they asked a question - whispering, it turns out, is socially contagious! And I was quickly anointed the "PL/SQL Whisperer." Well, I can think of worse names, but I sure hope that soon I will recover my voice.

This was only my fourth business trip all year, which is a truly wonderful change from previous years (a less than fantastic result is that I will lose my Platinum status on American Airlines next week, so it will be tough to even get exit row seats when I do fly).

It was, unfortunately, a bit of a hard week on the road. Besides losing my voice, my flight on Wednesday from Berlin to Amsterdam was cancelled. I had to reroute through Zurich, but the flight to Zurich left late, so while I was able to just make the connection to Amsterdam, my bag did not follow along with me. By the end of the first day of training at AMIS offices, I had quite the five o'clock shadow. But the bag was delivered on Thursday, so it wasn't really such a big deal.

And then on Friday, I celebrated the end of a long week with a night in Amsterdam. Had a nice dinner with good friend, newly-minted Oracle ACE, AMIS consultant and father of three wonderful children, Patrick Barel. Then I went back to my hotel room to catch up on email and call my wife via Skype.

But the wireless network was not available. So the manager dutifully visited my room with an ethernet cable and (as far as I could tell) had to jam the cable a bit forcefully both into the wall and into my laptop. That made me uncomfortable. And still no Internet access.

So then he agreed to let me use the Internet in a different room - which led to a very irritating discovery: the ethernet plug would not come out of the back of the laptop. The spring that holds the cable in place had snapped. Manager Jon poked at it for a while, making me more uncomfortable, but it would not come out. Oh, I was very bummed and finally gave up, had him cut the cable so I could take the darn thing home, and figured I would have to have a technician get it out.

Yuch.

But then the day manager visited in the morning before I left for the airport. He had better tools and was able to quickly remove the plug. Whew, what a relief.

Both classes went well (of course, the students might feel differently, I suppose) - the PL/SQL community in Germany and the Netherlands is wide and deep: lots of very experienced developers attended. So I learned a few things along the way, which will soon make themselves known in PL/SQL Challenge quizzes. 

I also made an interesting discovery about how students concentrate on and extra information from the hundreds of slides and hours of talking I give them to them in a two day class. I covered FORALL in the class, also its SAVE EXCEPTIONS clause. I showed slides, ran code and talked about how if at least one statement raises an error, Oracle will save the exception information and then raise the ORA-24381 error. I talked about how you should avoid writing an exception handler like:


WHEN OTHERS THEN IF SQLCODE = -24381

and instead you should declare your own exception and assign it to this error, so 
you can write an exception handler like:


WHEN my_errors.forall_failure 

Then right at the end of the course, I talked about avoiding the hard-coding of error numbers and showed -24381 again as an example.

In the AMIS class, we ended with a short quiz. Two players tied for highest score (they selected 19 of 21 choices correctly across 5 questions - quite good!). So it was necessary for these two hotshots to engage in a sudden-death tiebreaker. The way I do this is I show a slide with a question and the first person to answer correctly wins.

The first slide asked: "Which feature of PL/SQL does this number relate to?" And then I showed "-24381". I thought there would be an instant response, given how often I showed and mention this number in the class.

And I was shocked to discover that neither of the students recognized the error code. I stared at them, surprised. Really? When I talked about it, referenced it multiple times, in the last two days?

Well, OK. We went on through several questions without success and then Christian Rokitta identified correctly that the optimizing compiler was first introduced in Oracle Database 10g. So congratulations to Christian for his first place showing, and to Danny Ven for the other score of 19 and his second place.

I am not exactly sure what to conclude from the inability to recognize the -24381 code. Perhaps students in my courses are confronted with so much information that they automatically filter out low level details. That way, they will be able to concentrate on the bigger picture.Certainly, people usually do not complain about how little I cover in my classes or how slowly I go through the material. And right at the beginning, I tell them to focus on principles and concepts, don't worry about all the technical details.

So I guess they took my advice!

In any case, now it is 4 AM on Sunday and I am (a) back in Chicago (hurray!) and (b) awake due to jet lag and (c) still without a voice.

Tuesday, October 7, 2014

My Ninth OOW - and first as an Oracle employee

Before heading out to San Fran last Thursday for meetings and the ACE Director briefing, I happened to pick up my Oracle PL/SQL Advanced Programming with Packages (my second book, published in 1996, immediately following up from the 1995 publication of my Oracle PL/SQL Programming text at IOUW in Phillie). 

Here’s what I found taped inside the cover:



That was the first Oracle Open World ever, and I was still working for SSC, the consulting firm I joined when I left Oracle in 1992. Must confess: don’t remember anything about OOW96, but I know how to count. Here’s some OOW math:

I have attended OOW96, 97, 98, 99 2000….2014: nine Oracle Open Worlds. During that time I published nine books on PL/SQL and one (with primary author Guy Harrison) on MySQL Stored Procedures; I wrote one or two automated testing frameworks for PL/SQL, and racked up 2M miles on American Airlines - ignoring my family as I traversed a sizeable chunk of human-occupied Earth presenting on PL/SQL.

OOW14 was, however, the first OOW I have ever attended as an Oracle employee. 

And it was a blast.

As I mentioned above, I went to the ACE Director briefing on Friday, and gave a short presentation at the enviable timeslot of 4:45 PM (just before food and beer. That’s tough). Ironically, this was the first ACE D briefing I’d ever attended - and I am no longer an ACE Director. I’d never gone to any when I was an ACE D, because (a) that would mean that I‘d be away from ome and family even longer, and (b) since I only knew (and was interested in) one thing (PL/SQL), the vast majority of presentations would be, well, boring.

Anyway, that was then, this is now. So at the briefing, I talked about the importance of promoting Oracle Database as much more than a “dumb” datastore, and instead as a powerful platform for application development. Lots of nodding heads. However, the message that seemed to resonate most strongly with my friends was that we, Oracle, need them (and you) to help us demonstrate the power of Oracle Database, and help users leverage more of its advanced features.

The next (Saturday) morning was, to be honest, the highlight of my entire trip - and OOW hadn't even started. That's because I spent three hours with a good friend in the Purisima Open Space Preserve, on a seven mile walk in through a redwood forest. I like Oracle a whole lot, but technology can't hold a candle to the connection I feel these days to trees (and my granddaughter).




But then my feet got sore. So we drove along US1, had a delightful lunch looking at over the ocean….and then I headed north. A day later, there I was at Moscone. Not as beauitful. Not as peaceful. Not quite as inspiring. But still pretty amazing.

I imagine that a few of my most devoted fans would like to read a day by day account of my time in San Fran. Most of you would, however, get bored fast. So, instead I offer the following highlights:

ACE Dinner

Even though I had to “give back” my ACE D title when I rejoined Oracle, Victoria Lira, the excellent director of the ACE program, was kind enough to invite me along, anyway. It was a great evening! These dinners are always fantastic, because whatever the quality of drinks and food, you have within close proximity literally dozens of the finest Oracle tech minds in the world. Wow. These days I am seeking feedback on my ideas for evangelizing application development features of Oracle Database, and there was no shortage of advice and cautionary tales. Then the program began and for me the highlight was an incredible acrobat/contortionist. I do a little bit of stretching and ab work each day, and that just made her flexibility and control even more impressive to me.

YesSQL Celebration

I’d been thinking that when I joined Oracle I would more or less continue what I’d been doing: writing, writing about, training and presenting on PL/SQL. And sure I will be doing that. But in addition I have been asked to, well, shake things up a bit. Try some different ideas for getting application developers excited (again) about PL/SQL and even more important, SQL. And one thing I’d noticed as I looked around Oracle, our websites, and our community is that SQL has become a bit taken for granted, like the air we breathe.

So I figured the first thing we should do is remind ourselves of how amazing this technology is and how important is all of our work with the technology.

And thus was born YesSQL! A celebration of SQL and PL/SQL. The idea for this event was, roughly, to NOT talk about products and features, but instead to share stories from our evangelists, users and developers (the people who build SQL and PL/SQL and the optimizer and APEX and so on).

About 500 people attended, which was great. My Macbook started throwing a hissy fit right before we were about to start. That was not so great. And since I organized the event pretty much on my own, it was a bit hit-and-miss. I also didn't have my act together enough to make sure we took lots of pictures and video. I promise to do a (MUCH) better job next year!

We started off with Tom Kyte's reflections on SQL and NoSQL. With sharp insight, he noted that the whole point of SQL was to replace "NoSQL" databases, such as VSAM. Then the years go by, some people forget the power of SQL, the cycle spins around and....it's time for NoSQL again! Then another few years go by, and now SQL is "sexy" again, because even the Hadoop developers recognize that it is really, really hard to write applications without a powerful set language like SQL. Hence, NewSQL!

Then a greeting from Thomas Kurian, Executive Vice President of Product Development and former PL/SQL Product Manager (!), which you can watch here. But in the meantime, enjoy a snap of Thomas eating from a slice of Happy YesSQL Celebration cake:



Next was, for me, the highlight of the evening: stories from Andy Mendelsohn, EVP of Server Technologies. He's been responsible for Oracle Database and all of its surrounding technologies for the last twelve years, but before that, let's see: he built the B-tree indexing code, and was one of the co-designers of the Oracle PL/SQL language. Been there, done lots of things. And shared some very entertaining stories of the early days at Oracle.

We also heard from Andy Witkowski, SQL Architect and (for YesSQL) the NoSQL Pirate. Mohamed Zait, who heads the optimizer team, shared a very amusing video (sorry not able to share it, at least not yet) showing how his time comes up with (and rejects) new ideas for the optimizer. Bryn Llewellyn and Iyer Iyer Chandrasekharan (PL/SQL PM and Dev Manager, respectively) talked about life with PL/SQL. Mike Hichwa reminisced about the early origins of APEX.

And that's not all! Maria Colgan of Optimizer and now In Memory fame; Graham Wood, SQL performance tuning wizard; Joel Kallman, APEX dev manager; all chipped in with stories and reflections on life with Oracle Database.

But then, sigh....we ran out of time. And not a single user shared their story. Which counts as a bit of a failure for me. My apologies! We will, however, start gathering stories BEFORE OOW15 and at YesSQL15, we will put users first and listen to your stories, before we let Oracle people fill up the evening.

DinoDate, aka, PL/SQL the Scripting Language Liberator

I gave a talk on PL/SQL new features in 12.1 and that was great fun, as usual. But I also did a talk with Christopher Jones, who is a PHP pro, as well as all-around strong Oracle technologist (and PM). We wanted to show how you can leverage Oracle Database as an application development platform, and not simply as a "dumb" datastore.

And since I was involved, we needed to break out of the emp-dept HR demo model, because it bores the heck out of me and I hate to be bored.

So we dreamt up DinoDate, the premier dating site for dinosaurs. After all, the meteor is falling. Time is running out. Must find a mate fast



[Note: sure, we could have made the site prettier and likely will, but focus group surveys indicated a distinct disinterest by dinosaurs in user interface design.]

And, of course, Chris decided that if I would insist on such silliness, that there was a price to be paid (by me):



So Chris built the site in PHP, talking advantage of the "usual" client side or macro techniques for improving performance and scalability with Oracle Database, including:
  • End-to-end tracing
  • Connection pooling
  • Bind variables
  • Statement Caching
  • Not auto-committing
  • Pre-fetching
  • Enabling the Client Query Result Cache
Still, performance was not the greatest. Enter, Steven Feuerstein, PL/SQL Evangelist extraordinaire! "What else can I do?" asks Chris, and then we (both) showed how to improve performance and scalability, and also add significant functionality to users in search with:
  • Consolidate multiple server calls from PHP into single stored procedure.
  • Leverage Oracle Advanced Queueing to remove processing bottlenecks.
  • Reduce network traffic with bulk processing.
  • Use Oracle Text to easily implement fuzzy searches.
  • Use Oracle Spatial to allow searching for nearby dinosaurs.
Overall, I thought it went quite well. We will explore using DinoDate as a demo platform for our various technologies, and hope to make all code available to you soon.

The session was recorded and when I can figure out where it is, I will let you all know.

Doing PL/SQL from SQL: Correctness and Performance (by Bryn Llewelyn)

My favorite session at OOW was Bryn Llewellyn's Doing PL/SQL from SQL: Correctness and Performance (CON8269):

"A SQL statement that calls a PL/SQL function is slower than one that defines the same result by using only SQL expressions. The culprit is the SQL to PL/SQL round-trip. Its cost can be reduced by marking the function deterministic, by invoking it in a scalar subquery, by using the PL/SQL function result cache, or by some other caching scheme. With caching, you can’t predict the result set if the function isn’t pure, so it would seem that caching should be used with caution. This session shows that a function must have certain properties to be safe in SQL, even without caching—in other words that the possible semantic effect of caching is a red herring. You can’t rely on how often, or in what order, an uncached function is called any more than you can rely on group by to do ordering."

It was an impressive piece of research (actually interviewing developers who built specific features), deep and clear thinking, and convincing (though somewhat limited) benchmarks.

I encourage you to download the presentation here, but I offer Bryn's summary below:
  • A PL/SQL function must be statement-duration-pure if it is to be called from SQL
  • In most cases, this simply falls out of what you want to write
  • Mark it deterministic when it is (SSQ when not? Hmm...)
  • By its very nature, it’s likely to be self-contained – so use pragma UDF, or equivalently write its whole definiIon in the with clause
  • You might well and up with a function that both is marked deterministic and has pragma UDF. Don’t worry. The performance benefit of the SQL-friendly compilatIon mode is not compromised by computatIons done for caching pragma UDF is the hands-down winner ‘cos its benefit is
  • independent of cardinality
  • If you find a potenIal use for a PL/SQL function that does SQL, and that is called from SQL, think hard... very hard
  • Might you, after all, express the whole thing in SQL?
  • Are you confident that you’re not getting nonsense by violations of read-consistency
  • Then, and only then, go ahead
  • Result-cache it, or not, in the light of cardinality considerations
  • Use the scalar subquery locuIon as well when you expect to meet only hundreds of distinct values in any one statement execution
Well, there's more I want to say and share with you about OOW14, but I think I will publish this before too many days pass. 

Sunday, September 21, 2014

Coming down to earth at OOW14

Before I joined Oracle, I was honored to be an ACE Director for several years, and boy did I get spoiled. Especially at Oracle Open World time.

Oracle paid for my airfare and hotel. They set up the hotel reservation. They picked me up in (non-stretch) limo and delivered me to that hotel. They drove me back to the airport. Nice....

Then I rejoined Oracle in March of this year.

In late August, I was talking with a friend at Oracle and mentioned that I had yet to arrange my hotel.

"Whaaaat?" she practically screeched in the phone. "You don't have your hotel? Oh, Steven, you better get on that right away."

Turns out I had received an email on 7 July saying, in effect, "Congrats, Steven as Oracle employee. We have confirmed your registration for the now. NOW GO RESERVE YOUR HOTEL ROOM."

But I didn't notice that last part. Just filed the email away.

So I followed the advice of my friend, and went on-line to get my hotel reservation set up.

Guess what? I am staying in Emeryville, across the Bay. An hour long BART commute back and forth, that still leaves me 2 miles from the hotel. A big change in the daily dynamic. In the past, I could casually stroll from hotel to first session of day, or to a breakfast meeting. Then at the end of the day, I always knew the hotel was just a few minutes away.

Now, I must commute to OOW each day (and back at night). I will not have my hotel room as a place to return during the day. I will lug my backpack around all day. I will think about how late I want to stay at that "one more" event in the evening.

Yep, a care-free "celebrity" no more.

Kind of a relief, really. I never did feel very comfortable having a driver just for me (not that I ever turned down the airport pickup, though I often arranged to get back on my own).

And I always found it a little bit silly to be featured in the "Celebrity Seminar Series" when I did trainings for Oracle University.

I realize it's going to be tough, but I think I can do it. I can ride public transportation. I can pay more attention to my plan for the day and evening.

Or maybe I can find a friend with a hotel room at Union Square to crash with.

Friday, September 19, 2014

Use COLUMN_VALUE when selecting from scalar table function

Received this question today:

I don’t have a problem to select from collection when collection is based on objects with columns/attributes. What about a collection defined as:

TYPE list_of_numbers_t IS TABLE OF NUMBER;

What would be the column name when you select from the collection?

Short answer:

COLUMN_VALUE

Longer answer: here's a script I used to demonstrate several different features of nested tables. See query at bottom.

CREATE OR REPLACE TYPE list_of_names_t
   IS TABLE OF VARCHAR2 (100);
/

GRANT EXECUTE ON list_of_names_t TO PUBLIC
/

DECLARE
   happyfamily     list_of_names_t := list_of_names_t ();
   children        list_of_names_t := list_of_names_t ();
   grandchildren   list_of_names_t := list_of_names_t ();
   parents         list_of_names_t := list_of_names_t ();
BEGIN
   /* Can extend in "bulk" - 6 at once here */
   happyfamily.EXTEND (6);
   happyfamily (1) := 'Veva';
   happyfamily (2) := 'Chris';
   happyfamily (3) := 'Lauren';
   happyfamily (4) := 'Loey';
   happyfamily (5) := 'Eli';
   happyfamily (6) := 'Steven';
   
   /* Individual extends. */
   children.EXTEND;
   children (children.LAST) := 'Chris';
   children.EXTEND;
   children (children.LAST) := 'Eli';
   children.EXTEND;
   children (children.LAST) := 'Lauren';
   --
   grandchildren.EXTEND;
   grandchildren (grandchildren.LAST) := 'Loey';

   /* Multiset operators on nested tables */
   parents :=
      (happyfamily MULTISET EXCEPT children)
         MULTISET EXCEPT grandchildren;

   FOR l_row IN 1 .. parents.COUNT
   LOOP
      DBMS_OUTPUT.put_line (parents (l_row));
   END LOOP;

   /* Use TABLE operator to apply SQL operations to
      a PL/SQL nested table */

   FOR rec IN (  SELECT COLUMN_VALUE family_name
                   FROM TABLE (happyfamily)
               ORDER BY family_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.family_name);
   END LOOP;
END;
/

And note that as of 12.1, you can use the TABLE operator with associative arrays as well!