Tag: sql

Real Talk : PL/SQL and SQL as your only development skill

notes-514998_640This morning I was asked a question about the job opportunities for a PL/SQL developer these days. I’m talking about someone with good SQL and PL/SQL skills, but limited, or no, knowledge of other development languages.

I think most people know I’m a big fan of PL/SQL. If you have good SQL skills and you know PL/SQL well, you can do pretty much anything with an Oracle database, including all types of web service and web development. Throw in some APEX skills and you can be super productive as a web developer against Oracle databases.

So back to the question, what are the job opportunities for a PL/SQL developer? In the UK at least, that’s not a great place to be right now. When I first started with Oracle technology it was not uncommon for companies to employ developers just to code PL/SQL. There are still jobs like that available, my employer has two PL/SQL contractors right now, but the market for a programmer with just PL/SQL is on the decline. Search for “programming language popularity” and you will see a number of indexes don’t include SQL and PL/SQL in the top 20 lists. Search for “enterprise programming language popularity” and you will see SQL and PL/SQL appear. There may be flaws in the way the information for these lists is gathered, but you get the message.

That’s not to say SQL and PL/SQL skills are not of value, just that those skills alone are no longer enough. They have to be part of a package that includes other development skills.

Most people I talk to work in organisations that use multiple database engines (Oracle, SQL Server, MySQL, several NoSQL engines), so having a person that can only do PL/SQL development means they are of limited use compared to someone that also knows Java, C# or Javascript to a high level. That is, development skills that span database engines.

In a similar way, my current employer won’t commit to APEX as a strategic development platform because it is just for Oracle databases. Using database links to other engines to allow you to continue using APEX against them is not strategic. 🙂 In the same way, we have a lot of PL/SQL right now, but in the future I can see this being of less importance compared to other skills that are multi-engine. Do I like this situation? No, but it seems to be where we are right now.

Of course, this could be a conversation about “Java/C#/Javascript as your only development skill”. Development in todays world requires multiple languages, each serving a different purpose. It could also be a database engine discussion. I can’t imagine ever working for a company again that doesn’t expect me to look after MySQL, SQL Server and other engines, as well as Oracle.

I hope this doesn’t come off as negative. I love SQL and PL/SQL and I would love to be able to tell you these skills alone would set you up for life, but that would be a lie. As a developer, you are forced to follow the market and the market says you need multiple development skills to survive. I hope you pick SQL and PL/SQL as part of your skill set, as they are still very important in enterprise companies, but in the current climate betting your whole development career on a single language is not a safe bet. 🙂

Cheers

Tim…

PS. Us old folks will cling on until the bitter end. 🙂

Video : CASE Expressions and CASE Statements

Today’s video is about CASE expressions and CASE statements.

The guest in today’s video is Scott Wesley, who is currently plugging his new book called Pro jQuery in Oracle Application Express. He will no doubt use the vast quantities of money he makes from the books sales to throw outlandishly extravagant barbecues, because that’s all they do down under. 🙂

Cheers

Tim…

Video : Flashback Version Query

Today’s video gives a quick run through of flashback version query.

If you prefer to read articles, rather than watch videos, you might be interested in these.

The star of today’s video is Tanel Poder. I was filming some other people, he saw something was going on, came across and struck a pose. I figured he knew what I was doing, but it’s pretty obvious from the outtake at the end of the video he was blissfully unaware, but wanted in on the action whatever it was! A true star! 🙂

Cheers

Tim…

Video : SQL/XML (SQLX) : Generating XML using SQL in Oracle

Another video fresh off the press.

If videos aren’t your thing, you can always read the article the video is based on.

The star of this video is Kevin Closson. Kevin’s a really nice guy and has a brain the size of a planet, but you know somewhere in the back of his mind he’s wondering what it would be like to hunt you down, kill you and mount your head above his fireplace. 🙂

Cheers

Tim…

Video : XMLTABLE : Convert XML into Rows and Columns using SQL

Here’s an Easter present for everyone out there. 🙂

If you are not a fan of videos, you can read the article it’s based on here.

The cameo in this video is Mike Hichwa of APEX fame, along with a rather rowdy bunch who try to muscle into the video. See the outtakes at the end.

Cheers

Tim…

Video : The MERGE Statement

After a what seems like an eternity of being ill and having a dodgy throat, followed quickly by a couple of conferences, I’ve finally got back on the horse and recorded another video.

I was explaining a specific aspect of the MERGE statement to one of my colleagues and while I was doing it I was thinking, “Have I done a video on MERGE yet?” Now I have.

The cameo for this video is Cary Millsap. If you watch the out-takes at the end you will see the level of respect and trust I have garnered in the community. The words confused and suspicious spring to mind! 🙂

An honourable mention goes out to James Morle for videobombing. 🙂

Cheers

Tim…

SQL for Beginners : Videos and Articles

love-sqlI’ve been saying for some time I should do some more entry level content, but it’s been kind-of hard to motivate myself. I mostly write about things I’m learning or actively using, so going back and writing entry level content is not something that usually springs to mind.

Recently I’ve got involved in a number of “grumpy old man” conversations about the lack of SQL knowledge out there. That, combined with a few people at work getting re-skilled, prompted me to get off my ass and give it a go. It’s actually quite difficult trying to get yourself into the head-space of someone who is coming fresh to the subject. You don’t want to pitch it too low and sound patronizing, but then pitching it too high makes you sounds like an elitist dick.

Anyway, after completing the Efficient Function Calls from SQL series of videos, I decided to jump into a SQL for Beginners series. I’m also putting out some articles, which are essentially transcripts of the videos, to allow people to copy/paste the examples. More importantly, they have links to articles with more details about the subject matter.

Once I’ve done a quick pass through the basics, I’ll start adding a bit more depth. I’ll probably dip in and out of the series. If I stick with it too long I’ll probably go crazy from boredom. 🙂

If you know someone who is fresh to SQL, can you ask them to take a look and give me some feedback? It would be nice to know if they are helpful or not.

Cheers

Tim…

“a SQL” or “an SQL”?

This is an age old question and of course the answer depends on how you say “SQL”.

  • … a Structured Query Language statement…
  • … a Sequel statement…
  • … an Es Queue El statement… (I say it this way)

Different people say it different ways. Most of the time I don’t notice, but I just read something by another writer and kept seeing “a SQL …” and it was freaking me out. Then I realised I always write “an SQL …”, which reads “an sequel …” to some people, which sounds really stupid. 🙂

According to the Oracle docs it is sequel, so “a SQL …” is the correct way.

“SQL (pronounced sequel) is the set-based, high-level declarative computer language…”

The MySQL docs go the other route.

“The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.”

According to Wikipedia, it doesn’t matter either way.

“The original standard declared that the official pronunciation for “SQL” was an initialism: /ˈɛs kjuː ˈɛl/ (“es queue el”).[11] Regardless, many English-speaking database professionals (including Donald Chamberlin himself[37]) use the acronym-like pronunciation of /ˈsiːkwəl/ (“sequel”),[38] mirroring the language’s pre-release development name of “SEQUEL”

If one of the designers can’t make his mind up, what hope to we have? 🙂

Anyway, when you are reading my stuff and you see “an SQL …” everywhere, it’s correct for the way I say SQL. 🙂

Cheers

Tim…

PS. All the other typos are just plain typos because I’m practically illiterate. 🙂

PPS. PL/SQL is a lot simpler as it is “a PL/SQL …” regardless of how you say it. 🙂

Update: The general consensus from comments, twitter and emails seems to be:

  • When you write it, it should be “a SQL …”, regardless of how you say it. Taking me back to school, Paul Steffensen said, “if it starts with a vowel it’s an, otherwise it’s a”. In my reply, I mentioned that “an Es Queue El …” does start with a vowel. 🙂 That being said, “a SQL …” goes with the basic written language, the sequel version and the full wording of the acronym, so it fits more of the cases.
  • If, like me, you say “Es Queue Ell”, you should probably say “an Es Queue Ell” when speaking, but still write it “a SQL …”
  • Andrew Taylor said, “just to be on the safe side I say *some* SQL.” Pure genius!
  • In many cases, you can probably reword the statement so the “a” is not necessary. This feels like a cop out, but it might be what I end up doing. 🙂

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c

I’ve spent the last couple of evenings playing with the new SQL pattern matching feature in Oracle 12c.

I’m doing some sessions on analytic functions in some upcoming conferences and I thought I should look at this stuff. I’m not really going to include much, if anything, about it as my sessions are focussed on beginners and I don’t really want to scare people off. The idea is to ease people in gently, then let them scare themselves once they are hooked on analytics. 🙂 I’m thinking about Hooked on Monkey Fonics now…

At first glance the pattern matching seems pretty scary. There are a lot of options and as soon as you throw regular expressions into the mix it does make your head swim a little. After a couple of half-baked attempts, where I found convenient excuses to give in when the going got tough, I finally sat down and plugged through the docs. If you actually RTFM it is a lot easier than hoping to wing it. Who’da thunk it? 🙂

I’ve tried to keep the article really light. The docs are pretty good for this stuff (if you read them) and they have a lot of examples. I started adding more and more detail to the article, then chopped most of it out. There is no point regurgitating all the options when it is in the docs. Most of the examples I’ve seen before just talk about basic patterns, like V and W shapes, but it’s quite simple to do complicated stuff once you start playing. In fact it takes more time to set up the example data than it does to figure out the queries to bring it back.

In the near future I will be copy/pasting examples and adjusting them or just sitting with my article and the docs when trying to use this stuff. I think it’s going to take a long time before I can type this stuff from memory. Partly that’s because I can’t see myself having lots of cause to use it. I can’t think of any scenarios I’ve experienced where this would have been a natural fit. Having said that, I’ve never worked in things like stock markets, betting and stuff like that where I can imagine this sort of pattern matching is needed all the time. I seem to remember one person at a conference, who shall remain nameless, saying this feature was one of their drivers for upgrading to 12c. I wonder if that was for real or an exaggeration?

Anyway, if you need this sort of analysis, I think it’s worth checking out, but try to remember it’s not as scary as it first looks. 🙂

Cheers

Tim…

Should SQL have a compulsory ORDER BY clause?

How many times have you heard someone say, “The rows always came back in the correct order, but since the *event* it’s now wrong. The *event* may be an upgrade or some maintenance task on the table etc.

Oracle clearly say,

“Use the ORDER BY clause to order rows returned by the statement. Without an ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.”

This also applies for the GROUP BY clause, that haunted people during their 10g upgrades.

“The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.”

Both these statements can be found in the docs for the SELECT statements.

So why not make the ORDER BY clause compulsory? The statement could include an explicit NOORDER, like this.

SELECT * FROM emp e ORDER BY e.empname

SELECT * FROM emp e ORDER BY NOORDER

Of course, if that happened I would probably be in the group of people complaining about how ridiculous it is to force us to specify a clause we don’t need, but it would make people think… 🙂

Cheers

Tim…

Update: There seems to be a little confusion about what I am saying. I am not suggesting ordering should be mandatory. I am suggesting the ORDER BY clause could be mandatory, making you have to explicitly say if the output from the query is ordered or not. So these two statements would be functionally identical.

SELECT * FROM emp

SELECT * FROM emp ORDER BY NOORDER