[Resolved] ORA-06550 PL SQL Error in Oracle

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.

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

image 3 ORA-06550 PL SQL Error in Oracle 3

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.

FAQs on ORA-06550

When does ORA 06550 occur?

It occurs when a database object is invalidated or compiled with errors. i.e a PL/SQL compilation error was ignored.

How do you define ORA-06550 Error

ORA-06550 error is the location in the PL SQL code where the error is present.

Is fixing ORA-06550 easy?

Yes , Once you identify the reason at the Error location thrown you can fix easily.

More on EBS Apps Tech

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Cloud Technologies Blog
Logo
Enable registration in settings - general
Compare items
  • Total (0)
Compare
0
Shopping cart