ORA-06550: line n, column n


ORA-06550: line string, column string: string
Cause: Usually a PL/SQL compilation error.
Action: none

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e4100.htm

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.


Related Posts:
- ORA-00936 missing expression
- ORA-00911: invalid character
- ORA-01722: invalid number
- ORA-00904: invalid identifier
- ORA-06502: PL/SQL: numeric or value errorstring

5 comments:

  1. Hi every one!
    Can anyone give me some idea about PRAGMA INLINE?

    ReplyDelete
    Replies
    1. Pragma inline is compiler directive to replace its call with its definition, like we have #define in C language
      check this link out: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/inline_pragma.htm

      Delete
  2. i have created the package
    create or replace package maths
    as
    procedure addition(a in number, b in number,c in out number);
    function subtraction(a in number,b in number,c out number) return number;
    procedure multiplication(a in number,b in number,c out number);
    function division(a in number,b in number,c out number) return number;
    end maths;
    And i created package body,
    create or replace package body maths
    as
    procedure addition(a in number,b in number,c in out number)
    is
    begin
    c:=a+b;
    end addition;
    function subtraction(a in number,b in number,c out number) return number
    is
    begin
    c:=a-b;
    return c;
    end subtraction;
    procedure multiplication(a in number,b in number,c out number)
    is
    begin
    c:=a*b;
    end multiplication;
    function division(a in number,b in number,c out number) return number
    is
    begin
    c:=a/b;
    return c;
    end division;
    end maths;
    And then i called the procedure by using the code
    set serveroutput on
    declare
    x number;
    y number;
    z number;
    begin
    x:=10;
    y:=20;
    addition(x,y,z);
    dbms_output.put_line(z);
    end;
    but i am getting the below error:

    Error starting at line 148 in command:
    declare
    x number;
    y number;
    z number;
    begin
    x:=10;
    y:=20;
    addition(x,y,z);
    dbms_output.put_line(z);
    end;
    Error report:
    ORA-06550: line 8, column 1:
    PLS-00905: object SATYA.ADDITION is invalid
    ORA-06550: line 8, column 1:
    PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    HOW CAN I RESOLVE THIS ERROR CAN ANY ONE PLZ HELP ME:

    ReplyDelete
    Replies
    1. You forgot to add package name when calling the member function, use following
      declare
      x number;
      y number;
      z number;
      begin
      x:=10;
      y:=20;
      maths.addition(x,y,z);
      dbms_output.put_line(z);
      end;

      Delete
  3. This post is actually a good one it assists new net visitors, who are wishing for blogging.

    ReplyDelete