Oracle PL/SQL Tutorial/System Packages/UTL FILE — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 10:05, 26 мая 2010
Содержание
- 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.
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.
File open and close with UTL_FILE package
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>
File open flags
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>
Open a file with mode passed in
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>
Save table records to a file
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;
Use UTL_FILE package to create a file
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>
utl_file.invalid_&&firstparm
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_--
UTL_FILE.IS_OPEN
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>
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>