Thursday, May 29, 2014

Resources for New PL/SQL Developers

Just received this question in my In Box:

"Which book do you recommend for people, brand new to PL/SQL? Assume the user is already familiar with SQL and now wants to start programming in Oracle."

First, I must confess that I spend too little time thinking about and being in contact with developers who are brand-new to PL/SQL. That is something I need to and plan to correct, especially given that when I search on "PL/SQL" in search engines, 3-4 of the top 10 hits are for tutorial sites. There is clearly a demand. 

Until I put together my own set of tutorials, however, I thought I would offer the following:

Books

Oracle PL/SQL for Dummies

Written by my good friends and really outstanding Oracle technologists, Michael Rosnblum and Paul Dorsey of Dulcian. These guys really know their stuff and have put together an accessible text.

Learning Oracle PL/SQL

Written primarily by my co-author on the massive Oracle PL/SQL Programming, Bill Pribyl, this book is, admittedly, dated, but since the fundamentals of PL/SQL haven't changed (ever!), I believe you can still benefit from this text.

Beginning PL/SQL From Novice to Professional

I confess: I have not read this book, but the author, Don Bales, knows his stuff well and I am sure you will find the book helpful.

So those are books targeting beginners. Having said that, for anyone who has ever programmed in any language, you will find PL/SQL to be quite straightforward. So if the idea of programming and the concepts of loop, case, exception handling, etc. are not new to you, you will probably have lots of fun and learn all you need to know about PL/SQL from the 6th edition (covering all PL/SQL features through Oracle Database 12c) of Oracle PL/SQL Programming.

In addition, it's never to soon to pick up tips for writing high quality PL/SQL code. Sound good? Check out my Oracle PL/SQL Best Practices. A relatively short book that describes dozens of best practices that teaches best practices by following the challenges of a development team writing code for a make-believe company, MyFlimsyExcuse.com.

There are, of course, many other books on PL/SQL and more generally Oracle application development. Check out authors Tom Kyte and Michael McLaughlin, to name just two.

Websites

OK, so much for books. Of course, most people don't read books anymore. They just check their latest twitter feed for all the knowledge they (think) they need. So don't forget to follow me on twitter: @sfonplsql. :-)

And there are many, many sites that offer various kinds of content on PL/SQL. Time for another confession: I have not visited nor do I use many of them. But I offer below sites that I know offer very high quality content on PL/SQL.


Oracle Dev Gym

The Dev Gym (and it's previous incarnation, PL/SQL Challenge) is a website that promotes “active learning” — rather than passively reading a book or web page, you take quizzes on PL/SQL, SQL, logic, Database Design, and Oracle Application Express, thereby testing your knowledge. I wrote a daily quiz for this site for four years (over 1000 quizzes). Whew. If you have not checked out PL/SQL Challenge and you want to get serious about PL/SQL, you gotta visit!

Practically Perfect PL/SQL

My YouTube channel! Dozens of videos on PL/SQL, as well coding therapy sessions and tips on doing great presentations.

Oracle Technology Network for PL/SQL

The Oracle Technology Network (OTN) “provides services and resources that de‐ velopers need to build, test, and deploy applications” based on Oracle technology. Boasting membership in the millions, OTN is a great place to download Oracle software, documentation, and lots of sample code. The PL/SQL page offers some excellent resources, and we will be adding to it greatly in the coming months (now: May 2014).

AskTom

AskTom is the amazing and incredibly popular Q&A forum hosted by Tom Kyte, arguably the finest Oracle technologist in the world. I know one thing pretty well: PL/SQL. Tom knows PL/SQL better than I do, and then on top of that is an SQL expert, an optimizer expert...and the list goes on and on. AskTom is a bit of a "grab bag" - you never quite know what you will find in the sometimes lengthy discussion threads, but you are sure to find many answers to questions there. Tom also offers great lists of links, resources and so on.

oracle-developer.net

Maintained by Adrian Billington (Who wrote the section in Chapter 21 on pipelined table functions), this site is a resource for Oracle database developers which contains an outstanding collection of articles, tutorials, and utilities. Adrian offers in-depth treatments of new features in each release of Oracle Database, full of examples, performance analysis scripts, and more.

ORACLE-BASE

ORACLE-BASE is another fantastic resource for Oracle technologists built and maintained by a single Oracle expert: Tim Hall. Tim is an Oracle ACE Director, OakTable Network member, and was chosen as Oracle ACE of the Year 2006 by Oracle Magazine Editor’s Choice Awards. He has been invovled in DBA, design, and development work with Oracle databases since 1994. See http://oracle- base.com. 

Rubber Hitting Road

So you read books, you check out websites. You realize that you can do this. You can write PL/SQL. You can manipulate the powerful Oracle Database using SQL and the simple, procedural constructs of PL/SQL.

Now it is time to write code. Because you never know a language until you start wrestling with the realities of making something work.

So that's your next task: write code!

Of course, many of you will be writing code for your job, so we don't to say a whole lot more about this. 

If, on the other hand, you are looking to hone your skills to strengthen your ability to get a job, then I suggest the following:

Learn and use Oracle Application Express. With nothing more than SQL, PL/SQL and just enough HTML, CSS, etc. to make it look as "pretty" as you like, you can build powerful websites against the Oracle database. Apply for a workspace online, and get coding! Build an app for yourself or your family. It's free! 

Learn and use SQL Developer, an "integrated development environment" (editor) for SQL and PL/SQL. It's from Oracle and it's free. 

Tuesday, May 13, 2014

A new app is (almost) born: ExpertTeam

A few weeks ago I announced formation of FeuerTeam, a team of Oracle technologists who would like to help me build the global PL/SQL community.

And now I find myself neck deep building an APEX app that will allow both FeuerTeam members and I to “self service” the activity on FeuerTeam. The fact that I am doing this and how I am doing this may be interesting to you. It reflects so clearly how I go about doing things these days.

I work on demand and try to avoid spending time on a project until I am sure it is needed.

I knew that I needed a team of people to help me accomplish my goals (which are under construction as I type). But I didn’t know if many people would be interested.

So I asked. And you responded. Lots of you. And then I knew: you will help me. Thanks so much! 

But then I was confronted with a challenge: how do I manage this process? Iudith is reviewing a magazine article. Ravshan is helping with APEX development questions. Others will be providing solutions to posted problems. How do I tell you about the kinds of help I need? How do you respond, sign up for a task? How do I make sure I don’t become a bottleneck in the whole process?

So there you have it: I needed to build an application.

Fortunately I have at my disposal the power of the relational model, the Oracle Database, SQL, PL/SQL and now APEX. I can build a website without having to learn barely anything new. How cool is that?

So I sorted out the database design: with straight table DDL. I don’t use a modeler. I am so old school in so many ways (even when it comes to music). I worked it out in my head and in scribbled notes on paper, typed it in (actually, generated most of it), ran it (creating tables and indexes and foreign keys and triggers) and then dove into APEX to start building the app in a fairly incremental fashion. Not sitting down and mapping out the whole site. And using the default settings of APEX to have the tool do as much of the work for me as possible (and test how well an almost pure default APEX app will look and behave).

This approach follows one of my favorite mottos: Act Now Perfect Later.

I just don’t seem to have the patience to sort it all out in advance. I want to see results now. The downside, as I am sure many of you have experienced, is that I make mistakes, miss things, and have to circle back and make corrections. The upside is that rather than having an app in a month or 3 months, I will very soon “open up” the app for your use.

I have decided to call this app ExpertTeam, because I generally assume that anything I need will be needed by others, so why not start with a generic approach? I am not building this app for me; I will just be its first user. I hope to offer ExpertTeam to other Oracle experts who could benefit from a team. I am sure they will take a close look at how well FeuerTeam has worked for me. So I am very incented to get this to work, and to make it easy for everyone to help.

And that helping has already begun. Ravshan, a FeuerTeam member, has been helping me move the ExpertTeam app forward much more quickly than I could have done on my own. So FeuerTeam is already a big win for me, and we’ve barely begun!  Thanks, Ravshan!

Iudith also recently did a really nice job reviewing my latest Oracle Magazine article. Thanks, Iudith!

And now can you see the problem? What happens when FIFTY of you do something really great for me? I will want to thank all of you, give you credit, but I can’t do this manually.

So I will push forward with ExpertTeam and hope that sometime this month I can invite to help me lift the global PL/SQL community to new levels of engagement, sharing and excitement!

Tuesday, May 6, 2014

FORALL and ROLLBACKs


I received this question in my In Box over the weekend:

Hi Steven,

I have grown up reading the PL/SQL bible, Oracle PL/SQL Programming. I have the 5th edition with me right now, Indian reprint, ISBN - 971-81-8404-949-7.

I was going through the chapter 21 : Optimizing PL/SQL performance. Please refer to the section 'ROLLBACK behaviour with FORALL'.  In point number 2, it's mentioned that "Any previous DML operations in that FORALL statement that already completed without any error are NOT rolled back."

Can you please explain this ? As far as I understand a FORALL can have only one DML statement, so all the previous work done by the DML will be rolled back.

Thanks for your time. Hoping a response soon.

An Indian reprint of the 5th edition? You are so lucky. I don't even have one of those. I don't even have a copy of that! I will have to ask my friends at O'Reilly Media for a copy! :-) 

Now to answer your question, and it is a common one:

Perhaps the way I phrased this is not as clear as could be. So let's take it step by step and hopefully all will become clear. Let's start with a simplified FORALL statement:

FORALL iterator IN low_value .. high_value
   dml_statement
;

FORALL is itself a PL/SQL statement, and within it you find a single DML statement - and no more than one, as you so correctly point out.

When the PL/SQL engine encounters a FORALL statement, it uses the IN clause to determine how many individual DML statements will be generated (with bind variables drawn from the bind array(s)) and then passed to the SQL engine. 

In the sentence you quoted above, I use the term "DML operations" to refer to each of these generated  statements.

So, for example, in this little example, a total of 5 DML statements might be executed by the SQL engine. 

DECLARE
   TYPE ids_t IS TABLE OF INTEGER;
   l_department_ids ids_t := ids_t (1, 2, 3, 4, 5);
BEGIN
   FORALL indx IN low_value .. high_value
      UPDATE employees SET salary = salary * 2
        WHERE employee_id = l_department_ids (indx);
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line (SQL%ROWCOUNT);
END;

Why only "might be"? Suppose that the salary column is defined as NUMBER(3) and the salary for an employee in department 2 is 600. When I double that, I now need 4 digits to store the value. So the SQL engine will reject the update and throw an error back to the PL/SQL engine.

So in that case, the following takes place (or not as the case may be):
  • one DML statement completed (for employee ID 1)
  • one failed (for ID 2)
  • three of them (for IDs 3-5) never even executed
And the PL/SQL block in which the FORALL was executed traps the error from the second statement executed and displays 1 because it reflects the total number of rows modified by the FORALL statement, not the last individual DML statement generated by the FORALL statement.

And this tells us quite unambiguously that the effect of the first UPDATE is not rolled back by the second UPDATE's failure.  

Oh and by the way: this behavior has nothing whatsoever to do with FORALL. You could execute three separate DML statements in a block and it works the same: if the first two finish successfully and the third fails, the effects of those two statements are not rolled back.

Of course, if your exception propagates all the way out of the top-most PL/SQL block unhanded, then all the outstanding changes in your session will be rolled back.

Hope that helps! Comments, questions, anyone?

Cheers, SF

Monday, May 5, 2014

I am (or aim to be) a problem solver

Back in 1995, O'Reilly published the first edition of Oracle PL/SQL Programming. Changed my life...thanks readers! The following extended dedication appeared in that first edition, early evidence of my compulsive verbosity. But I still like it and thought I would offer it for your reading pleasure.

Sometimes I have trouble explaining to my about-to-be-nine-year old son, Eli, the nature of my professional occupation. The statements "I work with computers" and "I write programs on computers" certainly describe my means of employment, but they do not afford much of an explanation.

After some thought, I have found what I believe to be the simplest and also most accurate statement of my professional role in life:

I am (or aim to be) a problem solver.

Every time I write a program or assist other developers with their code, I work to solve a problem. The problem might be as broadly defined as "Build an application to keep track of all product complaints." It could also have a scope as narrow as "Find out why the string parsing mechanism in Sam's package doesn't work." At hear, there is really very little difference between these two requests. 
I am asked to bring logic, experience and technical skill to bear on an unresolved issue – and resolve it.

Solving problems is an extremely satisfying experience. This is part of the reason, I believe, that software programming can be such an addictive habit. In 1995, the quality of life – indeed, the very lives – of hundreds of millions of people around the world are threatened by the greed and out of control ambitions of a very few. Software, with its simple rules and clearly defined boundaries, offers a haven, an oasis, a paradise, into which we can escape, debug out programs, and feel that we have accomplished something constructive.

I dedicate this book to my youngest son,. Eli Silva Feuerstein, in the hope that as an adult he will be an effective and compassionate problem solver, both in the area of his professional interest and in the infinitely more troubled world at large.


I also dedicate this book to my parents, Sheldon and Joan Feuerstein, as a meager expression of my thanks for their lifelong dedication to the happiness and success of their five children (and now six grandchildren).

Eli is now 27 and an Oracle Application Express developer! But I am pretty sure he likes music more than software....