Recompile dependent PL/SQL code after changes

Reminder: If you edit and recompile PL/SQL stored an Oracle database, you need to keep "dependencies" with other code in mind. You may need to ensure that other stored code relying upon your code is also recompiled.

Partner with a DBA if you do not have adequate permissions.

A colleague of mine recommends the following procedure:

1: Query baseline invalid objects

Have a DBA run the following SQL for a baseline of "invalid objects" living in the database:

select owner, object_type, object_name, namespace, object_id
from dba_objects
where status != 'VALID'
order by owner, object_type;

Ideally, there wouldn't be any.

But according to my colleague, sometimes there's "cruft" in large databases.

---

2: Edit and recompile your code

Edit and recompile your code with the DDL of your choice.

---

3: Requery invalid objects for new records

Have a DBA run the following SQL query again and let you know if there's anything new:

select owner, object_type, object_name, namespace, object_id
from dba_objects
where status != 'VALID'
order by owner, object_type;

Objects newly appearing in the query results need to be re-compiled.

These are likely ones that were dependent upon the code you edited.

Recompiling with the Toad GUI

To mass-recompile code with Toad, a DBA could:

1. Go to the Schema Browser
2. Put it into "Treeview" mode
3. Expand "Other Schemas"
4. Find a user whose code needs recompilation
5. Expand that user's node
6. Expand "Invalid Objects"
7. Right-click on "Invalid Objects"
8. Select "Compile all Invalid PL/SQL Objects."

Recompiling with PL/SQL

It looks like another approach might be for the DBA to execute something along the lines of the following anonymous PL/SQL script:

DECLARE
    CURSOR c1 IS
        select owner, object_type, object_name, namespace, object_id
        from dba_objects
        where status != 'VALID'
        and owner||'.'||object_name not in (
            'CRUFT_OWNER_1.CRUFT_OBJECT_A', 'CRUFT_OWNER_1.CRUFT_OBJECT_B'
            , 'CRUFT_OWNER_2.CRUFT_OBJECT_C', 'CRUFT_OWNER_2.CRUFT_OBJECT_D', 'CRUFT_OWNER_2.CRUFT_OBJECT_E'
            )
        order by owner, object_type;
BEGIN
    DBMS_OUTPUT.put_line ('Recompile started at ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
    FOR c1_rec IN c1 LOOP
            dbms_output.put_line(
                'Attempting to recompile object ID ' || c1_rec.object_id 
                || ', owner ' || c1_rec.owner
                || ', object_type ' || c1_rec.object_type
                || ', object_name ' || c1_rec.object_name
                || ', namespace ' || c1_rec.namespace
            );
            dbms_utility.validate(c1_rec.object_id);
            dbms_output.put_line(
                'Finished attempt to recompile object ID ' || c1_rec.object_id 
                || ', owner ' || c1_rec.owner
                || ', object_type ' || c1_rec.object_type
                || ', object_name ' || c1_rec.object_name
                || ', namespace ' || c1_rec.namespace
            );
    END LOOP;
    DBMS_OUTPUT.put_line ('Recompile cleanup ended at ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
END;

---

4: Query invalid objects to ensure back at baseline

The DBA should then run the following SQL query again to ensure that it only returns the same "known cruft" records as before you made your change:

select owner, object_type, object_name, namespace, object_id
from dba_objects
where status != 'VALID'
order by owner, object_type;