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.
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.
Comments
Post a Comment