Recompiling Invalid Code

This is an example of code stored in an invalid state, and how to recompile. The first query shown below was run (and hence stored in User_Source), but compiled with errors.

Create or Replace Function fHired (vHireDate Date)
Return Number is
 Begin
   Return Round((Sysdate-vHireDate)/365)
 End;
/
Warning: Function created with compilation errors.

A second piece of code is written that references the first, which is currently invalid. Obviously, even though there are no errors here, this won't compile either.

Create or Replace Procedure Hired is
Begin
 For i in (Select EName
                , fHired(HireDate) as Yrs
             From Emp )
 Loop
   DBMS_Output.Put_Line(i.Ename||' '||i.Yrs);
 End Loop;
End;
/
Warning: Procedure created with compilation errors.

You can display any errors with the "Show Errors [type name]" command. If you don't supply the type, and name, the last compiled code will display its errors.

SQL> Show Errors
LINE/COL ERROR
-------- ----------------------------------------------
3/28     PL/SQL: ORA-06575: Package or function FHIRED
         is in an invalid state

SQL> Show Errors Function fHired
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PLS-00103: Encountered the symbol "END" when
         expecting one of the following: ...

Since both have errors, they are marked as 'Invalid.'

Select Object_Type
     , Object_Name
     , Status
  From User_Objects
 Where Object_Name in ('FHIRED','HIRED')
 Order By Object_Type
        , Object_Name;

Object       Object
Type         Name    Status
------------ ------- ------------
FUNCTION     FHIRED  INVALID
PROCEDURE    HIRED   INVALID

Next we go back and fix the first piece of code by adding the missing semicolon. When it's run from the prompt, it will show that it got created.

Create or Replace Function fHired (vHireDate Date)
Return Number is
 Begin
     Return Round((Sysdate-vHireDate)/365);
 End;
/
Function created.

The second piece of code then can be compiled by issuing the following statement:

SQL> Alter Procedure Hired Compile;
Procedure altered.

This shows the function and procedure as they are stored in the User_Source table.

Select *
 From User_Source
Where Name in ('FHIRED','HIRED')
Order by Name
       , Line;

NAME   TYPE      LINE TEXT
------ --------- ---- -------------------------------------------------------
FHIRED FUNCTION     1 Function fHired (vHireDate Date)
FHIRED FUNCTION     2 Return Number is
FHIRED FUNCTION     3     Begin
FHIRED FUNCTION     4         Return Round((Sysdate-vHireDate)/365);
FHIRED FUNCTION     5     End;

NAME   TYPE      LINE TEXT
------ --------- ---- -------------------------------------------------------
HIRED  PROCEDURE    1 Procedure Hired is
HIRED  PROCEDURE    2 Begin
HIRED  PROCEDURE    3   For i in (Select EName
HIRED  PROCEDURE    4                  , fHired(HireDate) as Yrs
HIRED  PROCEDURE    5               From Emp )
HIRED  PROCEDURE    6   Loop
HIRED  PROCEDURE    7     DBMS_Output.Put_Line(i.Ename||' '||i.Years);
HIRED  PROCEDURE    8   End Loop;
HIRED  PROCEDURE    9 End;