When you create a PL/SQL block of code within Oracle in the form of a procedure, function, or package and a compilation error occurs; Oracle will not immediately give you the error details. This article details the procedure to view errors generated by a PL/SQL block within SQL*PLUS.
1. Logon to your Oracle database server as the Oracle software owner.
2. Create a file called create_file.sql with the following SQL.
create or replace procedure test_file
is
v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin
v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
utl_file.put_line(v_write, v_record);
utl_file.fclose(v_write);
end test_file;
/
3. Logon to SQLPLUS with SYSDBA privileges.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:00:09 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
4. Set FEEDBACK and ECHO to on and execute the file create_file.sql.
SQL> set echo on
SQL> set feedback on
SQL> @./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /
Warning: Procedure created with compilation errors.
SQL>
5. The procedure was created with compilation errors, however it does not show you the errors. To see the errors generated in your procedure creation statement, use the SQL*PLUS command: show errors
SQL> show errors
Errors for PROCEDURE TEST_FILE:
LINE/COL ERROR
——– —————————————————————–
10/1 PL/SQL: Statement ignored
10/1 PLS-00201: identifier ‘V_WRITE’ must be declared
12/1 PL/SQL: Statement ignored
12/19 PLS-00201: identifier ‘V_WRITE’ must be declared
14/1 PL/SQL: Statement ignored
14/17 PLS-00201: identifier ‘V_WRITE’ must be declared
SQL>
6. Now you can see that the procedure creation statement is missing the definition for the variable V_WRITE. To correct this error replace the contents of your create_file.sql file with the following text, where the variable v_write is defined as utl_file.file_type.
create or replace procedure test_file
is
v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin
v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
utl_file.put_line(v_write, v_record);
utl_file.fclose(v_write);
end test_file;
/
7. Re-enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file create_file.sql.
Mylinux:/home/lcatt:>sqlplus ljcatt
SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:43:17 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /
Procedure created.
SQL>
The procedure now compiles without error, through the use of the SQL*PLUS command ‘show errors’ which allowed us to see the detailed error message provided by the Oracle RDBMS.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com