в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:49Z

1 версия

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

== Get a line from a file with UTL_FILE package==



<source lang="sql">
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE get_line(file_in IN UTL_FILE.FILE_TYPE, line_out OUT VARCHAR2, eof_out OUT BOOLEAN)
2 IS
3 BEGIN
4 UTL_FILE.GET_LINE (file_in, line_out);
5 eof_out := FALSE;
6 EXCEPTION
7 WHEN NO_DATA_FOUND
8 THEN
9 line_out := NULL;
10 eof_out := TRUE;
11 END;
12 /

SQL>
SQL></source>

== Read file==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE PROCEDURE readit
2 IS
3 v_filehandle_input UTL_FILE.FILE_TYPE;
4 v_newline VARCHAR2(32767);
5 BEGIN
6 v_filehandle_input := UTL_FILE.FOPEN("c:\temp","functions.sql", "r", 32767 );
7 loop
8 BEGIN
9 UTL_FILE.GET_LINE(v_filehandle_input,v_newline);
10 DBMS_OUTPUT.PUT_LINE(v_newline);
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN EXIT ;
13 END;
14 end loop;
15 UTL_FILE.FCLOSE (v_filehandle_input) ;
16 end;
17 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE READIT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/22 PL/SQL: Item ignored
3/22 PLS-00201: identifier "UTL_FILE" must be declared
6/3 PL/SQL: Statement ignored
6/3 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
9/5 PL/SQL: Statement ignored
9/23 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
15/3 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
15/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
SQL>
SQL>
SQL>
SQL></source>

== Read from the CLOB in chunks of 1000 characters and write to the output file==



<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
15 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READONLY);
16
17 end if;
18
19 fp :=UTL_FILE.FOPEN("BFILE_DIR","output.dat","w");
20
21 LOOP
22
23 BEGIN
24
25 DBMS_LOB.READ (clob_pointer,Amount,Position,v_Buf);
26
27 UTL_FILE.PUT_LINE(fp,v_Buf,TRUE);
28
29 Position :=Position +Amount;
30
31 EXCEPTION
32
33 WHEN NO_DATA_FOUND THEN
34
35 EXIT;
36
37 END;
38
39 END LOOP;
40
41 UTL_FILE.FCLOSE(fp);
42
43 DBMS_LOB.CLOSE (clob_pointer);
44 END;
45 /

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

== Show file content==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE PROCEDURE show_file (loc IN VARCHAR2,file IN VARCHAR2)
2 IS
3 fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, "R");
4 line VARCHAR2(2000);
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE (file);
7 LOOP
8 UTL_FILE.GET_LINE (fid, line);
9 DBMS_OUTPUT.PUT_LINE (line);
10 END LOOP;
11 EXCEPTION
12 WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
13 END;
14 /

SQL></source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия