oracle max_linesize null,Oracle UTL_FILE 用法例子

FCLOSE

Close A File Opened By UTL_FILE

utl_file.fclose()

see FOPEN demo

FCLOSE_ALL

Close All Files Opened By UTL_FILE

utl_file.fclose_all;

set serveroutput on

DECLARE

vInHandle utl_file.file_type;

vOutHandle utl_file.file_type;

BEGIN

vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');

vOutHandle := utl_file.fopen('ORALOAD', 'out.txt', 'W');

IF utl_file.is_open(vInHandle) THEN

utl_file.fclose_all;

dbms_output.put_line('Closed All');

END IF;

END fopen;

/

FCOPY

Copies a contiguous portion of a file to a newly created file

utl_file.fcopy (

location   IN VARCHAR2,

filename   IN VARCHAR2,

dest_dir   IN VARCHAR2,

dest_file IN VARCHAR2,

start_line IN PLS_INTEGER DEFAULT 1,

end_line   IN PLS_INTEGER DEFAULT NULL);

-- demo requires creating directory CTEMP ... see link at bottom of page

BEGIN

utl_file.fcopy('ORALOAD', 'dump.txt', 'ORALOAD', 'didit.txt');

END;

/

FFLUSH

Physically writes pending data to the file identified by the file handle

utl_file.fflush ();

See Write demo

FGETATTR

Reads and returns the attributes of a disk file

utl_file.fgetattr(

location    IN VARCHAR2,

filename    IN VARCHAR2,

exists      OUT BOOLEAN,

file_length OUT NUMBER,

blocksize   OUT NUMBER);

set serveroutput on

DECLARE

ex    BOOLEAN;

flen NUMBER;

bsize NUMBER;

BEGIN

utl_file.fgetattr('ORALOAD', 'test.txt', ex, flen, bsize);

IF ex THEN

dbms_output.put_line('File Exists');

ELSE

dbms_output.put_line('File Does Not Exist');

END IF;

dbms_output.put_line('File Length: ' || TO_CHAR(flen));

dbms_output.put_line('Block Size: ' || TO_CHAR(bsize));

END fgetattr;

/

FGETPOS

Returns the current relative offset position within a file, in bytes

utl_file.fgetpos(fileid IN file_type) RETURN PLS_INTEGER;

See Read-Write demo

FOPEN

Open A File For Read Operations

utl_file.fopen(

,

,

,

)

RETURN

DECLARE

vInHandle utl_file.file_type;

vNewLine VARCHAR2(250);

BEGIN

vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');

LOOP

BEGIN

utl_file.get_line(vInHandle, vNewLine);

dbms_output.put_line(vNewLine);

EXCEPTION

WHEN OTHERS THEN

EXIT;

END;

END LOOP;

utl_file.fclose(vInHandle);

END fopen;

/

Open A File For Write Operations

:= utl_file.fopen(

FOPEN_NCHAR

Open a file to read or write a text file in Unicode instead of in the database charset

FREMOVE

Delete An Operating System File

utl_file.fremove (location IN VARCHAR2, filename IN VARCHAR2);

BEGIN

utl_file.fremove('ORALOAD', 'dump.txt');

END fremove;

/

FRENAME

Rename An Operating System File

utl_file.frename (

location IN VARCHAR2,

filename IN VARCHAR2,

dest_dir IN VARCHAR2,

dest_file IN VARCHAR2,

overwrite IN BOOLEAN DEFAULT FALSE);

BEGIN

utl_file.frename('ORALOAD','test.txt','ORALOAD','x.txt',TRUE);

END frename;

/

FSEEK

Adjusts the file pointer forward or backward within the file by the number of bytes specified

utl_file.fseek (

fid             IN utl_file.file_type,

absolute_offset IN PL_INTEGER DEFAULT NULL,

relative_offset IN PLS_INTEGER DEFAULT NULL);

See Read-Write demo

GETLINE

Read a Line from a file

utl_file.getline (

file     IN FILE_TYPE,

buffer   OUT VARCHAR2,

linesize IN NUMBER,

len      IN PLS_INTEGER DEFAULT NULL);

See Read demos

GETLINE_NCHAR

Same as GETLINE except can be used to read Unicode rather than the database's character set

GET_RAW

Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read

utl_file.get_raw (

fid IN utl_file.file_type,

r   OUT NOCOPY RAW,

len IN PLS_INTEGER DEFAULT NULL);

See UTL_MAIL demo

IS_OPEN

Returns True If A File Handle Is Open: Otherwise False

utl_file.is_open (file IN FILE_TYPE) RETURN BOOLEAN;

See FCLOSE_ALL Demo

NEW_LINE

Writes one or more operating system-specific line terminators to a file

utl_file.NEW_LINE (file IN FILE_TYPE, lines IN NATURAL := 1);

See Read Demo

PUT

Writes a string to a file

utl_file.put(

file   IN FILE_TYPE,

buffer IN VARCHAR2);

See Write demo

PUTF

A PUT procedure with formatting

utl_file.putf(

file   IN FILE_TYPE,

format IN VARCHAR2,

[arg1 IN VARCHAR2 DEFAULT NULL,

. . .

arg5   IN VARCHAR2 DEFAULT NULL]);

See Write demo

PUT_LINE

Writes a line to a file. Appends an operating system-specific line terminator

utl_file.put_line(

file      IN FILE_TYPE,

buffer    IN VARCHAR2,

autoflush IN BOOLEAN DEFAULT FALSE);

See Read-Write demo

PUT_NCHAR

Writes a Unicode string to a file

PUT_RAW

Accepts as input a RAW data value and writes the value to the output buffer

utl_file.PUT_RAW (

fid       IN utl_file.file_type,

r         IN RAW,

autoflush IN BOOLEAN DEFAULT FALSE);

See extract_blob Demo

PUT_LINE_NCHAR

Writes a Unicode line to a file

PUTF_NCHAR

Writes a Unicode string to a file