Wednesday, August 20, 2014

Your Top Tips for PL/SQL Developers?

I will giving a presentation to Oracle Corporation PL/SQL developers (that is, Oracle employees who build internal apps using PL/SQL) in September, lots of them new to the language and starting new development.

The focus of the presentation is on best practices: what are the top recommendations I want to give developers to ensure that they write high quality, maintainable, performant code?

I plan to turn this into a checklist developers can use to stay focused on the Big Picture as they write their code.

So if you could pick just THREE recommendations to developers to help them write great code what would they be?

YesSQL! a celebration of SQL and PL/SQL: OOW14 event 29 September

First the key details:

When: 6:30 - 8:00 PM on Monday, 29 September

Where: Moscone South - 103

To register: Session CON9027. This event will be offered as a "regular" OOW session, which means you register to attend through Schedule Builder.

Why: Because SQL and PL/SQL are amazing technologies, and the people who them to deliver applications are amazing technologists

For many, many years - since 1979, in fact - Oracle Database software and other relational solutions have been at the core of just about every significant human development, whether it be based in private enterprise, government, or the world of NGOs.

SQL, relational technology, Oracle Database: they have been incredibly, outrageously successful. And SQL in particular is a critical layer within the technology stack that runs the systems that run the world. SQL is a powerful yet relatively accessible interface between algorithmic processing and data. 

Rather than write a program to extract, manipulate and save your data, you describe the set of data that you want (or want to change) and leave it to the underlying database engine to figure out how to get the job done.

 It's an incredibly liberating approach and I have no doubt that SQL and Oracle Database are two of the defining technologies that made possible the Information Era and the Internet/Mobile Era. Sure, you could argue that if Oracle hadn't come along, some other company would have taken its place. But Oracle did come along, and from 1979 through 2014, it has continually improved the performance and capabilities of Oracle SQL, providing innovation after innovation.

Let me repeat that, because I think that so many of us have lost perspective on the impact Oracle technology – and we Oracle Database developers* – have had on the world:

SQL and Oracle Database are two of the most important software technologies of the last forty years. And all of you, all of us, played a key role in applying that technology to implement user requirements: literally, to build the applications upon which modern human civilization functions. Us. We did that, and we do it every day. 

How cool is that?

Very cool....and deserving of special note. So we are going to note that and much more at the first-ever YesSQL! A celebration of SQL and PL/SQL.

Co-hosted by Tom Kyte and Steven Feuerstein, YesSQL! celebrates SQL, PL/SQL, and the people who both make the technology and use it.

No feature Powerpoints. No demos. Instead special guests Andy Mendelsohn, Maria Colgan, Andrew Holdsworth, Graham Wood and others will share our stories with you, and invite you to share yours with us, because....

YesSQL! is an open mic night. Tell us how SQL and PL/SQL - and the Oracle experts who circle the globe sharing their expertise - have affected your life!

Bottom line: If developing applications against Oracle Database is a big a part of your life, join us for a fun and uplifting evening.

Share Your Stories!

I hope you can join us at the event (you'll be able to sign up for YesSQL! just like for a regular OOW session). But if you can't (or even if you can), you can share your story with us, right here (and on the PL/SQL Challenge, in our latest Roundtable discussion).

How has SQL and/or PL/SQL and/or Oracle Database changed your life, personally, professionally or otherwise? We will select some of your stories to read at the YesSQL! event and if you are attending, you can tell the story yourself.

I also encourage you to record a short video telling your story. That's so much more entertaining! Just record and upload to a location of your choice, and I will grab it or link to it (YouTube, for example).

* I used to talk about PL/SQL developers and APEX developers and ADF developers and Javascript developer and so on, but I have recently come to realize that very, very few Oracle technologists can be “pigeon-holed” that way. Sure, I know and use only PL/SQL (and SQL), but just about everyone else on the planet relies on a whole smorgasbord of tools to build applications against Oracle Database. So I’m going to start referring to all of us simply as Oracle Database Developers.

Thursday, August 14, 2014

Recording of "How to Write PL/SQL Code You Can Actually Read" now available

I recorded the second in my PL/SQL by Feuerstein 2014 webinar series yesterday.

You can access the recording here.

If you were registered for the event, simply provide your email address and log in. But first have the popcorn ready because the show will begin immediately!

If you did not register, provide your email in the Register field. You will then be prompted to confirm it and then have your popcorn ready.

I tried something different with this webcast: rather than spend most of my time in slides, I spent 40 minutes taking a look at spaghetti code and then showing you the cleaned up version.

I am not wildly excited about how it turned out. I would be very happy to read any comments you have about the webinar right here on this blog.

And be sure to sign up for the other webinars, available at the Oracle PL/SQL Learning Library.

New PL/SQL book: Oracle PL/SQL Performance Tuning Tips & Techniques

I recently received a copy of Michael Rosenblum's and Dr. Paul Dorsey's latest book: Oracle PL/SQL Performance Tuning Tips & Techniques. Very impressive!

It's so different from mine: only 300 pages compared to my monster tome of 1000+ pages. Ah, so much easier to hold.

But way more importantly, it is packed full of performance advice, based on the deep, long experiences of two Oracle technologists who have been out in the trenches helping customers put together successful applications that fully leverage Oracle Database and all is core technologies.

There are an awful lot of books on PL/SQL in the market; many of them (inevitably) cover the same material, albeit in different ways.

I found this book to be a very refreshing addition to the mix. It takes a holistic approach, offering glimpses into aspects of Oracle Database architecture and tuning/analysis tools with which most PL/SQL developers are not terribly familiar.

It uncovers some delightful nuggets, such as improving the deterministic caching of user-defined function calls in SQL by placing that function call in a scalar subquery (page 191).

I plan to apply a number of their ideas to the PL/SQL Challenge backend; I also expect to be modifying some of my training materials to reflect their experience in some feature areas for which I am mostly an "academic" presenter. That makes it a book definitely worth having on my bookshelf, and one I can certainly recommend to others!

Of course, no book is perfect. I feel that Oracle PL/SQL Performance Tuning Tips & Techniques could benefit from a clearer statement of use cases for a number of features, such as FORALL and the Function Result Cache. Certainly, many readers will be experienced developers and so perhaps don't need that framing, but be optimistic, fellows! Expect that many readers will be relatively inexperienced developers trying to figure out how to improve their code's performance.

Bottom Line:

If you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success.

Tuesday, August 12, 2014

PL/SQL Challenge Community to the rescue! Get job name inside DBMS_SCHEDULER-executed proc

An Oracle Database developer* contacted me recently with this problem:

Do you know a way to get the DBMS_SCHEDULER Job Name from within the code that is being executed by the job?

I could have told him to visit the OTN SQL and PL/SQL Forum and post his question there, but I thought that instead I would ask the players at the PL/SQL Challenge if they had any ideas. So I posted this message in the Recent News section:

Solution from Niels Hecker

Soon after, I received a solution from Niels Hecker, one of the top PL/SQL Challenge quiz players, and all-around very helpful guy. He wrote:

I have found a really easy solution: in the context "UserEnv" there is an element "BG_Job_ID" which is not a real id of the job but the id of the job-object in the database (DBA_Objects.Object_ID). Here’s the code I wrote leveraging this fact:

-- the user needs the following privileges directly assigned:
--     EXECUTE on package DBMS_Lock

-- create logging-table with associated log-procedure
    ID     INTEGER,
    Stamp  TIMESTAMP(3),
    Msg    VARCHAR2(4000)

    MINVALUE 0     MAXVALUE 4294967295

    tmp  VARCHAR2(4000);
  tmp := RTrim( SubStr( pMsg, 1, 4000));
  INSERT INTO tbl_LogMsg (ID, Stamp, Msg)
END LogMsg;

-- create a job to execute the procedure (created in the next step)
      Job_Name           => '"This is the Job to look after"',
      Job_Type           => 'PLSQL_BLOCK',
      Job_Action         => 'ExecutedAsJob();',
      Start_Date         => NULL,
      Repeat_Interval    => NULL,
      Enabled            => False,
      Auto_Drop          => False,
      Comments           => 'Just a test to see if you can find out the job-name'

-- create the full procedure

    iJobID   INTEGER;
    vcOwner  VARCHAR2(30);
    vcName   VARCHAR2(30);

            Result     INTEGER;
            iSID       INTEGER;
            iInstance  INTEGER;
          -- retrieve the actual session-id and the instance-number
          Result := Sys_Context( 'UserEnv', 'BG_Job_ID');

          IF (Result IS NULL) THEN
            iSID      := Sys_Context( 'UserEnv', 'SID');
            iInstance := Sys_Context( 'UserEnv', 'Instance');

            -- retrieve the id of the actual job which is in fact
            -- the object-id of the scheduler job
            -- (assertion: there is only one running job at a time
            --             for the a specific session)
            SELECT srj.Job_ID
                INTO Result
                FROM gv$Scheduler_Running_Jobs srj
                WHERE     (srj.Inst_ID = iInstance)
                      AND (srj.Session_ID = iSID);

            LogMsg( 'GetJobObjectID$() - SID: ' || iSID || ', Instance: '
                    || iInstance);
          END IF; -- (Result IS ...

          RETURN (Result);

              LogMsg( SQLERRM);
              LogMSg( DBMS_Utility.Format_Error_BackTrace());
              RETURN (NULL);
        END GetJobObjectID$; -- local to ExecutedAsJob

BEGIN -- of ExcecutedAsJob
  LogMsg( 'Procedure/Job started');
  DBMS_Lock.Sleep( 1.0);

  -- get the job-/object-id
  iJobID := GetJobObjectID$();
    LogMsg( 'Found this running job - ID: ' || iJobID);
    DBMS_Lock.Sleep( 1.5);

    LogMsg( 'Ooops - no running job found and goodbye ...');

  -- get the owner and name of the job
  SELECT o.Owner, o.Object_Name
      INTO vcOwner, vcName
      FROM DBA_Objects o
      WHERE (o.Object_ID = iJobID);
  LogMsg( 'Job-Object: "' || vcOwner || '"."' || vcName || '"');

  DBMS_Lock.Sleep( 2.5);
  LogMsg( 'Procedure/Job ended');

END ExecutedAsJob;

-- code to run the job and query to see the results
  DELETE FROM tbl_LogMsg;
  DBMS_Scheduler.Enable( '"This is the Job to look after"');

SELECT Sys_Context( 'UserEnv', 'SID') AS "SID", t.*
FROM tbl_LogMsg t ORDER BY t.ID;

-- after 5 seconds the query should give a result like:
SID  ID  STAMP                    MSG
---- --- ------------------------ ----------------------------------------------------
66   0   2014-08-07 11:26:06,930  Procedure/Job started
66   1   2014-08-07 11:26:07,932  Found this running job - ID: 266922
66   2   2014-08-07 11:26:09,434  Job-Object: "TEST2"."This is the Job to look after"
66   3   2014-08-07 11:26:11,935  Procedure/Job ended

-- clean up the database
exec DBMS_Scheduler.Drop_Job( '"This is the Job to look after"');

The fellow in need reported that this did solve his problem. Thanks, Niel!

Of course, the PL/SQL Challenge community is full of helpful and expert folks, so I did receive other ideas as well. I offer them below, with the caveat that I have not tested them myself. Thanks to everyone for their assistance!

From Chris Saxon

Hi Steven,

I saw your post about the getting the name of a job from within it on PLCH. This approach does that.

The view USER_SCHEDULER_RUNNING_JOBS show jobs that are active. Querying this, filtering on the current session id will return the current job (if you're within one). The query is:

SELECT job_name FROM user_scheduler_running_jobs
where  session_id = sys_context('USERENV', 'SID');

A full script to show this is below

create table job_name ( name varchar2(100) )

create or replace procedure store_job as

  insert into job_name ( name )
    SELECT job_name FROM user_scheduler_running_jobs
    where  session_id = sys_context('USERENV', 'SID');   

            job_name => '"CHRIS"."TEST_JOB"',
            job_type => 'STORED_PROCEDURE',
            job_action => 'CHRIS.STORE_JOB',
            number_of_arguments => 0,
            start_date => NULL,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '');

             name => '"CHRIS"."TEST_JOB"');
    SYS.dbms_scheduler.run_job ('TEST_JOB');

SELECT * FROM job_name

From Cristi Boboc

I do not have much experience either with this package but I think the name of the job can be obtained by the following algorithm:

1. I get the session under which the process execute,
2. From the active running Jobs I get the one which runs in the same session.
A pseudo-code (I do not have an environment to test - therefore kindly please excuse my mistakes) could look like:

SELECT owner, job_name, running_instance, session_id, j.* 
  FROM all_scheduler_running_jobs j
WHERE session_id = sys_context('USERENV','SID')

or, if the "old way of scheduling jobs":

SELECT job, instance, sid, j.* FROM dba_jobs_running j
WHERE session_id = sys_context('USERENV','SID')

From Zoltan Fulop

You raised a question on PL/SQL Challenge regarding DBMS_SCHEDULER. Since I worked a lot with that package let me share my experience about how to get the job name within the code that is being executed by the job. You can use the dictionary view called user_scheduler_running_jobs which lists the currently running jobs or you can simply get the v$session.action attribute by the sys_context('USERENV', 'ACTION') if you're running that job in a background process. Here you can find an example:

CREATE TABLE plch_log (job_name VARCHAR2(100));

  l_job_name user_scheduler_running_jobs.job_name%TYPE;
    SELECT job_name
      INTO l_job_name
      FROM user_scheduler_running_jobs
     WHERE running_instance = SYS_CONTEXT ('USERENV', 'INSTANCE')
       AND session_id = SYS_CONTEXT ('USERENV', 'SID');
    WHEN no_data_found THEN
      l_job_name := SYS_CONTEXT ('USERENV', 'ACTION');

  INSERT INTO plch_log VALUES (l_job_name);

  l_job_name VARCHAR2(100) := dbms_scheduler.generate_job_name('PLCH_');
    job_name            => l_job_name
   ,job_type            => 'STORED_PROCEDURE'
   ,job_action          => 'PLCH_PROC'
   ,enabled             => TRUE
   ,auto_drop           => TRUE);
    job_name            => l_job_name
   ,use_current_session => FALSE);


SELECT * FROM plch_log;


DROP TABLE plch_log;

From Iudith Mentzel
I have zero experience with DBMS_SCHEDULER, but, on a quick glance, maybe the following could help (I did not try it, it is just a "dry" idea)::
Another way would be to use the DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT, which can pass the JOB_NAME (and other job metadata ) to the program executed by the job, but, as far as I understand, the program should be prepared/defined to accept that argument, so it is maybe less generic.

*About that reference to Oracle Database Developer

I used to talk about PL/SQL developers and APEX developers and SQL developer and so on, but I have recently come to realize that very, very few Oracle technologists can be “pigeon-holed” that way. Sure, Steven knows and uses only PL/SQL (and SQL), but just about everyone else on the planet relies on a whole smorgasbord of tools to build applications against Oracle Database. So I’m going to start referring to all of us simply as Oracle Database Developers.

Saturday, August 2, 2014

Is it all about End Users or End Boredoms?

I've been writing software since 1979, floating along from Algol and Lisp in the classroom to Fortran in the research project, at Big Pharma, at Big Insura...until finally I landed a job at Oracle Corporation, found PL/SQL, wrote a book and settled into specializing on a single technology for the last 22 years.

And all along the way, I wrote code to satisfy the requirements of my end users, who were almost always employees of companies. In other words, not "retail."

But with the advent of the Internet and World Wide Web, and then especially with the viral use of smartphones and tablets, the predominant "end user" of applications is now, well, everyone.

But when everyone is a user, what are the apps for?

They used to help organizations meet their goals (make money, end hunger, above all sell things). And in this context, the data and the database were absolutely, fundamentally important.

When you received an order for a product, you really did want to be able to trust that the order information was saved and saved correctly. When you shipped the product, the inventory had to be updated properly. Otherwise chaos would ensue.

No one helped (or helps, today) avoid data chaos better than the Oracle Database.

Readers never block readers, writers never block readers.
Row-level locking.
Optimal performance without any sacrifices to data integrity.

All great stuff, and that's one of the reasons that Larry Ellison is one of the wealthiest people in the world: Oracle Database works.

Oh, and the SQL language was (and remains) a transformational way to work with data. And the PL/SQL language is a powerful extension to SQL, allowing developers to implement any  business requirement imaginable.

But's a user. And what exactly are the apps all these new users need?

Well, "need" is an interesting word, easily confused by humans with another four-letter word: want.

Look at the apps that dominate the end user space, that ushered in the era of Big Data, that led to the creation of new database technologies and even putting the word "No" in front of one the most successful software products ever (SQL).


Etc. etc. etc. So many of these websites and products give everyone a way to say:

"Look at me! I am here! This is what I am doing!"


"What are my high school buddies doing now?"


"Help! Emergency! I am bored! Please distract me on whatever screen is most accessible."

[And almost all of them are "free", funded by advertising. But I won't get started on that topic, right now.]

Who likes to be bored? That is, who likes to not be distracted, to be stuck inside your own brain, having to think about things, or even worse to pay deep attention to the world around you?

Apparently, not a whole lot of us.

And before I lose the train of thought/argument for this post, let's bring it back to data.

Very few humans have the privilege, if one could call it that, to be known, to be recognized, to be famous. But I think that all or many of us do crave that attention, in part because it is a validation of one's life, an injection of meaning. And we also like to make connections with other humans, which is harder and harder to do when you spend lots of time in front of a TV or video game deck or in a book.

And so when it became possible to have your own "wall" and build your own "timeline" that everyone, or at least your "friends" could see, there was no stopping us.

But think about the data: when you post an item about what kind of shampoo you used in your recent shower, it really doesn't matter if it takes a while for the info toy show up, or even if it is lost entirely in the ether. Sure, it might be frustrating, but you just shrug and post an item about what kind of beer you enjoyed for dinner. And that one sails through.

In other words, for many of our new everyone users with their new everyone apps, data integrity and consistency are not that critical. It's all about helping us avoid being bored, and that happens simply by typing our tweets, sharing our photos. Having it published is just the icing on the cake.

Combine that lack of concern about integrity with the enormous increase in volume when everyone becomes a user, and it's not hard to see why the beautiful features of SQL and Oracle Database are less critical - for this particular segment of software.

[And for the limited period of time needed for Oracle to improve its database technology to handle massively large data sets and high throughput.]

What is more difficult to see is why so many software developers and CIOs would conclude that data integrity and consistency is no longer needed for lots of other apps in which boredom does not drive the process.

But of course what goes around comes around. So now Google, the creator of Hadoop, has come to realize that it's really hard to build applications on top of that cool, new database. What's needed is a language to access and manipulate that data. Which means it's time for....wait for it...."NewSQL".


In the meantime, Oracle continues to strengthen its core database technology, extend it into new areas, boost performance by orders of magnitude (see the new In Memory database option).

In the meantime, we in the software industry need to help our End Users End their Boredom.

It's not going to be easy, and it is a never-ending task for sure, but at least it generates lots of data.

OOW Session Details: The Whys and Wherefores of New Oracle Database 12c PL/SQL Features

Coming to Oracle Open World 2014? Paying attention to the PL/SQL language? (well, you probably are if you are reading my blog!)

Then I've got some exciting news:

My session, titled "The Whys and Wherefores of New Oracle Database 12c PL/SQL Features", and given the very best session number: CON7828, will take place at 3:45 PM on Wednesday, October 1 (also the birthday of my son, Eli - surely this is not a coincidence. Surely there must be a reason...oh, wait, right, no. Stuff just happens).

And the location? Moscone South 103

As anyone who has ever attended OOW before knows, Moscone South 103 is the premier location for a session on PL/SQL.

OK, fine, so I am being a little...sarcastic? No, no, not at all. Just playful.

I am very excited to be attending OOW as an Oracle Architect for the first time. We are going to have FUN!

Especially because Monday night at 6:30 PM, we will hold the FIRST EVER YesSQL! a celebration of SQL and PL/SQL. 

This celebration is on the OOW schedule as a "session", though I can assure you it will be unlike any other session you've gone to. Here's a description:

Co-hosted by Tom Kyte and Steven Feuerstein, YesSQL! is an Oracle Open World event celebrating SQL, PL/SQL, and the people who both make the technology and use it.

At YesSQL!, special guests Andy Mendelsohn, Maria Colgan, Andrew Holdsworth, Graham Wood and others share our stories with you, and invite you to share yours with us, because....

YesSQL! is an open mic night. Tell us how SQL and PL/SQL - and the Oracle experts who circle the globe sharing their expertise - have affected your life!

And right after YesSQL! everyone is invited to join the big Tech Fest on Howard Street, where we can continue our conversations, and mingle with Java, MySQL and other developers in the vast Oracle ecosystem.

Bottom line: If developing applications against Oracle Database is a big a part of your life, join us for a fun and uplifting evening.

Space is limited at YesSQL!, so be sure to sign up. The session ID is CON9027.