29 April 2011

More semantic analysis - and objections - in 28 April quiz (2244)

This quiz asked: "Which of these choices describe a way in which a value can be assigned to a PL/SQL variable?"

We scored as correct the following: "Change the value of a variable by passing it as an argument to a procedure or function." Why? Because you can define an OUT or IN OUT parameter and then change the value of the variable passed as an actual argument to that parameter within the body of either a procedure or function.

Several players wrote with objections like these:

"The variable is not actually changed by the act of passing it as a parameter to a procedure or function. It is actually being modified by the assignment that happens in the body of the procedure or function. I believe that your test code for that option is actually just another example of the first option (Assign a value to a variable using the assignment syntax)."

"A variable can be assigned by passing it as a parameter to a procedure, but it cannot be assigned by passing it as a parameter to a function, so it is ambiguous whether the last option should or should not be selected."

My responses:

First, you can define OUT and IN OUT parameters in a function's parameter list, so "procedure or function" in the question does not affect the correctness of the answer. Having said that, I recommend, as do many others, that you do not include OUT and IN OUT parameters in a function's parameter list. It restricts how you can use the function (cannot call it in an SQL statement, for example), but I also believe that your functions are easier to understand, use and maintain they it only passes data back through the RETURN clause.

Second, regarding the issue of whether and when the value of a variable passed as an argument is changed: the question asked "can be assigned" [my emphasis]. The word "can" means that, and I believe is fairly commonly interpreted as, "it is possible". It does not mean that every time you pass a variable as an argument it will be changed. It does not ask you to specify the mechanism by which the change takes place.

As I read over this post, I realize that it is likely that a number of players objected because they analyzed the correctness of the choice "Change the value of a variable by passing it as an argument to a procedure or function." independently of the question. If I asked you to answer true or false to "The value of a variable is changed by passing it as an argument to a procedure." I could see why you might say "It depends."

Remember, though, that you must evaluate each choice as an answer to the question, not as an independent statement. When this is done, the "can" of the question establishes that you must only determine if such a thing can (is possible) happen.


  1. Sorry, but I still don't have a "It depends". The fact is that the passing "can" not change the variable in any case. It has to be done in the procedure or function.

    Please consider also that a lot of us are not native speekers!

    Br Martin

  2. Hello again,

    I have discussed the answer with my collegues and passing as and argument "can" actually change the value of an variable.

    l_var VARCHAR2(10) := 'Filled';

    PROCEDURE only_out(argument_out OUT VARCHAR2) IS
    END only_out;
    dbms_output.put_line(coalesce(l_var,'Not Filled'));
    dbms_output.put_line(coalesce(l_var,'Not Filled'));

    So bad for me the choice is correct.

    Br Martin

  3. > you can define OUT and IN OUT parameter

    Yes I can, but:

    If I pass my variable to an IN OUT parameter it will not be changed as long as there is no assignment within the procedure and this will always be one of the other (undisputed) choices.

    An OUT parameter, well, thats tricky. My variable will be set to NULL if I don't assign a value within the procedure. This might be a valid opinion - might be! We come near to hair splitting if we go this way.


  4. I think the question was worded clearly enough. Even for non-native English-speakers, "can" is one of the most basic words in the language. If you can not understand it, you can not program in English because you WILL NOT understand the meaning of anything you read.

    - Stew

  5. Thanks for your thoughts Stew. Sometimes
    non-native English speekers miss some nuances, but at least they speek more than one language.

    What I didn't like is to reduce the correctness to a semantic discussion instead of showing that assigning a variable to an OUT parameter will change the value.

    Br Martin