Oracle PL/SQL/System Packages/UTL FILE

Catch different UTL_FILE related errors

  

SET ECHO ON
SET SERVEROUTPUT ON
DECLARE
     emp_data UTL_FILE.FILE_TYPE;
BEGIN
    emp_data := UTL_FILE.FOPEN ("c:\a","empdata.csv","W");
    UTL_FILE.FCLOSE (emp_data);
EXCEPTION
    WHEN UTL_FILE.internal_error THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: An internal error occurred.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.invalid_filehandle THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: The file handle was invalid.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.invalid_mode THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: An invalid open mode was given.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.invalid_operation THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: An invalid operation was attempted.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.invalid_path THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: An invalid path was give for the file.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.read_error THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: A read error occurred.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.write_error THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: A write error occurred.");
        UTL_FILE.FCLOSE_ALL;
    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE ("Some other error occurred.");
        UTL_FILE.FCLOSE_ALL;
END;
/

Load the data into table myTable. You may use any valid number for column c1"s 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.

Save data in a cursor to a file

  

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  MGR NUMBER(4),
                  HIREDATE DATE,
                  SAL NUMBER(7, 2),
                  COMM NUMBER(7, 2),
                  DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);

SET ECHO ON
SET SERVEROUTPUT ON
DECLARE
     emp_data UTL_FILE.FILE_TYPE;
BEGIN
    emp_data := UTL_FILE.FOPEN ("c:\a","empdata.csv","W");
    FOR emp IN (SELECT ename FROM emp) LOOP
        UTL_FILE.PUT_LINE (emp_data, emp.ename;
    END LOOP;
    UTL_FILE.FCLOSE (emp_data);
EXCEPTION
    WHEN UTL_FILE.internal_error THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: An internal error occurred.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.invalid_filehandle THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: The file handle was invalid.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.invalid_mode THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: An invalid open mode was given.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.invalid_operation THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: An invalid operation was attempted.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.invalid_path THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: An invalid path was give for the file.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.read_error THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: A read error occurred.");
        UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.write_error THEN
        DBMS_OUTPUT.PUT_LINE ("UTL_FILE: A write error occurred.");
        UTL_FILE.FCLOSE_ALL;
    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE ("Some other error occurred.");
        UTL_FILE.FCLOSE_ALL;
END;
/
drop table emp;

Use UTL_FILE.FCLOSE to close a file

  
DECLARE
  v_FileHandle UTL_FILE.FILE_TYPE;
BEGIN
  v_FileHandle := UTL_FILE.FOPEN("/tmp/", "utl_file.txt", "w");
  
  UTL_FILE.PUT_LINE(v_FileHandle, "This is line 1!");
  FOR v_Counter IN 2..11 LOOP
    UTL_FILE.PUTF(v_FileHandle, "This is line %s!\n", v_Counter);
  END LOOP;
  
  UTL_FILE.FCLOSE(v_FileHandle);
END;
/

--

Use utl_file.fopen to open a file

  
SQL>
SQL> declare
  2    l_file_handle utl_file.file_type;
  3  begin
  4    l_file_handle := utl_file.fopen( "/tmp", "tom.data", "w" );
  5  end;
  6  /
SQL> --

Use UTL_FILE.PUT_LINE to write a line of text to a file

  
CREATE OR REPLACE PROCEDURE SHAREFLE IS
  v_MyFileHandle UTL_FILE.FILE_TYPE;
  BEGIN
       v_MyFileHandle := UTL_FILE.FOPEN("C:\","HELLO.TXT","a");
       UTL_FILE.PUT_LINE(v_MyFileHandle, "Hello Again for the Second Time! " || TO_CHAR(SYSDATE,"MM-DD-YY HH:MI:SS AM"));
       UTL_FILE.FCLOSE(v_MyFileHandle);
 EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE
                ("ERROR " || TO_CHAR(SQLCODE) || SQLERRM);
           NULL; 
 END;

Use UTL_FILE to print a transacript to a file.

  
CREATE TABLE lecturer (
  id               NUMBER(5) PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3)
  );
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10001, "Scott", "Lawson","Computer Science", 11);
INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
              VALUES (10002, "Mar", "Wells","History", 4);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10003, "Jone", "Bliss","Computer Science", 8);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10004, "Man", "Kyte","Economics", 8);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10005, "Pat", "Poll","History", 4);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10006, "Tim", "Viper","History", 4);
CREATE TABLE myStudent (
  student_id NUMBER(5) NOT NULL,
  department CHAR(3)   NOT NULL,
  course     NUMBER(3) NOT NULL,
  grade      CHAR(1)
  );


INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000, "CS", 102, "A");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10002, "CS", 102, "B");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10003, "CS", 102, "C");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10001, "HIS", 101, "B");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10002, "HIS", 101, "B");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10003, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10004, "HIS", 101, "C");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10005, "HIS", 101, "C");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10006, "HIS", 101, "E");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10007, "HIS", 101, "B");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10008, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10009, "HIS", 101, "D");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10010, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10008, "NUT", 307, "A");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10010, "NUT", 307, "A");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10009, "MUS", 410, "B");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10006, "MUS", 410, "E");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10011, "MUS", 410, "B");
INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000, "MUS", 410, "B");

CREATE OR REPLACE PROCEDURE PrintTranscript (
  p_ID IN lecturer.ID%TYPE,
  p_FileDir IN VARCHAR2,
  p_FileName IN VARCHAR2) AS
  myLecturerGPA NUMBER;
  myLecturerRecord  lecturer%ROWTYPE;
  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NumCredits NUMBER;
  CURSOR c_CurrentClasses IS
    SELECT *
      FROM myStudent
      WHERE student_id = p_ID;
BEGIN
  v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, "a");
  SELECT *
    INTO myLecturerRecord
    FROM lecturer
    WHERE ID = p_ID;
  UTL_FILE.PUTF(v_FileHandle, "Student ID: %s\n", myLecturerRecord.ID);
  UTL_FILE.PUTF(v_FileHandle, "Student Name: %s %s\n", myLecturerRecord.first_name, myLecturerRecord.last_name);
  UTL_FILE.PUTF(v_FileHandle, "Major: %s\n", myLecturerRecord.major);
  UTL_FILE.PUTF(v_FileHandle, "Transcript Printed on: %s\n\n\n", TO_CHAR(SYSDATE, "Mon DD,YYYY HH24:MI:SS"));
  UTL_FILE.FCLOSE(v_FileHandle);
EXCEPTION
  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20061, "PrintTranscript: Invalid Operation");
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20062, "PrintTranscript: Invalid File Handle");
  WHEN UTL_FILE.WRITE_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20063, "PrintTranscript: Write Error");
  WHEN UTL_FILE.INVALID_MODE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20064, "PrintTranscript: Invalid Mode");
  WHEN UTL_FILE.INTERNAL_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20065, "PrintTranscript: Internal Error");
END PrintTranscript;
/
drop table lecturer;
drop table myStudent;

Use UTL_FILE to read from a file and insert into the lecturer table.

  
CREATE TABLE lecturer (
  id               NUMBER(5) PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3)
  );
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10001, "Scott", "Lawson","Computer Science", 11);
INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
              VALUES (10002, "Mar", "Wells","History", 4);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10003, "Jone", "Bliss","Computer Science", 8);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10004, "Man", "Kyte","Economics", 8);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10005, "Pat", "Poll","History", 4);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10006, "Tim", "Viper","History", 4);

CREATE OR REPLACE PROCEDURE Loadlecturer (
  p_FileDir  IN VARCHAR2,
  p_FileName IN VARCHAR2,
  p_TotalInserted IN OUT NUMBER) AS
  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NewLine  VARCHAR2(100);  -- Input line
  myFirstName lecturer.first_name%TYPE;
  v_LastName lecturer.last_name%TYPE;
  v_Major lecturer.major%TYPE;
  v_FirstComma NUMBER;
  v_SecondComma NUMBER;
BEGIN
  v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, "r");
  p_TotalInserted := 0;
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;
    v_FirstComma := INSTR(v_NewLine, ",", 1, 1);
    v_SecondComma := INSTR(v_NewLine, ",", 1, 2);
    myFirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
    v_LastName := SUBSTR(v_NewLine, v_FirstComma + 1,
                         v_SecondComma - v_FirstComma - 1);
    v_Major := SUBSTR(v_NewLine, v_SecondComma + 1);
    INSERT INTO lecturer (ID, first_name, last_name, major) VALUES (1, myFirstName, v_LastName, v_Major);
    p_TotalInserted := p_TotalInserted + 1;
  END LOOP;
  UTL_FILE.FCLOSE(v_FileHandle);
  COMMIT;
EXCEPTION
  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20051, "Loadlecturer: Invalid Operation");
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20052, "Loadlecturer: Invalid File Handle");
  WHEN UTL_FILE.READ_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20053, "Loadlecturer: Read Error");
  WHEN UTL_FILE.INVALID_PATH THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20054, "Loadlecturer: Invalid Path");
  WHEN UTL_FILE.INVALID_MODE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20055, "Loadlecturer: Invalid Mode");
  WHEN UTL_FILE.INTERNAL_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20056, "Loadlecturer: Internal Error");
  WHEN VALUE_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20057, "Loadlecturer: Value Error");
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE;
END Loadlecturer;
/
drop table lecturer;

Writing "Hello World!" to a File

  
CREATE OR REPLACE PROCEDURE HELLOFLE IS
      v_MyFileHandle UTL_FILE.FILE_TYPE;
 BEGIN
      v_MyFileHandle := UTL_FILE.FOPEN("C:\","HELLO.TXT","a");
      UTL_FILE.PUT_LINE(v_MyFileHandle,"Hello World! " || TO_CHAR(SYSDATE,"MM-DD-YY HH:MI:SS AM"));
     UTL_FILE.FCLOSE(v_MyFileHandle);
EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE("ERROR " || TO_CHAR(SQLCODE) || SQLERRM);
           NULL;
END;