Challenge discussion leads to ERs for new warnings!

I am very pleased to announce that a discussion on the PL/SQL Challenge blog regarding the 26 November 2010 quiz has led to the submission by Bryn Llewellyn, PL/SQL Product Manager, of two enhancement requests to add new compile-time warnings! See details below.

My hope, plan and expectation is that as our community grows, as more and more developers participate, we will have an ever-greater impact on the evolution of the Oracle PL/SQL language.

Great work, everyone!

Cheers, Steven

Background and note from Bryn Llewellyn

I asked this question of Bryn (drawn from the quiz): "Suppose my procedure looks like this:

CREATE OR REPLACE PROCEDURE twoargs 
   (arg1 OUT varchar2, arg2 OUT varchar2) IS
BEGIN
   arg1 := 1;
   arg2 := 2;
END;
/

I then call the procedure as follows:

DECLARE
   x VARCHAR2 (100);
BEGIN
   EXECUTE IMMEDIATE 'BEGIN twoargs( :v1, :v2 ); END;'
     USING OUT x, OUT x;
   /* Note original post showed OUT x, OUT l_value. I have fixed it! */
END;
/

Is the value assigned to x indeterminate or does that "indeterminacy" only come into play when using NOCOPY?"

Bryn answered as follows:

In your example, you used the same actual for two different out formals. In other words, the same name for two different phenomena: outputs with different meanings and in general different values. That's why it's proper to call it an example of aliasing.

Here, the indeterminacy arises because the PL/SQL language definition makes no promise about the order in which out formals are written back to their actuals. (At least, if we published such a definition, that statement would be true.) Any particular program, running in any particular version of Oracle Database, will doubtless always do this writing back in the same order. But that might change on a Database version boundary. It might also change according to some code change you made that would seem inessential (going from two out formals to three, changing the datatypes of the formals, changing to a specific scalar collection element rather than an ordinary scalar variable for one the actuals -- who knows). So there's no experiment you can do to show the indeterminacy in question here. Just take our word for it.

Your use of execute immediate slightly disguised things. But the "using out" construct is just another twist on the formals/actuals picture. So, for that matter is this:

select Sum(Sal), Avg(Sal)
  into a, a
  from Scott.Emp;

Your question did cause some lively discussion here -- which broadened its scope a bit. As a result, I filed these two enhancement requests:

10636541 - Provide new PL/SQL warning when same variable used for >1 actual
10636525 - Provide new PL/SQL warning when a package spec exposes a variable

They both have status "Published = Y" so you should be able to read them via My Oracle Support -- and tell your quizzers (or is that quizzees) about them.