Oracle PL/SQL/System Packages/UTL FILE — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 12:55, 26 мая 2010

Catch different UTL_FILE related errors

   <source lang="sql">

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

 </source>


Load the data into table myTable. You may use any valid number for column c1"s data.

   <source lang="sql">

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. 



 </source>


Save data in a cursor to a file

   <source lang="sql">

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; 

 </source>


Use UTL_FILE.FCLOSE to close a file

   <source lang="sql">

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

-- 

 </source>


Use utl_file.fopen to open a file

   <source lang="sql">

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

 </source>


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

   <source lang="sql">

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


Use UTL_FILE to print a transacript to a file.

   <source lang="sql">

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; 


 </source>


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

   <source lang="sql">

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; 

 </source>


Writing "Hello World!" to a File

   <source lang="sql">

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; 

 </source>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Packages/UTL_FILE&oldid=1291»