Tuesday, January 19, 2016

Getting started (and "playing around") with PL/SQL (and SQL (and Oracle Database))

Got this email Monday from a fellow new to PL/SQL and Oracle:
I have been in I.T. for a couple of years but always had this feeling that I could do better. I lost that hunger when the company I had worked for 10 years plucked me from the warehouse as a kind of internal promotion program. I guess you could say I slowly settled into a rut.  
I discovered your site recently and I am in the process of reviewing your tutorial videos (I have also started to read newer technical books). I am sure you have been told before but your tutorial videos are great!  
My problem is that I think the lessons would "stick" with me more if I had the chance to work with them and "play" around. 
I have very low self esteem (which I am also working on) so please don't laugh but is there a place that I can download a condensed or tutorial version of Oracle 11g?
First, I am always excited to hear from people who are just getting into Oracle Database, SQL and PL/SQL. Our part of the technology stack is "way down there" and often gets less attention these days. So my first response is always:

Welcome to my world! And it is a rather big, rich and supportive world. There are tens of thousands of experts in Oracle Database, all around the world, many of them eager to help and share their knowledge.

So many websites to visit and learn from, including:

PL/SQL Home Page - a great starting point for resources on PL/SQL

SQL Home Page - a great starting point for resources on SQL

Ask Tom - answers from a few of the biggest Oracle Database brains in the world

Oracle Technology Network, especially the forums - Oracle's own hosted community ecosystem

Oracle Dev Gym - quizzes on SQL, PL/SQL, Database Design and more!

Databases for Developers - a lightweight, 12-week "bootcamp" introduction to databases and SQL for application developers.

But Robert, who wrote the above message, wasn't just looking for sources of information. He wants to be able to play around with the technology - hands-on. Good idea, Robert! Reading, watching, etc. can never replace what you learn by actually writing some code.

These days, Oracle makes it really, REALLY easy to play around with SQL and PL/SQL. Just go to LiveSQL and get started!

LiveSQL offers several pre-defined schemas with data (and more to come soon!), tutorials and lots of scripts contributed by experts around the world.

If you want a more full-blown interaction with Oracle Database, well, hey, you can download a VM of Oracle Database 12c Release 1, with a whole bunch of tools, all for free!

Monday, January 11, 2016

TwitterQuiz results: What could be safely deleted from this code?

Last week, I posted this on Twitter:

What code can be removed w/o changing text shown after execution?

Who Said What?

evrocs_nl putting data in collection before the select, because the bulk collect will clear the collection first (unless really old oracle)

Yes! A BULK COLLECT always empties the target collection. If the query returns no data, the collection remains empty. Otherwise its contents are replaced by the result set of the query.

MDWidlake All of it as you forgot to turn serveroutput on anyway. Where do I collect my Kewpie doll?

Oh, Martin, you clever fellow. There's always one in a crowd. But sort of good point. This exercise was taken from the PL/SQL Challenge, whose PL/SQL quiz assumptions include that SERVEROUTPUT is always on.

ddfdba indx pls_integer:=100; l_empty objects_t; := l_empty

Yes! There is no need to declare a variable for the iterator used in a FOR loop; it is declared implicitly by PL/SQL. There is, furthermore, need to assign an "empty" collection to l_objects to initialize it. A BULK COLLECT automatically initializes a nested table, if it has not already been initialized.

gurcan_orhan where's the exception (when others then null;)

Well, Gurcan, your text was not relevant to the exercise at hand, but it is always an important question to ask a programmer about their subprogram: 

"Where's the exception section?"

The answer might well be "We handle exceptions in an outer block." and that could be a fine answer, but it never hurts to check.

patch72 both declarations of indx. l_empty variable. Assignment with l_empty. l_object(100) := 'BLIP'; l_objects.delete.

Yes! Some of this was already addressed, but Patrick points out that there is no need to delete all the elements from the collection. Why not? Because PL/SQL is such a fantastic, hard-working programming language. The l_objects collection is declared within the nested subprogram. Consequently, the PL/SQL runtime engine automatically releases the memory for l_objects when the execute of get_objects terminates. Nice work, PL/SQL!

yalimgerger, chetanr009, offered several of the same observations as those above. Thanks for taking the time to tweet!

suggested removing "The FOR indx IN 1 .. l_objects.COUNT loop." But I do not see how the subprogram will compile without it. Perhaps you can clarify with a comment?

Here's a pretty picture driving home all the code that could be removed from this block:

Lessons Learned

PL/SQL is a purpose-built language for writing high performance, easy to maintain and very secure applications on top of Oracle SQL and the Oracle Database.

Because of its tight focus, the PL/SQL development team has been able to optimize syntax, garbage cleanup, and more.

The more you understand what PL/SQL does for you, the more easily you will be able to leverage PL/SQL to deliver applications on-time and on-budget.

Monday, January 4, 2016

My resolutions for 2016: a short, sweet, focused list

Oracle Database Insider newsletter asked me to do another round of resolutions for the coming year. Since I love it when other people act as though they are interested in what I have to say, I told my manager I would have to clear my calendar for a couple of days and get this done. He agreed - so long as I did it during my week off between Christmas and New Years.

Oh, OK.

Before getting to the resolutions, I encourage you to subscribe to this very interesting and useful newsletter.

Here goes....

1. Finish what I and we started. 

I have a long-standing and bad habit of getting all excited about new stuff: new programs, new applications, new websites, new programs…go, go go! But I am not so good at making sure that all the existing and really great stuff is “finished” (as useful and usable as it could be).

So for 2016, I resolve to consolidate what is already in place and make sure our users can get the most out of it. I am thinking about websites like Ask Tom, LiveSQL, PL/SQL Challenge, Oracle Learning Library, the Oracle Database Developer Choice Awards and…

2. Don’t let the screen own me.

Without a doubt, I spend waaaaay too much time in front of multiple monitors and screens (but at least I don’t do anything with my smartphone besides phone calls, texts and photos - and I do not own a tablet). 

Of course, I need to do that for my job, but I hereby resolve to get away each day, multiple times a day, to go outside, no matter the weather, feel the sun and wind on my skin, breathe deeply and celebrate the natural world.

Along the way, I will as a result think more clearly and write better code.

I think.

3. Teach my granddaughter, Loey (age 4), to write code.

Nah, just kidding. 

I believe children should be kept away from computers and technology for as long and as much as possible (and that includes television). They will have loads of time to get sucked up into the Borg known as Civilization. Take your children outside instead, encourage them to play in the dirt (side benefit: boosts their immune system as they grow up into - possibly - a world without effective antibiotics), appreciate trees with them, explore the real world, not a world made of code.

4. Identify and help “raise up” the next generation of PL/SQL experts.

I might not look it, but I am old. Well, 57, anyway. :-) PL/SQL is going to be around - and utilized heavily - for many years to come. We need “young blood” - people under 40, I am thinking - to understand and champion “the hardest working language in software.” (I just made that up, by the way).

The Oracle Database Developer Choice Awards program is designed to do this, broadly, but I will also complement it with a focused effort.

Of course, you could make it easy on me. Are you young(er), experiencing JavaScript framework whiplash, feeling like "Dang, those databases sure are important and sure should be utilized more than they are these days"?

Then get in touch. Let me know you are interested in getting more involved with application development on Oracle Database - and perhaps PL/SQL more specifically.


I could go on (anyone who's familiar with my books will attest to that). So tempting, but I will end with just one more:


I will keep my lists short and my priorities clear.

So that's it.

I hope you all have a very healthy and productive 2016.

Spend as much time as you can with family and friends.

Write code those same family and friends would be happy to maintain for you (!).