Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. Due to this procedures ability to create OS layer files from within the database, its access must be controlled through system privileges. This article will show a common PLS-00201 error, which indicates a lack of access permissions and how to resolve it.
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 SQL*PLUS as the SYSDBA and create the user account LJCATT with the privileges: connect, create session, and resource to LJCATT.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jan 13 22:26:07 2009
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>
SQL> create user ljcatt identified by ljcatt;
User created.
SQL> grant connect to ljcatt;
Grant succeeded.
SQL> grant create session to ljcatt;
Grant succeeded.
SQL> grant resource to ljcatt;
Grant succeeded.
SQL>
4. Reconnect to SQL*PLUS as LJCATT and execute the file create_file.sql to create your procedure
Mylinux:/home/lcatt:>sqlplus ljcatt
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jan 13 22:43:17 2009
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
is
2 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
utl_file.fclose(v_write);
14 15
16 end test_file;
17 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE TEST_FILE:
LINE/COL ERROR
——– —————————————————————–
7/9 PL/SQL: Item ignored
7/9 PLS-00201: identifier ‘UTL_FILE’ must be declared
10/1 PL/SQL: Statement ignored
10/1 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
12/1 PL/SQL: Statement ignored
12/19 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
14/1 PL/SQL: Statement ignored
LINE/COL ERROR
——– —————————————————————–
14/17 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
SQL>
5. NOTE: The error received indicates that we do not have permissions to use the package UTL_FILE. Now connect to the database as sydba, execute the grant command: grant execute on utl_file to ljcatt;
SQL> connect / as sysdba
Connected.
SQL> grant execute on utl_file to ljcatt;
Grant succeeded.
SQL>
6. Connect back to the database as the user LJCATT, re-execute the SQL file create_file.sql, and execute the procedure test_file.
SQL> connect ljcatt
Enter password:
Connected.
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
end test_file;
16 17 /
Procedure created.
SQL> execute test_file
PL/SQL procedure successfully completed.
SQL>
The procedure now compiles without error and executes, due to the owner having proper privileges on the package UTL_FILE.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com