Oracle PL/SQL Tutorial/System Packages/UTL FILE

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

A PL/SQL block to read an operating system file called BLOB.JPG that contains 1 row of binary 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>


File open and close with UTL_FILE package

   <source lang="sql">

SQL> SQL> SQL> DECLARE

 2     lv_file_id_num  UTL_FILE.FILE_TYPE;
 3  BEGIN
 4     lv_file_id_num := UTL_FILE.FOPEN("c:\apps\loaders", "test4.dat", "W");
 5     UTL_FILE.FCLOSE(lv_file_id_num);
 6  END;
 7  /

SQL></source>


File open flags

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE play_with_files

 2     (loc_in IN VARCHAR2,
 3      file_in IN VARCHAR2,
 4      mode_in IN VARCHAR2,
 5      mixed_up IN BOOLEAN := FALSE)
 6  IS
 7     fID UTL_FILE.FILE_TYPE;
 8     line VARCHAR2(32767) := "fun stuff!";
 9  BEGIN
10     IF mixed_up AND mode_in = "R"
11     THEN
12        fID := UTL_FILE.FOPEN (loc_in, file_in, "W");
13     ELSIF mixed_up
14     THEN
15        fID := NULL;
16     ELSE
17        fID := UTL_FILE.FOPEN (loc_in, file_in, mode_in);
18     END IF;
19
20     IF mode_in = "R"
21     THEN
22        UTL_FILE.GET_LINE (fid, line);
23        DBMS_OUTPUT.PUT_LINE ("Read from " || file_in || " in " || loc_in);
24     ELSE
25        UTL_FILE.PUT_LINE (fid, line);
26        DBMS_OUTPUT.PUT_LINE ("Wrote to " || file_in || " in " || loc_in);
27     END IF;
28
29     UTL_FILE.FCLOSE (fid);
30  END;
31  /

SQL></source>


Open a file with mode passed in

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE FUNCTION fopen

 2     (loc_in IN VARCHAR2,
 3      file_in IN VARCHAR2,
 4      mode_in IN VARCHAR2 := "R")
 5      RETURN UTL_FILE.FILE_TYPE
 6  IS
 7     fID UTL_FILE.FILE_TYPE;
 8  BEGIN
 9     fID := UTL_FILE.FOPEN (loc_in, file_in, mode_in);
10     RETURN fid;
11  EXCEPTION
12     WHEN UTL_FILE.INVALID_OPERATION
13     THEN
14        IF mode_in = "A"
15        THEN
16           fID := UTL_FILE.FOPEN (loc_in, file_in, "W");
17           RETURN fid;
18        ELSE
19           RAISE;
20        END IF;
21  END;
22  /

SQL></source>


Save table records to a file

   <source lang="sql">

SQL> SQL> CREATE TABLE employees

 2  ( employee_id          number(10)      not null,
 3    last_name            varchar2(50)      not null,
 4    email                varchar2(30),
 5    hire_date            date,
 6    job_id               varchar2(30),
 7    department_id        number(10),
 8    salary               number(6),
 9    manager_id           number(6)
10  );

Table created. SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)

 2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);

1 row created. SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );

1 row created. SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);

1 row created. SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1004,  "Kate", "YourName@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);

1 row created. SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1005, "Dillon", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);

1 row created. SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)

 2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);

1 row created. SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);

1 row created. SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);

1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE emps2file (

 2     loc IN VARCHAR2,
 3     file IN VARCHAR2
 4     )
 5  IS
 6     fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, "W");
 7     line VARCHAR2(2000);
 8  BEGIN
 9     FOR rec IN (SELECT last_name, hire_date, salary FROM employee)
10     LOOP
11        line :=
12           rec.last_name || "," ||
13           TO_CHAR (rec.hire_date, "MM/DD/YYYY") || "," ||
14           rec.salary;
15        UTL_FILE.PUT_LINE (fid, line);
16     END LOOP;
17     UTL_FILE.FCLOSE (fid);
18  EXCEPTION
19     WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
20  END;
21  /

SQL> SQL> drop table employees;</source>


Use UTL_FILE package to create a file

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE fcreate (loc_in IN VARCHAR2, file_in IN VARCHAR2)

 2  IS
 3     fID UTL_FILE.FILE_TYPE;
 4  BEGIN
 5
 6     fID := UTL_FILE.FOPEN (loc_in, file_in, "W");
 7
 8     UTL_FILE.FCLOSE (fid);
 9  END;
10  /

Warning: Procedure created with compilation errors. SQL> SQL> show error Errors for PROCEDURE FCREATE: LINE/COL ERROR


-----------------------------------------------------------------

3/8 PL/SQL: Item ignored 3/8 PLS-00201: identifier "UTL_FILE" must be declared 6/4 PL/SQL: Statement ignored 6/4 PLS-00320: the declaration of the type of this expression is

        incomplete or malformed

8/4 PL/SQL: Statement ignored 8/21 PLS-00320: the declaration of the type of this expression is

        incomplete or malformed

SQL></source>


utl_file.invalid_&&firstparm

   <source lang="sql">

SQL> SQL> begin

 2     raise utl_file.invalid_&&firstparm;
 3  exception
 4      when others then dbms_output.put_line(sqlcode);
 5         dbms_output.put_line(sqlerrm);
 6  end;
 7  /

old 2: raise utl_file.invalid_&&firstparm; new 2: raise utl_file.invalid_--</source>


UTL_FILE.IS_OPEN

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE utlfile_test

 2  IS
 3    v_filehandle UTL_FILE.FILE_TYPE;
 4    v_newline VARCHAR2(1022);
 5  BEGIN
 6    IF NOT UTL_FILE.IS_OPEN(v_filehandle) THEN
 7      v_newline := "This is the line of text";
 8
 9    END IF;
10
11  END;
12  /

SQL> drop procedure utlfile_test; Procedure dropped. SQL> SQL></source>


UTL_File related exception

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE sp_utlfile

 2  IS
 3    v_filehandle UTL_FILE.FILE_TYPE;
 4    v_newline VARCHAR2(1023);
 5  BEGIN
 6    IF NOT UTL_FILE.IS_OPEN(v_filehandle) THEN
 7      v_filehandle := UTL_FILE.FOPEN("c:\temp", "input_file.txt", "r");
 8    END IF;
 9
10    LOOP
11      BEGIN
12
13        UTL_FILE.GET_LINE(v_filehandle, v_newline);
14        DBMS_OUTPUT.PUT_LINE (v_newline);
15      EXCEPTION
16
17        WHEN NO_DATA_FOUND THEN
18          EXIT;
19      END;
20    END LOOP;
21
22    UTL_FILE.FCLOSE(v_filehandle);
23
24  EXCEPTION
25    WHEN UTL_FILE.INVALID_PATH THEN
26      UTL_FILE.FCLOSE_ALL;
27      RAISE_APPLICATION_ERROR(-20051, "Invalid Path");
28
29    WHEN UTL_FILE.INVALID_MODE THEN
30      UTL_FILE.FCLOSE_ALL;
31      RAISE_APPLICATION_ERROR(-20052, "Invalid Mode");
32
33    WHEN UTL_FILE.INTERNAL_ERROR THEN
34      UTL_FILE.FCLOSE_ALL;
35      RAISE_APPLICATION_ERROR(-20053, "Internal Error");
36
37    WHEN UTL_FILE.INVALID_OPERATION THEN
38      UTL_FILE.FCLOSE_ALL;
39      RAISE_APPLICATION_ERROR(-20054, "Invalid Operation");
40
41    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
42      UTL_FILE.FCLOSE_ALL;
43      RAISE_APPLICATION_ERROR(-20055, "Invalid Operation");
44
45    WHEN UTL_FILE.WRITE_ERROR THEN
46      UTL_FILE.FCLOSE_ALL;
47      RAISE_APPLICATION_ERROR(-20056, "Invalid Operation");
48  END;
49  /

Warning: Procedure created with compilation errors. SQL> SQL> execute sp_utlfile ; BEGIN sp_utlfile ; END;

     *

ERROR at line 1: ORA-06550: line 1, column 7: PLS-00905: object sqle.SP_UTLFILE is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored

SQL></source>