Passionate about data

Data and its implications on software design and development.

Oracle Metadata Can Be Mis-leading

Oracle has metadata about all its objects in various tables/views. One such view is the USER_OBJECTS or ALL_OBJECTS, this view has a column named as STATUS which shows you if the given object is VALID or INVALID. The status applies to DB Code (Stored Procedures, Functions, Triggers etc).

To find all the INVALID objects in the schema, issue

1
SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID';

One problem with the way oracle maintains this metadata is, changing the underlying table on which the DB Code depends, oracle marks the objects are INVALID even though the underlying table may have changed in such a way, that it does not affect the DB Code at all (like adding a new column, or making a colum nullable). Here is some code which shows you what I mean. Run it through SQLPlus.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
COLUMN OBJECT_NAME FORMAT A30
COLUMN STATUS FORMAT A15
spool objects.log

CREATE TABLE FOO (ID NUMBER(10), NAME VARCHAR2(30));

CREATE OR REPLACE TRIGGER TRIG_FOO
BEFORE INSERT OR UPDATE
ON FOO
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
	IF :NEW.name IS NULL THEN
		:NEW.name := 'NOT AVAILABLE';
	END IF;
END;
/

CREATE OR REPLACE FUNCTION FUNCTION_GET_NAME_FOR_FOOID(inFooId number)
RETURN VARCHAR2
IS
fooName VARCHAR2(30);
BEGIN
	BEGIN
     SELECT name INTO fooName FROM foo WHERE id = inFooId ;
     EXCEPTION
         WHEN NO_DATA_FOUND THEN
     		RETURN 'NOT FOUND';
 	END;
     RETURN fooName;
 END;
/

SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID';

ALTER TABLE FOO ADD ( DESCRIPTION VARCHAR2(100));

SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID';

spool off

To get the objects back to VALID status, all that needs to be done is

1
2
ALTER TRIGGER TRIG_FOO COMPILE;
ALTER FUNCTION FUNCTION_GET_NAME_FOR_FOOID COMPILE;
1
SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID';