Want a free Oracle textbook? It's yours (for a price)

I have neglected this blog. I apologise.

I have an excuse, though. Two.  Firstly, I have been keeping busy contributing over at the All Things Oracle site. It's nice over there; you should check it out - but not just now.  Secondly, the projects I have been working on, of recent, have not involved a lot of interesting Apex, and so I have suffered for topics to write on for this blog.

However, I recently produced a series of articles for ATO called Oracle for Absolute Beginners.  It was received enthusiastically - so much so that the ATO guys and I discussed making it available for purchase on Amazon. Which I have done.

But you? I like you. And so I am making the book available for free here. Completely free. And there isn't a catch.

Okay, you're right, there is a catch. I would appreciate it if you would go to Amazon after reading it and leave an honest review. Please bear in mind as you do so, that if you're already an Oracle expert, the book is not necessarily aimed at you - so don't hold the fact that it doesn't contain a chapter on Oracle exadata or 12c cloud computing against it.

Got that? Thanks. Now download the book and then leave an Amazon review.

Oracle for Absolute Beginners: Part 4 - Multi-table queries

Read: Part 1, Part 2, Part 3

A wise man* once said: To build the Great Wall of China, you must start with a brick. In our previous articles we acquainted ourselves with our bricks; now it’s time to build. If we consider what we’ve learned so far – Select, Update, Insert, Delete – as unicellular organisms, what we’re about to do next is multicellular, big and beautiful – it’s like going from an amoeba to a bee, a butterfly, to Beyoncé.

[*that wise man was me]

Consider a real-world requirement that we might have of our Addressbook database; since it contains a list of our friends and their phone numbers, we will naturally want to see a list of their names and their phone numbers. Ah, but that presents a problem. Our friends’ names are in the FRIEND_NAME table, while their phone numbers are in the PHONE_NUMBER table. And complicating things further, we can only tell which number belongs to which friend by looking in the FRIEND_PHONE table. Aaargh!

JOINS

We could, of course, get the information by running a series of queries: one select to find our friends’ names and their friend_id; a second to find the phone_id of the phone number linked to each friend in FRIEND_PHONE; and a third query to find the number from PHONE_NUMBER using the phone_id we identified in our second query. So yes, it can be done. But hey, you can probably ride a unicycle across Siberia – but that didn’t stop them from inventing the car.

What we need are joins. We need a select statement that can query multiple tables at the same time.

The syntax for a multi-table select statement is as follows:

Continue reading...

Oracle for Absolute Beginners: Part 3 - Update, Insert, Delete

Read: Part 1, Part 2

A wise man* once said: So no-one told you life was gonna be this way. Your job’s a joke, you’re broke, your love life’s DOA. It’s like you’re always stuck in second gear. And when it hasn’t been your day, you week, your month, or even your year.

[*That wise man wasn’t me; but stick with me, I promise to bring this back around to Oracle SQL imminently. ]

So – since it hasn’t been your day, your week, your month, or even your year – you decide to go to Central Perk with your friends Ross, Rachel, Monica, Phoebe, Chandler and Joey.  Oh, and that new friend you made that’s also named Ross Geller.  The music is loud, the atmosphere is great, and a good time is had by everyone.

You wake up the next morning with a bit of a sore head and try to recall everything that went on the night before: you’d discovered that Chandler’s middle name is Muriel, and that Ross’ is Eustace. Rachel gave you her new phone number. Phoebe gave you her new address. Oh, and you had a big fight with new Ross and decided you no longer want to be friends with him.

You roll out of bed and groggily wipe your eyes: all this new information needs recording; you’d better fire up your database.

UPDATE

Since we thoroughly looked into SELECT statements in the previous part, we can now turn our attention to UPDATE statements. In SQL we use update statements to change existing records – not to create new records or to delete them – just to change them.

The syntax for update statements is as follows:

Continue reading...

Oracle for Absolute Beginners: Part 2 - SQL

Read Part 1

A wise man* once said, no one’s ever learned how to cook just by reading recipes. And so, since we painted in the background in Part 1, we are now going to roll up our sleeves and dive in. By the end of this article you will be reading and writing SQL, the lingua franca of databases.

[* that wise man was me.]

SQL

SQL stands for Structured Query Language (pronounced ess-cue-ell or sequel) and is the programming language used in the management of relational databases. And not just Oracle RDBMS; the code we are about to learn will work just as well with Microsoft’s SQL Server, IBM’s Informix, MySQL and dozens of others. SQL is very much the English of the database world; it is spoken in many environments.  This is one reason why the skills you are about to learn are very valuable; they are eminently transferrable.

SQL consists of a data definition language (DDL) and data manipulation language (DML).  What this means is that we use SQL not only to define the tables into which we plan to put our data, but to manipulate (query, edit, delete, stuff like that) the data once it’s in place.

Manipulating data using SQL is easy, as the syntax isn’t a million miles from the way we speak.  For instance, to select all the data from a table you would use the SELECT … FROM table_name command.  If, on the other hand, you wanted to update data, you’d use the UPDATE command; and the DELETE and INSERT commands pretty much do what you’d expect them to, too.

It’s easy.  Let me show you.

Continue reading...

Oracle for Absolute Beginners: Part 1 - Databases

A wise man* once said, an expert is someone who uses big words and acronyms where simple phrases would do just as nicely. So stand back and listen to this: Database, Relational Database, DBMS, RDBMS, SQL, Sub-queries, normalisation.

[* that wise man was me.]

So now that I’ve established my credentials by bamboozling you with arcane words and capital letters, let me tell you what the purpose of this series of articles is. By the end of it, you will be able to re-read that first paragraph and understand every word; or, if you would prefer that in more practical terms, you will be able to read – and write –  SQL, which is the programming language of databases.

Definitions

Let’s meet the main characters of our story: I’ll give you a couple of definitions; one building on the other.

A Database is an organised collection of data. Not yet sure what that means? Well, do you own an address book, either on your phone or in a physical book? That’s a database. After all, the addresses and phone numbers areorganised – with all friends whose names start with A being grouped separately from people whose names start with B or C or D.

A Relational Database is a database in which the data is organised according to type with the relationships being maintained between the differing types. Okay, that sounds a bit like Greek (or Dutch, if you’re Greek; or German, if you’re Dutch; or Xhosa if you’re German…), but it makes sense if you let me explain.

Dig out your address book again. Imagine all the names grouped together; and all the phone numbers grouped together in another list; and all the addresses in a third. On their own these individual lists might be interesting but not useful; but if we establish the relationship between the lists – this address is where that person lives and that’s their phone number – then our database takes shape.

Make sense? Don’t worry about it too much if it doesn’t; we’ll come back to it a little later. Let’s talk about Oracle now.

Continue reading...

Introduction to Instrumentation

The forest is thick with menace. The sunlight barely breaks through the leaves and is sludgy like curdled milk; the shadows of the branches look like gnarly fingers reaching to grasp at the pair of frightened small children.

As they wind down the path, Hansel leaves a trail of pebbles. “It will help us find our way home,” he says to Gretel, his voice quavering with fear.

There is a parallel somewhere in there with the world of Oracle, believe me. I know that you’re unlikely to be eaten by an ugly, wicked witch while writing PL/SQL (I can’t give you the same guarantees with Sql Server), but think of Hansel’s pebbles as debug messages, think of it as instrumentation.

Instrumentation is the practice of measuring the progress of your application’s performance as it runs, and is usually pre-baked into the software at the time the code is being written.  It is, of course, not a uniquely Oracle concept, but Oracle is all I know and all I will talk about in this article. So, if you got here by googling “Hansel and Gretel”, you may wish to leave now. 

Do You Need Instrumentation?

If you’ve ever asked:
• Why is my application suddenly performing so poorly?
• Which of my procedures needs tuning?
• Where exactly is my application crashing?
• What exactly is my code doing?

Or if you’ve ever said:

• Aaaaargh!

Then you probably do need instrumentation.

What Is Instrumentation?

Instrumentation, very simply put, is the practice of including your debugging into your code as the code is being written, and not after the fact, after the crash/issue/problem has happened. This way, when you need to understand what your code is doing, you can simply run your application and examine your logs.

What To Instrument?

No one understands your code as well as you do (I hope!) and so no one can supply you a full list of what information you want to capture in your instrumentation.  However, the following are some practices that are generally accepted as wise.

• Include instrumentation at the entry point of every procedure and function that you write.  The entry point, obviously, would be the first line after the BEGIN.

• Include instrumentation at the exit point of every procedure and every function that you write. The exit point of your procedures is right before your exception handling . Secondly,  I’m not your mother and so I don’t want to nag you, but I do hope that your functions generally have only one exit point – one RETURN – and are not littered with RETURNs within conditional statements and other digressions.  However, you should include instrumentation right before every exit point.

• If your routine accepts any parameters, you should include instrumentation outputting the values passed to each of these parameters.

• You will want instrumentation within your exception handling. You will definitely want to know if your code has raised an exception, and you will want to know what it was. Some Oracle experts argue that you should never have WHEN OTHERS THEN exception handling in your code. However, it may be a good idea to include it, instrument the exception, and then re-raise it.

• Instrument any significant points within the body of your code.  Got a conditional statement? Then instrument the various paths within it. Writing a cursor? Then instrument any parameters it takes and any output it spits out.

• You may also want to include a debug message logging the SQL%ROWCOUNT after significant DML.

Continue reading...

Review of Oracle APEX Best Practices (or the 200 day shower)

Over the past few days I have had two books on my bedside table: on my Google Nexus I have been reading David Eagleman’s novel Sum: FortyTales from the Afterlives, and whenever I put that down, I’ve been burying my nose in the dead-tree (i.e. non-ebook) version of Oracle APEX Best Practices by Learco Brizzi, Iloon Ellen-Wolff and Alex Nuijten. 

This article is about Oracle APEX Best Practices, of course, but let’s talk about the short story collection, Sum for a bit. One of the stories in the book hypothesises that in the afterlife we all relive our lives – with one difference: all our actions are grouped by an ‘order by’ statement. So instead of waking and sleeping, waking and sleeping, we spend 30 unbroken years asleep and then 40 continuous years awake. We spend 7 straight months making love, and then 6 full days clipping our toenails. We shower for 200 days. It’s a good book; read it.

Oracle APEX Best Practices is a good book too. I want to make that clear.  Two of the authors, Learco Brizzi and Iloon Ellen-Wolff are new names to me, but I have visited Alex Nuijten’s blog many times in the past and have never been let down. So I googled the other two authors, and what I read convinced me of their bona fides.

Not that the book doesn’t speak for itself. At 200-odd pages it is a good size; it is written in clear, precise English; and it covers Apex, from top to tail – from installation, through security and debugging, to deployment. It is mostly aimed at Apex beginners, but there is enough in-depth explanation in it to make it invaluable to intermediate Apex-ers too. It is a very good book.

But… 

In the afterlife in Sum, you spend 14 minutes experiencing pure joy, and then 27 long hours of intense pain. I have spent 2 full paragraphs praising Oracle APEX Best Practices; it is time to highlight my quibbles.

The first annoyance is this: to keep the page count down, the authors often ended sections with the url of some Oracle documentation or a blogpost containing more information. This is perfectly fine in an ebook, but what am I supposed to do with this in a paper book? I cannot type a 200-character url into my browser's address bar. Physical books cost more money than ebooks; they should not provide a worse experience.

My kingdom for a bit.ly!

I do also have some questions about the title. Best practices? This is a very good Apex book, no doubt, but I wouldn’t call it a best practices book. I expect best practices books to prescribe the way I develop my application and not just explain the options.

Finally, the authors occasionally struggle to stay true to their topic: Apex. In Chapter 2: Leveraging the Database, they spend 30 pages explaining how to use analytic and aggregate functions in Oracle databases. I was very grateful for the lesson – but what the hell is that doing an Apex book? What next - a fried rice recipe?

<\criticism >

Do not be put off by my quibbles; this is a good book. It was, unfortunately, released just on the eve of Apex 4.2 and therefore only covers up to 4.1; however, I do not think that this is a problem. Best practices are not version-dependent.

In Chapter 4, the authors discuss application security, advising on how to guard against SQL injections and URL tampering, and explaining Access Control Lists and dealing with password complexity rules using APEX_INSTANCE_ADMIN. And Chapter 5 is a 30 page essay on debugging and troubleshooting. Both these chapters are excellent and, along with the chapter on printing (BI Publisher, Apache FOP, Cocoon and JasperReports), are worth the cover price on their own (insofar as any software development book is worth its crazily-inflated price).

However, more than the long pieces on complex subjects, what I found useful in this book were the little reminders of things that I know and overlook: set up your User Interface Defaults before diving in and building your application, for example.

And even though I have said that the section on analytic functions, aggregate functions and index-organized tables should not be in an Apex book, I must concede that it is packed with useful (non-Apex) information.

According to Sum: Forty Tales from the Afterlives, we spend 1 whole year of our lives reading books. I do not regret the hours that I have spent on this one. 

(Full disclosure: I received a complimentary copy of this book from the publishers, Packt Publishing, in return for an unbiased review)