OracleBlog

While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?

INSERT INTO table (column1, column2)
VALUES (value1, value2), (value1, value2);

ERROR at line 1:
ORA-00933: SQL command not properly ended

Unlike some other databases (DB2, PostgreSQL, MySQL), Oracle doesn't support multirow inserts (yet). Instead, you need to execute these as separate statements.

INSERT INTO table (column1, column2)
VALUES (value1, value2);

1 row created.

INSERT INTO table (column1, column2)
VALUES (value1, value2);

1 row created.

Edit: Courtesy of Laurent Schneider (see the comments), here are two "tricks" in inserting several rows with the same statement.

Method #1:
INSERT ALL
INTO table (column1, column2)
VALUES (value1, value2)
INTO table (column1, column2)
VALUES (value1, value2)
...etc...
SELECT * FROM DUAL;

Method #2:
INSERT INTO table (column1, column2)
SELECT value1, value2 FROM DUAL UNION ALL
SELECT value1, value2 FROM DUAL UNION ALL
...etc...
SELECT value1, value2 FROM DUAL;

The moral of the story is not to expect Oracle to comply with the ANSI/ISO SQL standard every time. In some ways, like this one, they do not comply, and of course Oracle offers many extensions to the standard.

For more information, consult Appendix B of the Oracle SQL Reference, which deals very briefly with how your version of Oracle complies with the standard.

As for the ANSI SQL standard itself, I'm not aware of any free on-line source. If you want one, you'll have to purchase a copy directly from ANSI/ISO. There are different levels of standard compliance, Oracle qualifies as entry-level compliance. I don't know exactly what standards are entry-level and which ones aren't, but apparently multi-row inserts aren't.

Along the side of my page, you'll see my favourite Oracle blogs listed. I carefully maintain this list of fellow enthusiasts whose opinions and insights I most especially want to follow among the seemingly hundreds of Oracle blogs that are out there. Studying them, I think you'll find that each of them share the same core qualities listed below.

1. Accuracy

Accuracy is an absolute must. Just because its an informal blog from an independent individual shouldn't relieve it from the same standard of accuracy that you'd find in a paper published by Oracle themselves.

For just one example, consider Jonathan Lewis' blog, and his track record of reliable solutions. But you don't have to be a guru to be accurate! Indeed, if your articles are targetted to novices, that's even more reason why you should make sure everything you write is fully tested and error-free, right?

2. Relevance

The occasional off-topic article can be a pleasant change of pace, as can writing about some other unrelated technology. But write too many, and then nobody knows if they're going to get an insight into the performance of the MINUS operator, or something about spiders.

A great blog must consistently provide material relevant to the community. Readers can also quickly sniff out and abandon bloggers who are doing nothing more than marketing their products, their books, or their services.

By contrast, consider Pete Finnigan's blog. Each and every visit, I know I'm going to get his latest insight into Oracle security matters. Comb his articles, and I doubt you'll come across any covering the details of his hotel room at UKOUG or a rant about Wordpress.

Bear in mind, that a great Oracle article doesn't necessarily have to be technical to be relevant. For example, consider Coskan Gundogar's recent series aimed at new DBAs, and invalid DBAs, which you'll find to be not only relevant, but also thought-provoking.

3. Genuine Insight

A great Oracle blog has to be more than just a syntax reference. Some crappy blogs do nothing more than recycle someone else's work, either by linking or actually copying it, which falls anywhere between plagiarism, and a complete waste of time.

Instead, a great Oracle article is the one that shows you HOW to solve problems. It teaches you a new approach to using Oracle technology. Among my favourites are the articles that provide insights that I couldn't find anywhere else, and that I'm not likely to have experienced or figured out on my own.

The blogs that compose the list on the side are replete with examples, but consider Andrew "Arfur C" Clarke, and his recent post on the INSERT ALL syntax. His article, which was built on Pythian article about multi-table inserts, guides us through his thinking (with examples), all the way to his neat solution.

4. Readability

Oracle is very complex technology. Some people think that they are impressing everyone by showing how they're capable of coming up with sophisticated and complex solutions. What a load of crap. Anyone can do that, and no one wants to read about it. A great Oracle blog, by contrast, can take a complicated problem and make it look so easy and simple that even Shrek can understand it.

A great Oracle blog uses easy, simple, every day language, and remembers that not all its readers enjoy English as their first language. These blogs rarely forget to explain and define all the acronyms, abbreviations and industry terms used.

As just one example, consider Lewis Cunningham's latest article about implicit vs explicit conversion. A big part of Lewis' popularity is his ability to present his ideas in clear, plain language accessible to readers of all levels. Notice as well that his articles deliver specific, relevant, useful Oracle insights.

5. Posting Frequently

A great blog doesn't need to have a new article each and every day, but if there isn't new material of some kind of regular basis, people may assume its been abandoned. Blogging on a regular basis allows people to become familiar and comfortable with its style and approach, and look forward to its perspective on future topics.

Like most readers, I only have the time to regularly follow a limited numbers of blogs (in my case, up to 20). How many of my favourite blogs did I have to stop following because the author stopped blogging regularly? (Edit: List removed because it was easily misunderstood as criticism)

6. Concise, tested, working examples

Your article may have been accurate for your purposes, for your version, and in your environment, but what about mine? Of course I trust you, but by providing a concise, tested, working example, I can verify if your solutions are accurate for me in my world.

A great Oracle blogger knows that examples are critical for communication. Despite your best efforts, if the reader still doesn't understand the material, they at least have a working example to play with until they do. They can even use it as a basis for future research, including coming back to you with questions. If you need an example, consider Laurent Schneider, who has become famous for helping thousands by posting working solutions, both on Oracle forums and his blog.

7. Googlability.

Within a week, most people have read a newly-posted article. Then it dies off, and is usually forgotten, never to be read again. After all, how often do you find yourself browsing the archives? Never ... not when you're keeping up with all the new articles. This phenomenon is especially tragic for the great articles written by new bloggers before they become popular and/or start appearing on aggregators (for example, Don Seiler, who has maintained an excellent blog for years but was only recently added to some aggregators).

By including searchable phrases within the article and in its titles, there's a good chance that it can be picked up by Google when someone is researching that particular topic. Great articles include common search phrases, like Oracle error messages, specific commands, and popular expressions.

8. Opportunity for Discourse

A great article is one that covers an interesting topic that not only ties into the work of other bloggers, but even stimulates further discussion. All the blogs in my list allow comments, and in every case the comments are read and followed up upon.

Writing articles that generate discussions makes us more than just a collection of random observations, and into a true Oracle blogging community. It evolves us into a community with articles that build on the ideas of others, and that becomes more than just the sum of its blogs.

9. Pointers to more information

Typically the restrictions imposed by the blogging medium allows only a short look at a particular topic. Therefore, in anticipation of the thirst for more information that it stimulates, a great blog consistently directs the reader to such sources. Furthermore, this helps readers who are experiencing similar problems (but not the exact same one).

For a great example, look no further than the 2006 Oracle blogger of the year Eddie Awad. His recent article on forward declaration and mutual recursion not only contains information hard to find elsewhere, working examples, and an active discussion, but Eddie also unfailingly links to Oracle documentation.

10. Enthusiasm, and Humour

Oracle can be dry, I admit it. It can sometimes feel like a big, complex, bloated mass of three-letter initials and acronyms. That's why it's a treat to catch the humour and/or enthusiasm a colleague might have for a particular topic.

Don't forget that the most popular Oracle blog of all is undoubtedly Tom Kyte's, who is well-reknowned for both his tireless enthusiasm, and his entertaining style (and wit). As for humour, the perfect example is the Oracle WTF maintained by William Robertson et al.

But the energy doesn't necessarily have to be humourous, nor positive and uplifting. Negative, critical or cynical rants written with equal energy can be highly captivating, such as Dizwell's Howard Rogers' rants, which are the stuff of legends. (As are Tim Hall's)

Great Oracle Blogs

I would like to thank my fellow Oracle bloggers listed to the side, both past, present and future. Your insights have consistently improved my understanding, interest and appreciation for Oracle. That is certainly the hallmark of a great Oracle blog. Furthermore, you have all been a great influence in my own blog (ie THIS blog), which surely wouldn't have been this much fun to write without you.

With that in mind, I just updated my list with another great Oracle blog, that of Chris Foot. I recommend reading his latest series on his favourite features in Oracle 10g.

Like most of us, I still join tables in my SQL queries the old-school way. Simply put:

SELECT whatever
FROM table1 t1, table2 t2
WHERE t1.id = t2.id
AND t1.value > 10;

But increasingly often I run into people who use ANSI joins instead. They were either introduced to SQL with Oracle 9 (or Sybase 12, etc), and were taught to use the SQL standard way, or else they made the conversion at some point in the past few years. They would instead write that query like this:

SELECT whatever
FROM table1 t1
JOIN table2 t2 USING (id)
WHERE t1.value > 10;

Then they look at me all smug ... "Look how I have separated the JOIN clauses from the WHERE clauses. Isn't that infinitely more readable? Now go back to your cave."

I confess, I do like the elegance of separating those clauses. Our business analysts find that format much more readable, too, as do those who use several different databases. Every time they see one of my queries, especially a full outer join or a (+) in it, their nose starts to bleed.

Incidentally, if the columns are named differently, you'd use this syntax instead (which is less of a leap for old school SQL lovers):

SELECT whatever
FROM table1 t1
JOIN table2 t2 ON (t1.id1 = t2.id2)
WHERE t1.value > 10;

And, of course, you can have multiple joins, and can even nest one join inside another. You can use all the different types of joins, like INNER, OUTER, LEFT, RIGHT, UPSIDE-DOWN, INSIDE-OUT, whatever. Ok, not those last two. Tim Hall has some examples.

I started using ANSI joins for the first time recently. There certainly doesn't seem to be any performance hit. Of course, I wasn't expecting one, because you'd think Oracle would be smart enough to do both things the exact same way. I can't say for certain if one is better than the other, I guess you have to look at it case-by-case with your profiling tools. But I certainly haven't seen any difference.

The one thing I'm not crazy about is the NATURAL JOIN. In the example above, if id was the only column that those two tables had in common, they could specify is as a NATURAL JOIN and leave off the USING. To wit:

SELECT whatever
FROM table1 t1
NATURAL JOIN table2 t2
WHERE t1.value > 10;

What a NATURAL JOIN actually does, is join the two tables on all columns that exist in both tables. It does mean your query will still work if the structure of the table changes, but is that really what you want? Call me a caveman, but I like to explicitly state exactly what I want a query to do, and that includes exactly on which columns I want to join.

If you think I'm being too harsh, you should Ask Tom for his opinion:
"Natural joins -- a bug waiting to happen.",
"I strongly, vehemently strongly, discourage the use of this feature called natural joins.",
"To join tables by their column names -- how patently just a bad idea",
"Never never never use this feature."

I don't intend to use this one criticism to denounce ANSI joins altogether, far from it. Traditional joins (especially full outer joins) can look messy, especially the aforementioned group of new graduates, business analysts and multi-database SQL programmers.

Not everyone has really enjoyed moving from the traditional join syntax to ANSI syntax, including Doug Burns and Jeff Moss.

As you can see from the dates on those, I'm not the first one to write about this (but hopefully the last). I thought Mark Rittman was the first to expound on this, over 3 years ago, but he links to Jim Czuprynski, who appears to be truly the earliest crusader for the USE of ANSI joins, over 4 years ago. But, reminiscent of Wrath of Khan, it was actually Eddie Awad who first put the bug of ANSI joins into my ear. He's got a crash course on the different types of ANSI joins, but of course, that type of information you can find almost anywhere.

I'm not going to endorse that you immediately switch to ANSI joins, especially since if you haven't by now, you probably have no such intention anyway. But I think it makes a lot of sense for my fellow cavemen to at least be aware of it, and know how to use it.