BETWEEN NVL (start_in, -2**31+1) AND NVL (end_in, 2**31-1)which drew responses from several players: 1. In 10/20's quiz, the 3rd choice (calling update_parts() with no parameters) causes the "if NULL" logic to activate in the NVL statement of the BETWEEN statement. I have never seen ** notation before; a collegue (who used to program in FORTRAN) said this morning that it indicates raising the preceding number to a power. I have always only seen the carat ^ used for that. When taking the quiz, I quickly tried it out in Toad with: SELECT -2**31+1 from DUAL; to see what it did. But Toad said, "ORA-00936: missing expression". When I searched Oracle's 10g R2 on-line documentation for "**", it comes back with "Your search term ** did not match any topics." So I was hoping it was a typo and left it unchecked. (In fairness, a search on "^" comes back with the same message: "Your search term ^ did not match any topics.") Today, I copied the text of the quiz, ran it all in Toad and the output worked perfectly. So it's not a typo (rats!!). My question is: why does that notation work when used as it is in the procedure, but not straight SQL? My suggestion is: if ** is old-fashioned notation, maybe the quizes can use up-to-date notation? 2. There seemed to be a problem in today's quiz that would make the procedure not compile. You referenced 2 NVL replacement values such as -2**31+1 that seem wrong, surely there is one '*' too many? For this reason I stated that none of the answers were correct. 3. If it wasn't for the double * on the NVLs, I would have chosen the update_parts(-20000,-10000). Was the double * intentional? Well, it certainly was intentional to use "**". I didn't think this would cause very much consternation, but if you've never seen them before, it is understandable that you'd think this was a mistake. It is not. "**" is the exponention operator, as is clearly stated in the Oracle 10g doc here. It is equivalent to using the POWER built-in function.
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
21 October 2010
Questions about "**" from 20 October Quiz (1523)
The 20 October quiz included this line of code:
Subscribe to:
Post Comments (Atom)
Players who are not familiar with the exponentiation operator might also not know it is the one numerical operator that is supported in PL/SQL and not in SQL.
ReplyDeleteIn response to why "**" works in PL/SQL but not in a SQL query:
ReplyDeletePL/SQL has an exponentiation operator but SQL does not.
Even within a PL/SQL block, "**" cannot be used in a SQL query.
In addition to jhall62:
ReplyDeleteEven within a PL/SQL block "**" cannot be used in a SQL query ... or any other SQL statement.
Ok. My bad. I thought about testing it in Pl/SQL but didn't get to it :\ and even if I tried, I would probably put it in and select into statement.....
ReplyDeleteMy bad...
Live n' learn..... live n' learn :D
This is highlighting one of the things I love about this quiz: it's ferreting out what I know from what I assumed from what I didn't know before.
ReplyDeleteI started playing in July and have already learned so much that my code now looks very different from code I wrote this spring. And cleaning up old scripts with newly-learned features feels like spring cleaning. Way cool!
Thanks, Maase. It makes me very happy to read things like this - and energizes me to continue to and extend and expand the PL/SQL Challenge.
ReplyDeletePlease don't hesitate to tell other PL/SQL developers about the Challenge - for example on the OTN PL/SQL forum and other discussion groups. I am not going to do that. Too crass and self-serving. But you sure could!
Thanks, SF
I was more caught out with the update_parts(); call.
ReplyDeleteOccasionally when the cookies crumble I'll call a procedure with no actual parameters, but including the brackets and I get PLS-201, so I think a call like that is invalid in PL/SQL.
I didn't realise that if the formal parameters are defaulted, this type of call will be valid.
Live and learn.
To the use of brackets at procedure/functions with no parameters: from my experience you can use the empty brackets on every procedure/function, you have created. The only problem I found was at the functions inside the package "SYS.STANDARD". The statements "SELECT USER FROM Dual;" AND "SELECT SYS_GUID() FROM Dual" both works perfect whereas "SELECT USER() FROM Dual" and "SELECT SYS_GUID FROM Dual" both don't work and throw an exception.
ReplyDeleteAnd somewhere in the documentation about the object oriented features Oracle writes that you MUST use empty brackets, if you call an parameterless method inside of SQL-statements (even if it is not true, I have tried it a long time ago).
I always use brackets when calling a procedure of function, even if it has no parameters, because if someone else inherits code from me and sees a line like "var := func;" he must seek for the declaration of "func" to see if it is a variable or a function. If the line is "var := func();" he immediately knows that it is a function.
Some words about it from Steven?