Blog

ORA-06550: line string, column string: string

Cause: Usually a PL/SQL compilation error.

Action: none

ORA-06550 is a very simple exception, and occurs when we try to execute a invalid pl/sql block like stored procedure. ORA-06550 is basically a PL/SQL compilation error. Lets check the following example to generate ORA-06550:

SQL> create or replace procedure myproc
  2  as
  3  begin
  4     for c in (select * from scott.emp)
  5     loop
  6             dbms_output.put_line(c.empno || ' ' || c.ename || ' ' || sal);
  7     end loop;
  8  end;
  9  /
Warning: Procedure created with compilation errors.

SQL> exec myproc
BEGIN myproc; END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object MYUSER.MYPROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Here we create a stored procedure "myproc" which has some compilation errors and when we tried to execute it, ORA-06550 was thrown by the Oracle database. To debug ORA-06550 we can use "show error" statement as:

SQL> show error procedure myproc
Errors for PROCEDURE MYPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: Statement ignored
6/60     PLS-00201: identifier 'SAL' must be declared

Now we know variable SAL is not defined and must be written as c.sal. So we will need to make corrections in "myproc" as

SQL> create or replace procedure myproc
  2  as
  3  begin
  4     for c in (select * from scott.emp)
  5     loop
  6             dbms_output.put_line(c.empno || ' ' || c.ename || ' ' || c.sal);
  7     end loop;
  8  end;
  9  /
Procedure created.

SQL> set serveroutput on

SQL> exec myproc
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
PL/SQL procedure successfully completed.

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.