The quiz demonstrates that if I add a column to a query that is executed in a stored program unit, then that the program unit is not invalidated, since that unit couldn't possibly have depended on the new, previously non-existent, column.
One player, Oleksandr, wrote with the following oddity: if your query includes reference to ROWNUM, then Oracle will invalidate the program unit after adding a column. You can see this fact below:
SQL: CREATE TABLE mumu 2 ( 3 x NUMBER 4 , c VARCHAR2 (5 BYTE) 5 , z NUMBER 6 ); Table created. SQL: INSERT INTO mumu (x, c) 2 VALUES (2, 'x'); 1 row created. SQL: INSERT INTO mumu (x, c) 2 VALUES (3, 'c'); 1 row created. SQL: COMMIT; Commit complete. SQL: CREATE OR REPLACE PROCEDURE mu 2 AS 3 xx mumu.c%TYPE; 4 BEGIN 5 SELECT c 6 INTO xx 7 FROM mumu 8 WHERE ROWNUM = 1; 9 END; 10 / Procedure created. SQL: SELECT status 2 FROM user_objects 3 WHERE object_name = 'MU'; STATUS ------- VALID SQL: ALTER TABLE mumu ADD y NUMBER; Table altered. SQL: SELECT status 2 FROM user_objects 3 WHERE object_name = 'MU'; STATUS ------- INVALIDIf I remove the reference to ROWNUM (change to "z = 1" for example), then the program unit is not invalidated.
I do not know why this happening. Does anyone "out there" have any ideas or any other comments about this quiz?