Recompile dependent PL/SQL code after changes
27 Apr 2020
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:
- Go to the Schema Browser
- Put it into “Treeview” mode
- Expand “Other Schemas”
- Find a user whose code needs recompilation
- Expand that user’s node
- Expand “Invalid Objects”
- Right-click on “Invalid Objects”
- 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;
