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.

25 November 2010

Questions raised about 24 November quiz and program invalidations (1709)

The 24 November quiz asked you to "describe a benefit of using the function implementation [to 'hide' a literal value] rather than declaring a constant in the package specification."
We scored the following statement as correct:
"The function implementation reduces the likelihood that program units will be invalidated thus requiring fewer recompilations of the application code base."
A few players wrote with the following concerns:
1. "I think the correctness of option 2 (The function implementation reduces the likelihood that program units will be invalidated thus requiring fewer recompilations of the application code base.) for Nov 24th, 2010 quiz is arguable. The business rules are changing in time so often, and those changes may result in (in our case): i. salary change ii. abortion of the minimum salary policy that may force us to erase both the constant and the function. iii. salary data type change iv. minimum salary calculation change, which may cause alterations on the interface of minum_salary function etc. Implying that "the salary change amongst the statements above is much more likely to happen" is not true. According to the billions of conditions of the business, one of the other options may happen more frequently for a particular period and those options may lead us altering the package specification. Thank you."
2. "Do you think that the answer "..the function reduce invalidation..." for the Quiz is 100% true and doesn't have any side effect? I understand that recompilation of only package body is better for other units, but for the existing session is this recompilation bad too. Session lost the package body context (lost value of body variables), isn't it?"
3. "I assume that today quiz's answer " .. function implementation reduces likelihood ..." meant "... when changing the value of the constant". However I missed it in the text. It seemed a rather vague answer :("
Point #1 makes some very interesting observations about the dynamic nature of our applications, but I don't think this perspective changes the fact that by hiding the literal value in the package body, you can avoid invalidations and therefore recompilations when the value changes.
Point #2: sure, when you recompile the package body, this can have some side effects (most notably the dreaded ORA-04608 error that occurs when package "state" is altered), but I again I don't think this point argues against the correctness of the choice.
Point #3: yes, adding the phrase "when the literal value changes" would make the choice more explicit (or at least give you more of a hint regarding whether or not it is correct), but as a general statement, I still feel it is valid as is.
SF

10 comments:

I agree, the function implementation means that changes to the constant will never invalidate any other code in the system, since only the body needs to be recompiled.

To avoid the possibility of the ORA-04608 error (which is due to the value of the constant being kept in the session PGA, the first time the session calls any routine in that package), it could be written like this:

For "Point #1 makes some very interesting observations about the dynamic nature of our applications, but I don't think this perspective changes the fact that by hiding the literal value in the package body, you can avoid invalidations and therefore recompilations when the value changes."

We are not discussing the fact that we can avoid invalidations by encapsulating the appropriate structures in the package bodies.

We are discussing the corectness of the statement in the option:"The function implementation reduces the likelihood that program units will be invalidated thus requiring fewer recompilations of the application code base."

This statement is not mentioning about the changes on minimum salary. Therefore, it is not correct or complete. If the statement was like below:

"The function implementation reduces the likelihood that program units will be invalidated thus requiring fewer recompilations, which caused by changes on salary, of the application code base."

It would be correct and complete.

As the original statment is not including any information about salary changes, it is not possible to know that the minimum salary is very likely to change. Without that rigid information about salary changes, the likelihood of the salary change cannot be an issue beyond an anticipation, which does not make the original sentence true.

The statement of the quiz establishes the minimum salary rule as the context for each of the given choices; repeating this in the choices would be redundant. This context, however, is not necessary for the "invalidation" choice because of the following:

The probability that any rule change will invalidate program units is directly related to the number of rule changes that cause program units to be invalidated divided by the total number of possible rule changes. Structuring one’s code such that a particular rule change will not result in invalidating other program units does not change the total number of possible rule changes, thus the total probability that any rule change will invalidate program units is reduced.

I thought the style of question may illicit a few responses, but I thought the answers were pretty clear cut. It's a common scenario that pl/sql developers should be aware of and consider when designing package structure. My 5 cents.

Don't rescore. But the quiz is difficult. It is about opinions, expectations and experience of the reader. The moment I was reading the quiz I was already thinking about the options. And in general you are correct.

Off cause Steven and Jhall62 are right. Hiding constants in a package body makes it less likely that other packages are invalidated in case the constants are to changed.

Having said that, I think it is a very bad practice to use constants in procedures or functions or packages (headers or bodies) to define values that are likely to change. I would never do that.

Constants should only be used for values that are never changed, such as status codes from UTL_HTTP or error codes from UTL_FILE.

Values that are likely to change such as a minimum salary belong in a database table.

Thus in my opinion the quiz answer makes us realize that the second worst practice (to hide constants defining values that are likely to change in a package body) is better than the worst practice (to expose constants defining values that are likely to change in a package header).

There is a very little bit of ambiguity: "invalidate" - status or state? But keyword "recompilation" does not left any other choice: only status.I 'd also fall into this little trap. But no rescoring is required.

From practical point of view recompilation is not a trouble because it happens implicitly. The real trouble for 24*7*365 is invalidated state. So conceptuality of this choice is zero. It does not teach. It does not show good practice.

I know that this conversation has lasted too much and this is my last comment on this issue.

When I scan the comments, the one which belongs to jhall62 is the most relevant one and I think exactly the same as jhall62.

However, my problem is about the likelihood measure.

I am saying that there can be many thing which may force us to alter any particular point in our software. And salary change is just one of those things and it is not so true to imply that salary change is more likely to occur.

I am giving 3 examples:

Case 1: Minimum salary changes

Effect A: If we are providing the salary value by using a constant located in the package spec, this causes invalidations to occur on programs referencing our package.

Effect B: If we are returning the salary value from our function there will be no invalidations on the referencing programs.

Effect: No matter we are using a spec constant or a function to supply the min salary value, referencing programs get invalidated.

Case 3: Minimum salary policy changes: If the seniority of the employee is more than 10 years then the salary is 20K, else 10K.

Effect A: In the case of using spec constant, we can create 2 constants for senior and junior employees and this change invalidates the refencing programs.

Effect B: If we are using the function to return the minimum salary, we have to alter the function's interface by adding a numberic in parameter that carries the employee's seniority level. This change makes referencing programs invalidate.

Now let us evaluate the statement of the option that was said to be a correct one:

"The function implementation reduces the likelihood that program units will be invalidated thus requiring fewer recompilations of the application code base."

For case 1, this is true.For case 2, this is not true.For case 3, this is not true.

Now, how can we say that using the function for providing the minimum salary reduces the likelihood that program units will be invalidated thus requiring fewer recompilations?

I think we cannot say that becuse in case 2 and case 3, whether or not we are using a function to provide the minimum salary, the referencing programs get invalidated.

The original statement is logically equivalent to the one below:

Case 1 is more likely to happen among the other cases.

And I think this is not true, either.

That is all I am talking about. And of course I know the advantages of information hiding, high cohesion, low coupling in the software systems, but this is not the point in this quiz.

Bora, the original statement is not the same as saying that the likelihood of the minimum salary changing is greater than the likelihood of any other change occurring. This can be demonstrated using the three cases you mentioned. For the sake of simplicity assume that these are the only changes possible. If the associated PL/SQL code is written such that any of these changes results in other objects being invalidated, then the probability that a change will invalidate other objects is 100% regardless of the probability of each change even if these probabilities differ.

If the code associated with case one is implemented such that changing its value does not invalidate other objects then the probability that a change will invalidate other objects is reduced. If all three changes are equally likely then there is a ~67% chance that a change will invalidate other objects since two of the three will invalidate other objects. If the probability of the first case is half that of each of the other two cases then there is an 80% chance that a change will invalidate other objects. If the probability of the first case is twice that of each of the other two cases then there is a 50% chance that a change will invalidate other objects.