Oracle PL/SQL - PL SQL Introduction Compilation errors

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Introduction
  4. Basics of PL/SQL Programs

Introduction

The following code shows what happens when you mistype something when creating stored procedures.

The code is forgetting to define the datatype for an input parameter.

Oracle creates the function with compilation errors in database.

Even though the function is stored in the database, you can't use it.

You can use SHOW ERRORS command to check the error message. 

create or replace 
function f_getArea_Nr (i_rad_nr)                                  
   return NUMBER 
is 
    v_pi_nr NUMBER:=3.14; 
begin 
   return v_pi_nr * (i_rad_nr ** 2); 
end; 
/ 
Warning: Function created with compilation errors.                     

SQL> show errors

Status

If a stored procedure was created with compilation errors, it has an INVALID status.

You can check all stored procedures using the Oracle data dictionary view USER_OBJECTS: 

SQL> select object_type, object_name, status 
2  from user_objects 
3  where object_type in ('FUNCTION','PROCEDURE', 
4 'PACKAGE','PACKAGE BODY','TRIGGER') 
5  order by object_type,object_name 
6  / 

OBJECT_TYPE         OBJECT_NAME          STATUS 
------------------- -------------------- ------- 
FUNCTION            F_GETAREA_NR         INVALID 
PROCEDURE           P_PRINT              VALID 
...

Related Topic