Invoker Rights and Function Result Cache - Oh, Sloppy Compiler! (2322)

In the 17 May quiz, we asked:

Which of the following statements describe what happens when you combine invoker rights (AUTHID CURRENT_USER) with the function result cache?

and the only choice we scored as correct was:

If you include both AUTHID CURRENT_USER and RESULT_CACHE in the header of your function, Oracle will raise a compilation error.

Specifically, the following choice was scored as incorrect:

You can include both AUTHID CURRENT_USER and RESULT_CACHE in the header of your function, but the CURRENT_USER setting will be ignored.

It didn't seem like this would be a very problematic quiz, since Oracle does in fact have an error defined for just this scenario:

PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules 

And we verified this with a package as follows:

CREATE OR REPLACE PACKAGE plch_pkg
   AUTHID CURRENT_USER 
IS
   FUNCTION object_name (object_type_in IN all_objects.object_type%TYPE)
      RETURN all_objects.object_name%TYPE
      RESULT_CACHE;
END;
/

That's pretty clear, right? Ha!

Turns out that while all of the above is unambiguously correct for packages, when it comes to functions, the PL/SQL compiler is downright funky - and it messed up our quiz! Check this out (many thanks to Iudith for the code example and detailed analysis):

SQL> CREATE OR REPLACE FUNCTION F2 (p_id in number)
  2    RETURN VARCHAR2
  3    RESULT_CACHE
  4    AUTHID CURRENT_USER
  5  AS
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('*** INSIDE FUNCTION ***');
  8    RETURN 'ABC';
  9  END;
 10  /

Function created.

SQL>
SQL> CREATE OR REPLACE FUNCTION F1 (p_id in number)
  2    RETURN VARCHAR2
  3    AUTHID CURRENT_USER
  4    RESULT_CACHE
  5  AS
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('*** INSIDE FUNCTION ***');
  8    RETURN 'ABC';
  9  END;
 10  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION F1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/10     PLS-00999: implementation restriction (may be temporary)
         RESULT_CACHE is disallowed on subprograms in Invoker-Rights
         modules

If when you define your function you include the RESULT_CACHE keyword after the AUTHID CURRENT_USER clause, you will see the above error. If, however, you reverse the order of those clauses, the function compiles - but the results are not cached.

Now that right there is funky stuff.

We will take the following steps for this quiz:

1. Everyone gets credit for the two choices listed above (bad luck that this affected two of the choices).Your answers will be changed to reflect this.

2. We will change the text of the question so that it explicitly asks about using both invoker rights and result cache in a package. That way this ambiguity will be avoided.

3. We'll notify the PL/SQL product manager about this glitchy behavior, just in case they are not aware.

I look forward to your comments.

Steven

Using double quotes in trigger event functions (1424)

In the 16 May quiz, we tested your knowledge of ways to restrict firing of a trigger based on the column being updated. The following choice was scored as correct:

CREATE OR REPLACE TRIGGER plch_employees_trg
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('salary')
   THEN
      sys.DBMS_OUTPUT.put_line ('Updated');
   END IF;
END;
/

And this explanation was provided: "This choice relies on the UPDATING function to determine if an update is taking place on the salary column, by passing the name of the column to the function. Since I do not enclose the name in double quotes, Oracle will automatically upper-case the name of the column and so will correctly detect that in the case of the first update on last_name, no output will be displayed. As a result, "Updated" is displayed just once."

Two players wrote to note that "11gR2 does not interpret double quotes as enclosing character for the updating function, and automatically upper-case column names even if it has been defined in the table with lower case names."

I put together the following script to examine this more closely:

DROP TABLE plch_employees
/

CREATE TABLE plch_employees
(
   employee_id   INTEGER
 , last_name     VARCHAR2 (100)
 , salary        NUMBER NOT NULL
 , "comment"     VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100
              , 'Jobs'
              , 1000000
              , NULL);

   INSERT INTO plch_employees
        VALUES (200
              , 'Ellison'
              , 1000000
              , NULL);

   COMMIT;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg1
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('comment')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired comment.');
   ELSE
      sys.DBMS_OUTPUT.put_line ('comment will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg2
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('COMMENT')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired COMMENT.');
   ELSE
      sys.DBMS_OUTPUT.put_line ('COMMENT will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg3
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('"comment"')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired "comment".');
   ELSE
      sys.DBMS_OUTPUT.put_line ('"comment" will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg4
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('"COMMENT"')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired "COMMENT".');
   ELSE
      sys.DBMS_OUTPUT.put_line ('"COMMENT" will not fire.');
   END IF;
END;
/

BEGIN
   sys.DBMS_OUTPUT.put_line ('Update salary column');

   UPDATE plch_employees
      SET salary = 2 * salary
    WHERE employee_id = 200;

   sys.DBMS_OUTPUT.put_line ('Update comment column');

   UPDATE plch_employees
      SET "comment" = 'This is comment for emp no 200'
    WHERE employee_id = 200;

   COMMIT;
END;
/

And the output I see is:

Update salary column
"COMMENT" will not fire.
"comment" will not fire.
COMMENT will not fire.
comment will not fire.
Update comment column
"COMMENT" will not fire.
"comment" will not fire.
Fired COMMENT.
Fired comment.

Very curious. Any thoughts on this?