Wrong Scoring for Stateful Package Quiz? (5193)

The 11 August quiz explored the concept of "state" in a package and asked you to identify ways to avoid the raising of the ORA-04068 error.

Two players wrote with objections to how the answers were scored. Here are their comments:

I guess answer 7626 was supposed to be correct, but the offered solution will not work. The new package body should reference the variable declared in plch_pkg_constant: CREATE OR REPLACE PACKAGE BODY plch_pkg IS FUNCTION language_vendor RETURN VARCHAR2 IS BEGIN RETURN plch_pkg_constant.g_language; -- <== END language_vendor; END plch_pkg; So "Move the declaration of the variable to another package" is correct, but "as shown below" is not. 

My reading of the choices is that none of them will give you the response of the "newly changed variable", however my feeling that the "Move the variable to the package" is the correct choice because, I am thinking, that that will allow you to repeatedly call the function even though the response will always be "Oracle Corporation" and not what you set the variable to. 

My response: I can see why these players were caught up on this issue, but I believe it results from a mis-reading of the quiz question, which ends with:

"Which of the choices describe a way to change the code in plch_pkg so that a user connected to the SCOTT schema can call the hr.plch_pkg.language_vendor function repeatedly, even if the default value of the g_language variable is changed in the HR schema?"

Notice that I say "call the hr.plch_pkg.language_vendor function repeatedly". The g_language variable (which gives "state" to the package, leading to the ORA-04068 error) is not used by the language_vendor function. Changing the value of this variable does not affect this function in any way. So I don't think this statement is true: "The new package body should reference the variable declared in plch_pkg_constant."

Of course, in the "real world", it is unlikely that you can move a variable from one package to another without having a ripple effect through other subprograms and program units. But I don't think that is an issue here.

Your thoughts?

FORALL, UPDATE and Reality-Doc Conflicts (6431)

The 9 August quiz tested your knowledge of the restriction (prior to Oracle 11g) that you cannot reference fields of collections of records in a FORALL statement. The FORALL statement we showed in the question is this:

FORALL i IN 1 .. l_tab.COUNT
      UPDATE plch_employees
         SET salary = l_tab (i).salary * 1.1
       WHERE employee_id = l_tab (i).employee_id;

Notice that the same collection (l_tab) is referenced in both the SET and WHERE clauses.

A player who pays close attention to the Oracle documentation wrote as follows:

For both 11.1 and 11.2, the "PL/SQL Language Reference" documentations (as PDF, for 11.1 page 13-59, for 11.2 page 13-79) states the following: "If dml_statement is an UPDATE statement, its SET and WHERE clauses cannot reference the same collection."

Well, I must admit, this was news to me. I'd never noticed that restriction and, it turns out, for a fairly good reason: neither the PL/SQL compiler nor run-time engine complains about this scenario at all.

So is the documentation correct or is the functionality of PL/SQL correct? I asked Bryn Llewellyn, PL/SQL Product Manager. Here is his reply:

"I agree that it [my demonstration code] does seem to be at odds with the claim from the docs that you quote. 'll have to ask about this and get back to you."

Bryn also provided a script (see below) that he is using to reproduce the issue.

Needless to say, I do not plan to change the scoring of the question!

CLEAR SCREEN
CONNECT Sys/Sys AS SYSDBA
declare
  procedure Create_User(Who in varchar2) is
    User_Does_Not_Exist exception;
    pragma Exception_Init(User_Does_Not_Exist, -01918);
  begin
    begin
      execute immediate 'drop user Usr cascade';
    exception when User_Does_Not_Exist then null; end;
    execute immediate '
      grant
        Unlimited Tablespace,
        Create Session,
        Create Table,
        Create Trigger
      to Usr identified by p';
  end Create_User; 
begin
  Create_User('Usr');
end;
/
CONNECT Usr/p
create table t(ID integer primary key, c1 varchar2(10), c2 number)
/
begin
  insert into t values(1, 'a', 10);
  insert into t values(2, 'b', 10);
  insert into t values(3, 'c', 30);
  commit;
end;
/
select ID, c1, c2 from t
/
declare
  type x is table of number;
  v x;
begin
  select c2
  bulk collect into v
  from t
  for update;

  forall i in 1..v.Count()
  update t
  set c2 = v(i)*2
  where c2 = v(i);
end;
/
select ID, c1, c2 from t
/