How to resolve ORA-06550 Error Message in Oracle database

Learn the cause and how to unravel the ORA-06550 error message in Oracle.

Description

When you come across an ORA-06550 error, the following error message will appear:

ORA-06550: line num, column num: str

Cause

You tried to execute an invalid block of PLSQL code (like a saved method or function), but a compilation error occurred.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Refer to the line and column numbers (in the error message) to find the compilation error and correct it. Then strive recompiling your code.

Let’s seem to be at an example of how to get to the bottom of an ORA-06550 error. For example, if you created a procedure known as TestProc as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS
  3    vnum number;
  4  BEGIN
  5    vnum := vAnotherNum;
  6  END;
  7  /

Warning: Procedure created with compilation errors.

This technique was once created with compilation errors. So if we try to execute this procedure, we will get an ORA-06550 error as follows:

SQL> execute TestProc();
BEGIN TestProc(); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object EXAMPLE.TESTPROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

You can run the SHOW ERROR command to view the errors as follows:

SQL> show error procedure TestProc;
Errors for PROCEDURE TESTPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1  PL/SQL: Statement ignored
5/9  PLS-00201: identifier 'VANOTHERNUM' must be declared

As you can see, the error is prompted by means of the variable known as VANOTHERNUM not being declared. To unravel this error, we can modify our TestProc method to declare the variable as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS
  3    vnum number;
  4    vAnotherNumber number;
  5  BEGIN
  6    vAnotherNum := 999;
  7    vnum := vAnotherNum;
  8  END;
  9  /

Procedure created.

And now when we execute our TestProc procedure, the ORA-06550 error has been resolved.

SQL> execute TestProc();

PL/SQL procedure successfully completed.