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
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.
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
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.