Tuesday, June 2, 2015

About terminating characters for SQL statements

I recently tweeted this link to an article you published in the November/December 2004 edition of Oracle Magazine.

Bryn Llewellyn, PL/SQL Product Manager, followed up with a message I thought we might all benefit from reading, so here goes:

A side box in the article ends with: "Dynamic PL/SQL statements must end in a semicolon; dynamic SQL statements may not end in a semicolon."

That's like saying << when wearing a red pullover, you must make subject and verb in a sentence agree in number; you must not write "myself" when you mean "I">>. Taken literally, it's not untrue. But the implication is that things are different when you wear a differently colored pullover. But, of course, they are not different.

It's just a property of SQL that it has no larger building block than the single statement. There is no need for a terminator character. And the use of one, whatever might take your fancy, causes a syntax error. This a universal truth. It has nothing to do with dynamic SQL.

The real point is the special nature of PL/SQL's embedded SQL. And, for that matter, the rules of the SQL*Plus scripting language.

It's terribly sad that almost every single example SQL statement that you see, anywhere on the planet, presents it as the SQL*Plus command that you'd use submit it in the regime you get following this SQL*Plus command:

SET SQLTERMINATOR ;

I hate to see you encouraging wooly thinking with a rote rule rather than attempting to expose the correct mental model. Best practice, in any discipline, starts with sound understanding of basic concepts.

I recommend putting this in one's glogin.com:

SET SQLTERMINATOR OFF

The discipline that this will require will encourage the right way to think.

No comments:

Post a Comment