Oracle PL/SQL/System Packages/UTL FILE
Содержание
- 1 Catch different UTL_FILE related errors
- 2 Load the data into table myTable. You may use any valid number for column c1"s data.
- 3 Save data in a cursor to a file
- 4 Use UTL_FILE.FCLOSE to close a file
- 5 Use utl_file.fopen to open a file
- 6 Use UTL_FILE.PUT_LINE to write a line of text to a file
- 7 Use UTL_FILE to print a transacript to a file.
- 8 Use UTL_FILE to read from a file and insert into the lecturer table.
- 9 Writing "Hello World!" to a File
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;