CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
user1181973
user1181973

Reputation: 99

Executing PL/SQL Procedure with UTL_FILE

I have following procedure:

    CREATE OR REPLACE PROCEDURE sal_status(
  p_dir IN VARCHAR2, p_filename IN VARCHAR2) IS
  f_file UTL_FILE.FILE_TYPE;
BEGIN
  f_file:= UTL_FILE.FOPEN (p_dir, p_filename, 'W');
  UTL_FILE.PUT_LINE(f_file, 'REPORT: GENERATED ON ' || SYSDATE);
  UTL_FILE.FCLOSE (f_file);

  EXCEPTION
 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
  RAISE_APPLICATION_ERROR(-20001,'Invalid File.');
 WHEN UTL_FILE.WRITE_ERROR THEN
  RAISE_APPLICATION_ERROR (-20002, 'Unable to write to file');


END sal_status;

This procedure compiled successfully, but when I try run/execute it:

BEGIN
  sal_status('UTL_FILE', 'test.txt');
END;

I have following errors:

Ora-29280: invalid directory path
Ora-06512: at SYS.UTL_FILE, line 41
Ora-06512: at SYS.UTL_FILE, line 478
ORA-06512: at HR.SAL_STATUS, line 5
ORA-06512: at line 2

Upvotes: 0

Views: 1646

Answers (2)

user1181973
user1181973

Reputation: 99

Thanks for everyone,

Correct variant provided below:

CREATE OR REPLACE PROCEDURE sal_status(
  p_dir IN VARCHAR2, p_filename IN VARCHAR2) IS
  f_file UTL_FILE.FILE_TYPE;
BEGIN
  f_file:= UTL_FILE.FOPEN ('P_DIR', 'test.txt', 'W');
  UTL_FILE.PUT_LINE(f_file, 'REPORT: GENERATED ON ' || SYSDATE);
  UTL_FILE.FCLOSE (f_file);

  EXCEPTION
 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
  RAISE_APPLICATION_ERROR(-20001,'Invalid File.');
 WHEN UTL_FILE.WRITE_ERROR THEN
  RAISE_APPLICATION_ERROR (-20002, 'Unable to write to file');
END sal_status;

BEGIN
  sal_status('P_DIR', 'test.txt');
END;

Upvotes: 0

Anton Zaviriukhin
Anton Zaviriukhin

Reputation: 741

As you may see in UTL_FILE documentation for FOPEN function:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003526
You need to create directory object
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm
CREATE OR REPLACE DIRECTORY UTL_FILE AS 'directory/path'
and grant read permission on this directory to your user
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#i2125999
GRANT READ ON UTL_FILE TO [your_user]

Upvotes: 3

Related Questions