Oracle PL/SQL Tutorial/System Packages/UTL FILE

A PL/SQL block to read an operating system file called BLOB.JPG that contains 1 row of binary data.

SQL>
SQL>
SQL> create table myTable(
  2     c1 number primary key,
  3     c2 blob
  4  );
Table created.
SQL>
SQL>
SQL> DECLARE
  2    v_raw           RAW(32767);
  3    v_file_id       UTL_FILE.FILE_TYPE;
  4  BEGIN
  5      v_file_id:=
  6      UTL_FILE.FOPEN("c:\","blob.jpg","r",32767);
  7      UTL_FILE.GET_RAW (v_file_id,v_raw);
  8      INSERT INTO myTable VALUES (1,v_raw);
  9      UTL_FILE.FCLOSE( v_file_id);
 10  END;
 11  /

SQL>
SQL>
SQL> drop table myTable;
Table dropped.

File open and close with UTL_FILE package

SQL>
SQL>
SQL> DECLARE
  2     lv_file_id_num  UTL_FILE.FILE_TYPE;
  3  BEGIN
  4     lv_file_id_num := UTL_FILE.FOPEN("c:\apps\loaders", "test4.dat", "W");
  5     UTL_FILE.FCLOSE(lv_file_id_num);
  6  END;
  7  /

SQL>

File open flags

SQL>
SQL> CREATE OR REPLACE PROCEDURE play_with_files
  2     (loc_in IN VARCHAR2,
  3      file_in IN VARCHAR2,
  4      mode_in IN VARCHAR2,
  5      mixed_up IN BOOLEAN := FALSE)
  6  IS
  7     fID UTL_FILE.FILE_TYPE;
  8     line VARCHAR2(32767) := "fun stuff!";
  9  BEGIN
 10     IF mixed_up AND mode_in = "R"
 11     THEN
 12        fID := UTL_FILE.FOPEN (loc_in, file_in, "W");
 13     ELSIF mixed_up
 14     THEN
 15        fID := NULL;
 16     ELSE
 17        fID := UTL_FILE.FOPEN (loc_in, file_in, mode_in);
 18     END IF;
 19
 20     IF mode_in = "R"
 21     THEN
 22        UTL_FILE.GET_LINE (fid, line);
 23        DBMS_OUTPUT.PUT_LINE ("Read from " || file_in || " in " || loc_in);
 24     ELSE
 25        UTL_FILE.PUT_LINE (fid, line);
 26        DBMS_OUTPUT.PUT_LINE ("Wrote to " || file_in || " in " || loc_in);
 27     END IF;
 28
 29     UTL_FILE.FCLOSE (fid);
 30  END;
 31  /
SQL>

Open a file with mode passed in

SQL>
SQL> CREATE OR REPLACE FUNCTION fopen
  2     (loc_in IN VARCHAR2,
  3      file_in IN VARCHAR2,
  4      mode_in IN VARCHAR2 := "R")
  5      RETURN UTL_FILE.FILE_TYPE
  6  IS
  7     fID UTL_FILE.FILE_TYPE;
  8  BEGIN
  9     fID := UTL_FILE.FOPEN (loc_in, file_in, mode_in);
 10     RETURN fid;
 11  EXCEPTION
 12     WHEN UTL_FILE.INVALID_OPERATION
 13     THEN
 14        IF mode_in = "A"
 15        THEN
 16           fID := UTL_FILE.FOPEN (loc_in, file_in, "W");
 17           RETURN fid;
 18        ELSE
 19           RAISE;
 20        END IF;
 21  END;
 22  /

SQL>

Save table records to a file

SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );
Table created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  "Kate", "YourName@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, "Dillon", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE emps2file (
  2     loc IN VARCHAR2,
  3     file IN VARCHAR2
  4     )
  5  IS
  6     fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, "W");
  7     line VARCHAR2(2000);
  8  BEGIN
  9     FOR rec IN (SELECT last_name, hire_date, salary FROM employee)
 10     LOOP
 11        line :=
 12           rec.last_name || "," ||
 13           TO_CHAR (rec.hire_date, "MM/DD/YYYY") || "," ||
 14           rec.salary;
 15        UTL_FILE.PUT_LINE (fid, line);
 16     END LOOP;
 17     UTL_FILE.FCLOSE (fid);
 18  EXCEPTION
 19     WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
 20  END;
 21  /

SQL>
SQL> drop table employees;

Use UTL_FILE package to create a file

SQL>
SQL> CREATE OR REPLACE PROCEDURE fcreate (loc_in IN VARCHAR2, file_in IN VARCHAR2)
  2  IS
  3     fID UTL_FILE.FILE_TYPE;
  4  BEGIN
  5
  6     fID := UTL_FILE.FOPEN (loc_in, file_in, "W");
  7
  8     UTL_FILE.FCLOSE (fid);
  9  END;
 10  /
Warning: Procedure created with compilation errors.
SQL>
SQL> show error
Errors for PROCEDURE FCREATE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/8      PL/SQL: Item ignored
3/8      PLS-00201: identifier "UTL_FILE" must be declared
6/4      PL/SQL: Statement ignored
6/4      PLS-00320: the declaration of the type of this expression is
         incomplete or malformed
8/4      PL/SQL: Statement ignored
8/21     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed
SQL>

utl_file.invalid_&&firstparm

SQL>
SQL> begin
  2     raise utl_file.invalid_&&firstparm;
  3  exception
  4      when others then dbms_output.put_line(sqlcode);
  5         dbms_output.put_line(sqlerrm);
  6  end;
  7  /
old   2:    raise utl_file.invalid_&&firstparm;
new   2:    raise utl_file.invalid_--

UTL_FILE.IS_OPEN

SQL>
SQL> CREATE OR REPLACE PROCEDURE utlfile_test
  2  IS
  3    v_filehandle UTL_FILE.FILE_TYPE;
  4    v_newline VARCHAR2(1022);
  5  BEGIN
  6    IF NOT UTL_FILE.IS_OPEN(v_filehandle) THEN
  7      v_newline := "This is the line of text";
  8
  9    END IF;
 10
 11  END;
 12  /

SQL> drop procedure utlfile_test;
Procedure dropped.
SQL>
SQL>

UTL_File related exception

SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_utlfile
  2  IS
  3    v_filehandle UTL_FILE.FILE_TYPE;
  4    v_newline VARCHAR2(1023);
  5  BEGIN
  6    IF NOT UTL_FILE.IS_OPEN(v_filehandle) THEN
  7      v_filehandle := UTL_FILE.FOPEN("c:\temp", "input_file.txt", "r");
  8    END IF;
  9
 10    LOOP
 11      BEGIN
 12
 13        UTL_FILE.GET_LINE(v_filehandle, v_newline);
 14        DBMS_OUTPUT.PUT_LINE (v_newline);
 15      EXCEPTION
 16
 17        WHEN NO_DATA_FOUND THEN
 18          EXIT;
 19      END;
 20    END LOOP;
 21
 22    UTL_FILE.FCLOSE(v_filehandle);
 23
 24  EXCEPTION
 25    WHEN UTL_FILE.INVALID_PATH THEN
 26      UTL_FILE.FCLOSE_ALL;
 27      RAISE_APPLICATION_ERROR(-20051, "Invalid Path");
 28
 29    WHEN UTL_FILE.INVALID_MODE THEN
 30      UTL_FILE.FCLOSE_ALL;
 31      RAISE_APPLICATION_ERROR(-20052, "Invalid Mode");
 32
 33    WHEN UTL_FILE.INTERNAL_ERROR THEN
 34      UTL_FILE.FCLOSE_ALL;
 35      RAISE_APPLICATION_ERROR(-20053, "Internal Error");
 36
 37    WHEN UTL_FILE.INVALID_OPERATION THEN
 38      UTL_FILE.FCLOSE_ALL;
 39      RAISE_APPLICATION_ERROR(-20054, "Invalid Operation");
 40
 41    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
 42      UTL_FILE.FCLOSE_ALL;
 43      RAISE_APPLICATION_ERROR(-20055, "Invalid Operation");
 44
 45    WHEN UTL_FILE.WRITE_ERROR THEN
 46      UTL_FILE.FCLOSE_ALL;
 47      RAISE_APPLICATION_ERROR(-20056, "Invalid Operation");
 48  END;
 49  /
Warning: Procedure created with compilation errors.
SQL>
SQL> execute sp_utlfile ;
BEGIN sp_utlfile ; END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object sqle.SP_UTLFILE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>