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.
17 November 2010
What are the Datatypes of TRIM arguments (16 November quiz)? (1647)
The 16 November quiz tested your knowledge of the TRIM function. In the explanation of one choice, I wrote:
"Both trim character and original string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if the "source string" (against which trimming will be performed) is a character datatype and a LOB if the source string is a LOB datatype. The return string is in the same character set as the source string."
This is, to be honest, mostly copied from Oracle documentation.
One player, Tony, wrote with the following perspective:
"I suggest that this is incorrect. The example in the documentation immediately below that [the description of argument types] shows trim(leading number from date) - yes it performs an implicit to_char() around both, but the arguments themselves are of type NUMBER and DATE."
Here's my view on this: the datatypes of the arguments or parameters of TRIM are defined by that function's header.
It's true that with implicit conversion you can provide a number in your call to TRIM, and Oracle will convert it into a string. Regardless, by the time that TRIM is actually invoked, it is being passed a string, not a number.
So number or date cannot be classified as datatypes of TRIM parameters.
Your thoughts?
SF
Subscribe to:
Post Comments (Atom)
According to (and not only to) http://en.wikipedia.org/wiki/Function_parameter there's a distinction between the terms "argument" and "parameter".
ReplyDeleteThe (formal) *parameters* of TRIM() can be of (N)CHAR, (N)VARCHAR2 and (N)CLOB datatypes ... yes, so far OK.
However, via the implicit datatype conversion the actual *arguments* can also be of datatypes NUMBER, DATE, etc.
I have lots of troubles with considering types are need the implicit conversion to be allowed types (that also happened with some previous question).
ReplyDeleteOn other side, there are some overloads that are not valid (will trow PLS-00307 "too many declaration <...> match this call") so I understand that if the implicit conversion will never get an error you could consider that valid.
For that I mean that I think that a function that as a parameter that is an INTEGER should not be said to allow NUMBER or VARCHAR2 (even is there is an infinite number of values where that work there are at least one where that would not work).
In the 11gR1 documentation, Oracle generalizes the argument description based on the characteristic of being implicitly convertible to a VARCHAR2 instead of using an explicit list of types. It states, "Both trim_character and trim_source can be VARCHAR2 or any datatype that can be implicitly converted to VARCHAR2." As Pierre states, there is a fundamental difference between parameters and arguments. Regardless of any implicit type conversions Oracle performs, the type of the argument passed does not changed. The quiz is correct as originally presented.
ReplyDeleteYes, it is important to distinguish between formals (also known as parameters) and actuals (commonly referred to as arguments, though I don't think that is very rigorously followed in Oracle).
ReplyDeleteI will change the answer to also include "any datatype that can be implicitly converted to" one of those types (VARCHAR2 or CLOB).
Thanks! SF
Sometimes I think the assessment of quiz wording goes a little too far.
ReplyDeleteAny time you read documentation about single row functions, and review the accepted datatypes, does the thought of implicit conversion immediately come to mind? Do you think "how else can I utilise this function beyond the intended design?"
I don't. I see "this feature expected datatype xyz", and I move on.
rant over
Typically qualifiers "formal" and "actual" are applied to the word "parameter". The word "argument" is usually considered as a synonym for the "actual parameter". So, strictly speaking, the wording of the quiz does not reflects to the intention.
ReplyDeleteThat said - Oracle documentation is (way too) lax in this area.