Oracle PL/SQL/PL SQL/Compile Error

Build an anonymous block that will trigger an error.

    
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> 
SQL> DECLARE
  2
  3    
  4    my_string         VARCHAR2(1) := " ";
  5    my_number         NUMBER;
  6
  7  BEGIN
  8
  9    
 10    my_number := TO_NUMBER(my_string);
 11
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 10

SQL>
SQL>
SQL>

Check error form stored procedure

    
SQL> CREATE TABLE products(
  2    name            VARCHAR2(50),
  3    pack_size       VARCHAR2(30),
  4    status          VARCHAR2(20),
  5    price      NUMBER(8,2),
  6    min_price       NUMBER(8,2)
  7  );
Table created.
SQL>
SQL>
SQL> create or replace procedure print_products
  2  as
  3  declare
  4      cursor get_data is select name, price from products;
  5  begin
  6       for i in get_data
  7       LOOP
  8          if i.price > 50 then
  9               dbms_output.put_line(i.name || " Price: " || i.price);
 10          else
 11               dbms_output.put_line(i.name || " Product under 50");
 12          end if;
 13       END LOOP;
 14  end;
 15  /
Warning: Procedure created with compilation errors.
SQL>
SQL> show errors
Errors for PROCEDURE PRINT_PRODUCTS:

LINE/COL
--------
ERROR
------------------------------------------------------
3/1
PLS-00103: Encountered the symbol "DECLARE" when
expecting one of the following:
begin function package pragma procedure subtype type
use
<an identifier> <a double-quoted delimited-identifier>
form
current cursor external language
The symbol "begin" was substituted for "DECLARE" to
continue.
14/4
PLS-00103: Encountered the symbol "end-of-file" when
expecting one of the following:
begin case declare end exception exit for goto if loop
mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock
insert
open rollback savepoint set sql execute commit forall
merge

                          Page:   2
LINE/COL
--------
ERROR
------------------------------------------------------
pipe
SQL>
SQL>
SQL> drop table products;
Table dropped.
SQL>
SQL>

Check error for procedure

    
SQL>
SQL>
SQL>
SQL> create or replace procedure A(p number) is
  2    begin
  3        B(p+1);
  4    end;
  5   /
Warning: Procedure created with compilation errors.
SQL>
SQL> show errors
Errors for PROCEDURE A:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/7      PL/SQL: Statement ignored
3/7      PLS-00905: object sqle.B is invalid
SQL>
SQL> create or replace procedure B(p number) is
  2    begin
  3        A(p+1);
  4    end;
  5   /
Warning: Procedure created with compilation errors.
SQL>
SQL>
SQL> show errors
Errors for PROCEDURE B:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/7      PL/SQL: Statement ignored
3/7      PLS-00905: object sqle.A is invalid
SQL>

Check the error

  
SQL>
SQL>
SQL> -- function with no return type
SQL>
SQL>
SQL>  create or replace
  2    function no_return_type as
  3    begin
  4      return null;
  5    end no_return_type;
  6    /
Warning: Function created with compilation errors.
SQL>
SQL>  show errors
Errors for FUNCTION NO_RETURN_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/25     PLS-00103: Encountered the symbol "AS" when expecting one of the
         following:
         ( return compress compiled wrapped
SQL>

how DDL doesn"t work with PL/SQL

    
SQL>
SQL> BEGIN
  2     CREATE TABLE ddl_table (id NUMBER(10));
  3  EXCEPTION
  4     WHEN OTHERS
  5     THEN
  6        DBMS_OUTPUT.PUT_LINE(sqlerrm);
  7  END;
  8  /
SQL>

PLS-00306: wrong number or types of arguments in call

    
SQL>
SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
  2  /
Type created.
SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
  2   BEGIN
  3       FOR i IN list_in.FIRST..list_in.LAST LOOP
  4         IF list_in.EXISTS(i) THEN
  5           DBMS_OUTPUT.PUT_LINE("List ["||list_in(i)||"]");
  6         END IF;
  7       END LOOP;
  8   END print_list;
  9   /
Procedure created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2     number_list NUMBER_VARRAY := number_varray(1,2,3,4,5);
  3
  4   BEGIN
  5     print_list(number_list);
  6
  7
  8     number_list.TRIM(3);
  9
 10     print_list(number_list);
 11   END;
 12   /
   print_list(number_list);
   *
ERROR at line 5:
ORA-06550: line 5, column 4:
PLS-00306: wrong number or types of arguments in call to "PRINT_LIST"
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored
ORA-06550: line 10, column 4:
PLS-00306: wrong number or types of arguments in call to "PRINT_LIST"
ORA-06550: line 10, column 4:
PL/SQL: Statement ignored

PLS-00363: expression "3" cannot be used as an assignment target

    
SQL>
SQL> 
SQL> CREATE OR REPLACE PROCEDURE ModeInOut (p_InOut IN OUT NUMBER) IS
  2
  3     v_LocalVariable  NUMBER := 0;
  4   BEGIN
  5     IF (p_InOut IS NULL) THEN
  6       DBMS_OUTPUT.PUT_LINE("p_InOut is NULL");
  7     ELSE
  8       DBMS_OUTPUT.PUT_LINE("p_InOut = " || p_InOut);
  9     END IF;
 10
 11     v_LocalVariable := p_InOut;
 12
 13     p_InOut := 8;
 14
 15     DBMS_OUTPUT.PUT("At end of ModeInOut: ");
 16     IF (p_InOut IS NULL) THEN
 17       DBMS_OUTPUT.PUT_LINE("p_InOut is NULL");
 18     ELSE
 19       DBMS_OUTPUT.PUT_LINE("p_InOut = " || p_InOut);
 20     END IF;
 21   END ModeInOut;
 22    /
Procedure created.
SQL>
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> DECLARE
  2     v_InOut NUMBER := 1;
  3   BEGIN
  4     -- Call ModeInOut with a variable, which should be modified.
  5     DBMS_OUTPUT.PUT_LINE("Before calling ModeInOut, v_InOut = " ||v_InOut);
  6     ModeInOut(v_InOut);
  7     DBMS_OUTPUT.PUT_LINE("After calling ModeInOut, v_InOut = " || v_InOut);
  8   END;
  9   /
Before calling ModeInOut, v_InOut = 1
p_InOut = 1
At end of ModeInOut: p_InOut = 8
After calling ModeInOut, v_InOut = 8
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> BEGIN
  2     ModeOut(3);
  3   END;
  4   /
   ModeOut(3);
           *
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00363: expression "3" cannot be used as an assignment target
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

SQL>
SQL>
SQL>
SQL> BEGIN
  2     ModeIn(3);
  3   END;
  4   /
Inside ModeIn: p_In = 3
p_In = 3
PL/SQL procedure successfully completed.
SQL>
SQL>

Set the PLSQL_WARNING level to DISABLE:ALL

    
SQL> CREATE TABLE books (
  2    isbn      CHAR(10) PRIMARY KEY,
  3    category  VARCHAR2(20),
  4    title     VARCHAR2(100),
  5    num_pages NUMBER,
  6    price     NUMBER,
  7    copyright NUMBER(4),
  8    emp1   NUMBER,
  9    emp2   NUMBER,
 10    emp3   NUMBER
 11  );
Table created.
SQL>
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);
1 row created.
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);
1 row created.
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created.
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("4", "Database", "SQL", 535, 39.99, 2002, 4, 5, 9);
1 row created.
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ("5", "Database", "Java", 487, 39.99, 2002, 10, 11);
1 row created.
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ("6", "Database", "JDBC", 592, 39.99, 2002, 12, 13);
1 row created.
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("7", "Database", "XML", 500, 39.99, 2002, 1, 2, 3);
1 row created.
SQL>
SQL> CALL DBMS_WARNING.SET_WARNING_SETTING_STRING("DISABLE:ALL", "SESSION");
Call completed.
SQL>
SQL> CREATE OR REPLACE PROCEDURE compile_warning
  2  AS
  3     v_title VARCHAR2(100);
  4     CURSOR dbms_warning_cur IS SELECT title FROM books;
  5  BEGIN
  6     OPEN dbms_warning_cur;
  7     LOOP
  8     FETCH dbms_warning_cur INTO v_title;
  9         DBMS_OUTPUT.PUT_LINE("Titles Available: "||v_title);
 10     END LOOP;
 11     CLOSE dbms_warning_cur;
 12  END;
 13  /
Procedure created.
SQL>
SQL>
SQL> drop table books;
Table dropped.
SQL>

This example illustrates the PLS-483 error

    
SQL> CREATE TABLE log_table(
  2    code     VARCHAR2(100),
  3    message     VARCHAR2(100),
  4    info     VARCHAR2(100));
Table created.
SQL>
SQL>
SQL> DECLARE
  2    myException1 EXCEPTION;
  3    myException2 EXCEPTION;
  4  BEGIN
  5    RAISE myException1;
  6  EXCEPTION
  7    WHEN myException2 THEN
  8      INSERT INTO log_table (info)VALUES ("Handler 1 executed!");
  9    WHEN myException1 THEN
 10      INSERT INTO log_table (info)VALUES ("Handler 3 executed!");
 11    WHEN myException1 OR myException2 THEN
 12      INSERT INTO log_table (info)VALUES ("Handler 4 executed!");
 13  END;
 14  /
  WHEN myException1 OR myException2 THEN
  *
ERROR at line 11:
ORA-04045: errors during recompilation/revalidation of sqle.LOG_ERRORS
ORA-01031: insufficient privileges
ORA-06550: line 11, column 3:
PLS-00483: exception "MYEXCEPTION2" may appear in at most one exception handler
in this block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

SQL>
SQL> drop table log_table;
Table dropped.

This package will not compile because the specification and body do not match.

    
SQL>
SQL> CREATE OR REPLACE PACKAGE PackageA AS
  2     FUNCTION FunctionA(p1 IN NUMBER,p2 IN DATE)
  3       RETURN VARCHAR2;
  4   END PackageA;
  5   /
Package created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY PackageA AS
  2     FUNCTION FunctionA(p1 IN CHAR)
  3       RETURN VARCHAR2;
  4   END PackageA;
  5   /
Warning: Package Body created with compilation errors.
SQL>
SQL>
SQL>
SQL> show errors
Errors for PACKAGE BODY PACKAGEA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/13     PLS-00323: subprogram or cursor "FUNCTIONA" is declared in a
         package specification and must be defined in the package body
2/13     PLS-00328: A subprogram body must be defined for the forward
         declaration of FUNCTIONA.
SQL>
SQL>