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;

Related examples in the same category

1.	Writing "Hello World!" to a File
2.	Use UTL_FILE.PUT_LINE to write a line of text to a file
3.	Use utl_file.fopen to open a file
4.	Use UTL_FILE.FCLOSE to close a file
5.	Use UTL_FILE to read from a file and insert into the lecturer table.
6.	Catch different UTL_FILE related errors
7.	Save data in a cursor to a file
8.	Load the data into table myTable. You may use any valid number for column c1's data.

Use UTL_FILE to print a transacript to a file. : UTL_FILE « System Packages « Oracle PL / SQL

Related examples in the same category