|
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;
|