Oracle PL/SQL/PL SQL/Compile Error
Содержание
- 1 Build an anonymous block that will trigger an error.
- 2 Check error form stored procedure
- 3 Check error for procedure
- 4 Check the error
- 5 how DDL doesn"t work with PL/SQL
- 6 PLS-00306: wrong number or types of arguments in call
- 7 PLS-00363: expression "3" cannot be used as an assignment target
- 8 Set the PLSQL_WARNING level to DISABLE:ALL
- 9 This example illustrates the PLS-483 error
- 10 This package will not compile because the specification and body do not match.
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>