Oracle: How to recompile and view package source code

As SYSDBA you can recompile all packages from sqlplus with the following oracle utility

@$ORACLE_HOME/rdbms/admin/utlrp.sql

To view any "invalid" packages after compilation you can use the following SQL which will give u a summary overview of all object_types

set pages 49000
select object_type, status, count(*)
from user_objectsgroup by object_type, status;

If you have invalid package or package body objects it will basically mean you've got compilation errors in your pl/sql code.  To view the invalid objects in more detail use following SQL

COLUMN object_name FORMAT A30
SELECT
  object_type,
  object_name,
  status
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type, object_name;

When you've identified the package/body that is invalid you can manually recompile it with
ALTER PACKAGE <obj_name_from_prev_query> COMPILE BODY;

if its a package obj that is invalid then just omit the "body" part at end of the statement 

More comprehensive info on recompiling can be found here.

If the package/function etc still won't compile then a show errors cmd just after manually trying to recompile will show you why compilation failed.  "show errors" will give you the syntax error and the line & column reference on which it ocurred.

If you then want to see the source code for the package etc you'll have to query the USER_SOURCE table.

set pages 49000
select text
from user_source
where name='<invalid_obj_name_from_prev_query>'
and type='PACKAGE BODY'
order by line

Change type to suit, generally it will be either FUNCTION, PACKAGE, PACKAGE BODY, PROCEDURE or TRIGGER

You can then compare the source for the package/function etc with the lines for the earlier show errors cmd.

Now the hard work of debugging the pl/sql begins....  :-)