Mega Code Archive
Use UTL_FILE to print a transacript to a file
<![CDATA[
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;
]]>