в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:48Z

1 версия

Новая страница

== Open the output file for writing==



<source lang="sql">
SQL>
SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY,
3 clob_data CLOB);
Table created.
SQL>
SQL> DECLARE
2 clob_pointer CLOB;
3 v_Buf VARCHAR2(1000);
4 Amount BINARY_INTEGER :=1000;
5 Position INTEGER :=1;
6 fp UTL_FILE.FILE_TYPE;
7 BEGIN
8
9 insert into myClob values (1,EMPTY_CLOB());
10
11 SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1;
12
13 if (DBMS_LOB.ISOPEN(clob_pointer)!=1) then
14 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READONLY);
15 end if;
16
17 fp :=UTL_FILE.FOPEN("BFILE_DIR","output.dat","w");
18
19 LOOP
20 BEGIN
21 DBMS_LOB.READ (clob_pointer,Amount,Position,v_Buf);
22
23 UTL_FILE.PUT_LINE(fp,v_Buf,TRUE);
24
25 Position :=Position +Amount;
26
27 EXCEPTION
28
29 WHEN NO_DATA_FOUND THEN
30
31 EXIT;
32
33 END;
34
35 END LOOP;
36
37 UTL_FILE.FCLOSE(fp);
38
39 DBMS_LOB.CLOSE (clob_pointer);
40 END;
41 /

SQL>
SQL> drop table myClob;
Table dropped.</source>

== Save lines to a file==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE PROCEDURE put_line
2 (file_in IN UTL_FILE.FILE_TYPE,
3 line_in IN VARCHAR2,
4
5 max_line_size_in IN INTEGER := 1023)
6 IS
7 v_line VARCHAR2(32767);
8 v_linelen CONSTANT INTEGER := LENGTH (line);
9 v_start INTEGER := 1;
10 v_max CONSTANT INTEGER := max_line_size_in - 1;
11 BEGIN
12 LOOP
13 EXIT WHEN v_start > v_linelen OR v_linelen IS NULL;
14 v_line := SUBSTR (line_in, v_start, v_max);
15 IF v_start + v_max < v_linelen
16 THEN
17 v_line := v_line || "-";
18 END IF;
19 UTL_FILE.PUT_LINE (file_in, v_line);
20 v_start := v_start + v_max;
21 END LOOP;
22 END;
23 /</source>

== Save string to a file with UTL_FILE.PUTF==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE PROCEDURE saveStringToFile(fid IN UTL_FILE.FILE_TYPE,it_is IN VARCHAR2)
2 IS
3 BEGIN
4 UTL_FILE.PUTF (fid, "string",it_is);
5 END;
6 /

SQL>
SQL></source>

== Write text to a file with UTL_FILE package==



<source lang="sql">
SQL>
SQL> DECLARE
2 fid UTL_FILE.FILE_TYPE;
3 f VARCHAR(200);
4 v VARCHAR2(32767);
5 topdir VARCHAR2(100);
6
7 PROCEDURE recNgo (str IN VARCHAR2)
8 IS
9 BEGIN
10 DBMS_OUTPUT.PUT_LINE ("UTL_FILE error " || str || " in " || f);
11 UTL_FILE.FCLOSE (fid);
12 END;
13
14 PROCEDURE writeit
15 IS
16 BEGIN
17 f := "c:\a";
18 fid := UTL_FILE.FOPEN (f, "temp.txt", "W");
19 UTL_FILE.PUT_LINE (fid, v);
20 UTL_FILE.FCLOSE (fid);
21 DBMS_OUTPUT.PUT_LINE ("Successful write to " || f);
22 EXCEPTION
23 WHEN UTL_FILE.INVALID_PATH THEN recNgo ("invalid_path");
24 WHEN UTL_FILE.INVALID_MODE THEN recNgo ("invalid_mode");
25 WHEN UTL_FILE.INVALID_FILEHANDLE THEN recNgo ("invalid_filehandle");
26 WHEN UTL_FILE.INVALID_OPERATION THEN recNgo ("invalid_operation");
27 WHEN UTL_FILE.READ_ERROR THEN recNgo ("read_error");
28 WHEN UTL_FILE.WRITE_ERROR THEN recNgo ("write_error");
29 WHEN UTL_FILE.INTERNAL_ERROR THEN recNgo ("internal_error");
30 END;
31 BEGIN
32 writeit;
33 END;
34 /

SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Packages/File Write with UTL FILE - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия