[Resolved] ORA-06550 PL SQL Error in Oracle

866 Views

SaveSavedRemoved 0

ORA-06550 PL SQL Error in Oracle: While working on Oracle technologies, especially PL SQL or any tool or programming language interacting with the Oracle database, you might encounter ORA 06550 error.

What is this and how do we resolve it, We will look at it in this post.

Table of Contents

A Successful call to an existing database procedure

Examine the code in the screen shot

image ORA-06550 PL SQL Error in Oracle 1

set serveroutput on
 CREATE OR REPLACE PROCEDURE Ora06500test
    AS
      x number;
    BEGIN
      x := 10;
      dbms_output.put_line ('value of x '|| x);
    END;
 /
 

 Begin
 Ora06500test;
 end;
/

The above procedure is a simple procedure compiled successfully and when executed using an anonymous block it returns the value of x.

Doing a failed call due to ORA-06550 Error

Now let’s purposely invalidate this procedure by doing a syntax error

CREATE OR REPLACE PROCEDURE Ora06500test
    AS
      x number;
    BEGIN
      x := 10;
      dbms_output.putline ('value of x '|| x);
    END;
 /

Notice I removed the ( _ ) from put_line and made it as putline.

image 2 ORA-06550 PL SQL Error in Oracle 2 — *I compiled and saved the procedure ignoring the errors. and invalidating the object*

ORA-06550 Error shows the line number and column number

Now If I execute the anonymous block it returns

Notice the ORA-06550. The error throws up along with the line number and column number.

Forms of ORA-06550 Error

Sometimes we get

PLS-00302: component ‘XYZ’ must be declared ORA-06550 : Means XYZ object is not present in the database or is a keyword that oracle is not able to differentiate.

PLS-00201 : Probable the variable was not declared.

PLS-00905: object EMPtable is invalid ORA-06550: line x, column x: , PL/SQL Statement ignored : check if the EMPtable is present and valid.
java.sql.SQLException: ORA-06550: after calling procedure from java code : The procedure may be invalidated.

Summary of ORA-06550 Error

So ORA-06550 is thrown when a procedure is invalidated due to some dependencies or changes. We need to fix it by seeing what is the error in the line number and compile the object again

ORA 06550 just points to the location in your PL SQL code but that line of code can have different kinds of errors, like Syntax issue, Pointing to an invalidated object, using a keyword, etc. So basically we need to figure out what the error reason is and then we have to apply the fix to make our code run.