프로토타입

PROCEDURE FCLOSE
(
    file IN OUT FILE_TYPE
);

파라미터

예외 상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/HOME/TIBERO/PATH';

DECLARE
  fname   VARCHAR2(1024);
BEGIN
    file := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'r');
    UTL_FILE.FCLOSE(file);
END;
/

프로토타입

PROCEDURE FCLOSE_ALL;

예외 상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/HOME/TIBERO/PATH';

DECLARE
    outfile   UTL_FILE.FILE_TYPE;
    appfile   UTL_FILE.FILE_TYPE;
    path       VARCHAR(10);
BEGIN
    path := 'USER_PATH';

    outfile := UTL_FILE.FOPEN(path, 'output_file.txt', 'w');
    appfile := UTL_FILE.FOPEN(path, 'append_file.txt', 'w');

    UTL_FILE.FCLOSE_ALL;
END;
/

프로토타입

PROCEDURE FCOPY
( 
    location   IN VARCHAR2,
    filename   IN VARCHAR2, 
    dest_dir   IN VARCHAR2,
    dest_file  IN VARCHAR2,
    start_line IN BINARY_INTEGER DEFAULT 1, 
    end_line   IN BINARY_INTEGER DEFAULT NULL
);

파라미터

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/HOME/TIBERO/PATH';
         
BEGIN
    UTL_FILE.FCOPY('USER_PATH', 'MYFILE.DAT', 'USER_PATH', 'MTFILE.BAK');
END;
/

프로토타입

PROCEDURE FFLUSH
(
    file IN FILE_TYPE
);

파라미터

예외 상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';
DECLARE
  fhandle  UTL_FILE.FILE_TYPE;
  buffer   VARCHAR2(32767);
  path     VARCHAR2(1024);
  fname    VARCHAR2(1024);
BEGIN
  path     := 'USER_PATH';
  fname    :=  'MYFILE.TXT';
  fhandle  := UTL_FILE.FOPEN(path, fname, 'w');
  buffer   := 'This is the message for output file';
  for i in 1..10 loop
    UTL_FILE.PUT(fhandle, buffer);
  end loop;
  UTL_FILE.NEW_LINE(fhandle);
  UTL_FILE.FFLUSH(fhandle);
  UTL_FILE.FCLOSE(fhandle);
END;
/

프로토타입

PROCEDURE FGETATTR 
(
    location IN VARCHAR2,
    filename IN VARCHAR2,
    fexists  OUT BOOLEAN,
    file_length OUT NUMBER,
    blocksize OUT BINARY_INTEGER
);

파라미터

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
  info_exists  BOOLEAN;
  info_flen    NUMBER;
  info_bsz     BINARY_INTEGER;
  etc_path     VARCHAR2(1024);
  fname        VARCHAR2(1024);
BEGIN
  etc_path := 'USER_PATH';
  fname    := 'hostname';
  UTL_FILE.FGETATTR(etc_path, fname, info_exists, info_flen, info_bsz);

  if info_exists = TRUE then
    DBMS_OUTPUT.PUT_LINE('file name         :' || fname);
    DBMS_OUTPUT.PUT_LINE('file size (bytes) :' || TO_CHAR(info_flen));
    DBMS_OUTPUT.PUT_LINE('block size(bytes) :' || TO_CHAR(info_bsz));
  end if;
END;
/

프로토타입

PROCEDURE FREMOVE
(
    location IN VARCHAR2,
    filename IN VARCHAR2
);

파라미터

예외 상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

BEGIN
    UTL_FILE.FREMOVE('USER_PATH', 'MYFILE.BAK');
END;
/

프로토타입

PROCEDURE FRENAME 
(
    location   IN VARCHAR2,
    filename   IN VARCHAR2, 
    dest_dir   IN VARCHAR2, 
    dest_file  IN VARVCHAR2,
    overwrite  IN BOOLEAN DEFAULT FALSE
);

파라미터

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';
BEGIN
    UTL_FILE.FRENAME('USER_PATH', 'MTFILE.BAK', 'USER_PATH', 'MYFILE.DAT');
END;
/

프로토타입

PROCEDURE FSEEK 
(
    fid IN UTL_FILE.FILE_TYPE,
    absolute_offset IN PLS_INTEGER DEFAULT NULL,
    relative_offset IN PLS_INTEGER DEFAULT NULL
);

파라미터

예외 상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
  infile          UTL_FILE.FILE_TYPE;
  absolute_offset PLS_INTEGER:= 10;
  scan_first      RAW(32767);
  scan_second     RAW(32767);
  len CONSTANT    PLS_INTEGER := 32767;
BEGIN
  infile := UTL_FILE.FOPEN('USER_PATH','codd.bcnf', 'r');

  UTL_FILE.FSEEK(infile, absolute_offset, -5);
  UTL_FILE.GET_RAW(infile, scan_first, 1);
  UTL_FILE.FSEEK(infile, 5);
  UTL_FILE.GET_RAW(infile, scan_second, 1);

  if  scan_first != scan_second then
    DBMS_OUTPUT.PUT_LINE('ralative_offset ignored!');
  end if;

  UTL_FILE.FCLOSE(infile);
END;
/

프로토타입

PROCEDURE GET_LINE 
(
    file   IN  FILE_TYPE,
    buffer OUT VARCHAR2,
    len    IN  PLS_INTEGER DEFAULT NULL
);

파라미터

예외상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE 
    infile       UTL_FILE.FILE_TYPE;
    length       CONSTANT PLS_INTEGER := 1024;
    read_buffer  VARCHAR2(1024) := NULL;
BEGIN
    infile := UTL_FILE.FOPEN('USER_PATH','hosts', 'r');
    loop
        UTL_FILE.GET_LINE(infile, read_buffer, length);
        DBMS_OUTPUT.PUT_LINE(read_buffer);
        if read_buffer is NULL then
             DBMS_OUTPUT.PUT_LINE(read_buffer);
        else
            exit;
        end if;
    end loop;

    UTL_FILE.FCLOSE(infile);
END;
/

프로토타입

PROCEDURE GET_RAW
(
    fid  IN  UTL_FILE.FILE_TYPE,
    r    OUT NOCOPY RAW,
    len  IN  PLS_INTEGER DEFAULT NULL
);

파라미터

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE 
  infile       UTL_FILE.FILE_TYPE;
  length       CONSTANT PLS_INTEGER := 32;
  read_buffer  RAW(32767);
BEGIN
  infile := UTL_FILE.FOPEN('USER_PATH','hosts', 'r');
  UTL_FILE.GET_RAW(infile, read_buffer, length);
  UTL_FILE.FCLOSE(infile);
END;
/

프로토타입

PROCEDURE NEW_LINE
(
    file  IN FILE_TYPE,
    lines IN NATURAL DEFAULT 1 
);

파라미터

예외 상황

프로토타입

PROCEDURE PUT
(
    file IN FILE_TYPE,
    buffer IN VARCHAR2
);

파라미터

예외 상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';
DECLARE
  outfile  UTL_FILE.FILE_TYPE;
  buf      VARCHAR(1024) := NULL;
BEGIN
  outfile := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'w');

  for i in 1..10 loop
    buf := buf || '0123456789';
  end loop;

  UTL_FILE.PUT( outfile, buf );
  UTL_FILE.FCLOSE(outfile);
END;
/

프로토타입

PROCUDURE PUTF 
(
    file   IN FILE_TYPE,
    format IN VARCHAR2,
    arg1   IN VARCHAR2 DEFAULT NULL,
    arg2   IN VARCHAR2 DEFAULT NULL,
    arg3   IN VARCHAR2 DEFAULT NULL,
    arg4   IN VARCHAR2 DEFAULT NULL,
    arg5   IN VARCHAR2 DEFAULT NULL
);

파라미터

예외 상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    fname    VARCHAR2(2048);
    path     VARCHAR2(2048);
    outfile  UTL_FILE.FILE_TYPE;
BEGIN
    fname := 'MYFILE.DAT';
    path  := 'USER_PATH';
    outfile := UTL_FILE.FOPEN(path, fnamet /home/posung/path/MYFILE.DAT!cat 
               /home/posung/path/MYFILE.DAT, 'w');
    UTL_FILE.PUTF( outfile, '%s %s formated file output example\n', path, fname);
    UTL_FILE.FCLOSE(outfile);
END;
/

프로토타입

PROCEDURE PUT_RAW
(
    fid       IN UTL_FILE.FILE_TYPE, 
    r         IN RAW, 
    autoflush IN BOOLEAN DEFAULT FALSE 
);

파라미터

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    outfile  UTL_FILE.FILE_TYPE;
    buf RAW(8) := HEXTORAW('6161616100616100');
BEGIN
    outfile := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'wb');

    UTL_FILE.PUT_RAW(outfile, buf);

    UTL_FILE.FCLOSE(outfile);
END;
/

프로토타입

PROCEDURE PUT_LINE
(
    file      IN FILE_TYPE,
    buffer    IN VARCHAR2,
    autoflush IN BOOLEAN DEFAULT FALSE
);

파라미터

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    outfile  UTL_FILE.FILE_TYPE;
    out_buf  VARCHAR2(1024);
BEGIN
    outfile := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'w');

    for i in 1..10 loop
        UTL_FILE.PUT_LINE(outfile,  i || ' put_line is complete', TRUE);
    end loop;

    UTL_FILE.FCLOSE(outfile);
END;
/

프로토타입

FUNCTION FGETPOS
(
    fid  IN FILE_TYPE
)
return PLS_INTEGER;

파라미터

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
  file   UTL_FILE.FILE_TYPE;
  fpos   PLS_INTEGER := 0;
  path   VARCHAR2(1024);
  fname  VARCHAR2(1024);
BEGIN
    file := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'r');
    UTL_FILE.FSEEK(file, 10, 1);
    fpos := UTL_FILE.FGETPOS(file);
    DBMS_OUTPUT.PUT_LINE('Offest is ' || TO_CHAR(fpos, 'S9999999999'));

    UTL_FILE.FCLOSE(file);
END;
/

프로토타입

FUNCTION FOPEN
(
    location     IN VARCHAR2,
    filename     IN VARCHAR2,
    open_mode    IN VARCHAR2, 
    max_linesize IN BINARY_INTEGER DEFAULT 1024
) 
RETURN FILE_TYPE;

파라미터

예외 상황

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    infile    UTL_FILE.FILE_TYPE;
    outfile   UTL_FILE.FILE_TYPE;
    appfile   UTL_FILE.FILE_TYPE;
    max_lsz   CONSTANT BINARY_INTEGER := 4096;
    buffer    VARCHAR2(1024);
    data1     VARCHAR2(1024);
    data2     VARCHAR2(1024);
    path      VARCHAR2(1024);
BEGIN
    path := 'USER_PATH';

    outfile := UTL_FILE.FOPEN(path, 'output_file.txt', 'w',  max_lsz);
    appfile := UTL_FILE.FOPEN(path, 'append_file.txt', 'w',  max_lsz);

    data1 := '0123456789';
    data2 := 'abcdefghij';
    for  i in 1..10 loop
        UTL_FILE.PUT_LINE(outfile, data1);
        UTL_FILE.PUT_LINE(outfile, data2);
    end loop;

    UTL_FILE.FCLOSE(outfile);
    UTL_FILE.FCLOSE(appfile);

    infile := UTL_FILE.FOPEN(path, 'output_file.txt', 'r',  max_lsz);
    UTL_FILE.GET_LINE(infile, buffer);
    DBMS_OUTPUT.PUT_LINE(buffer);
    UTL_FILE.FCLOSE(infile);
    appfile := UTL_FILE.FOPEN( path, 'append_file.txt', 'a',  max_lsz );
    UTL_FILE.PUT_LINE(appfile, 'appended');
    UTL_FILE.FCLOSE(appfile);
END;
/

프로토타입

PROCEDURE IS_OPEN
(
    file IN  FILE_TYPE
) 
RETURN BOOLEAN;

파라미터

예제

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    openfile    UTL_FILE.FILE_TYPE;
    open_flag   BOOLEAN;
    status      VARCHAR(10);
    fname       VARCHAR(10);
BEGIN
    fname := 'MYFILE.DAT';
    openfile := UTL_FILE.FOPEN('USER_PATH', fname, 'r');
    if UTL_FILE.IS_OPEN(openfile) then
        status := 'opened';
    else
        status := 'not opened';
    end if;

    DBMS_OUTPUT.PUT_LINE(fname || ' file status : ' || status);
    UTL_FILE.FCLOSE(openfile);
    dbms_output.put_line(fname || 'is closed');
    if utl_file.IS_OPEN(openfile) then
        status := 'opened';
    else
        status := 'not opened';
    end if;
    DBMS_OUTPUT.PUT_LINE(fname || ' file status : ' || status);
END;
/