Oracle PL/SQL Tutorial/System Packages/UTL FILE
Содержание
- 1 A PL/SQL block to read an operating system file called BLOB.JPG that contains 1 row of binary data.
- 2 File open and close with UTL_FILE package
- 3 File open flags
- 4 Open a file with mode passed in
- 5 Save table records to a file
- 6 Use UTL_FILE package to create a file
- 7 utl_file.invalid_&&firstparm
- 8 UTL_FILE.IS_OPEN
- 9 UTL_File related exception
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>
<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>