askvenki

 Profile Answers by askvenki 

• Jul 22nd, 2007

 

utlfile is used to transfer data from database tables to flat file
 utl_file.open
           .put_line

g_sidhu

 Profile 

• Feb 5th, 2008

 

With the Oracle-supplied UTL_FILE package, you can read from and write to operating system files. It Provides security for directories on the server through the init.ora file. UTL_FILE Procedures and Functions:

• Function FOPEN

• Function IS_OPEN

• Procedure GET_LINE

• Procedure PUT, PUT_LINE, PUTF

• Procedure NEW_LINE

• Procedure FFLUSH

• Procedure FCLOSE, FCLOSE_ALL

samareshp

 Profile Answers by samareshp 

• Aug 3rd, 2009

 

UTL_FILE

The Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is on the computer where the client is running.

Procedures/Functions present in UTL_FILE:

fclose
procedure fclose (
file in out record
);

fclose_all

procedure fclose_all (
);


fcopy
procedure fcopy (
src_location in varchar2,
src_filename in varchar2,
dest_location in varchar2,
dest_filename in varchar2,
start_line in binary_integer default,
end_line in binary_integer default
);

fflush
procedure fflush (
file in record
);


fgetattr
procedure fgetattr (
location in varchar2,
filename in varchar2,
fexists out boolean,
file_length out number,
block_size out binary_integer
);

fgetpos
function fgetpos returns binary_integer
(
file in record
);


fopen
function fopen returns record
(
location in varchar2,
filename in varchar2,
open_mode in varchar2,
max_linesize in binary_integer default
);

Opens a file. Takes four parameters: location, filename, open_mode and max_linesize
location: must be either an (existing) directory on the server AND be in the utl_file_dir paramter, or a directory.
open_mode: 'W' for writing access or 'R' for reading access. Additionally, a 'B' can be specified for binary access.

fopen_nchar
function fopen_nchar returns record
(
location in varchar2,
filename in varchar2,
open_mode in varchar2,
max_linesize in binary_integer default
);


fremove
procedure fremove (
location in varchar2,
filename in varchar2
);


frename
procedure frename (
src_location in varchar2,
src_filename in varchar2,
dest_location in varchar2,
dest_filename in varchar2,
overwrite in boolean default
);


fseek
procedure fseek (
file in out record,
absolute_offset in binary_integer default,
relative_offset in binary_integer default
);


get_line
procedure get_line (
file in record,
buffer out varchar2,
len in binary_integer default
);

Reads a line from the opened file.
The maximum line length that can be read is 32K in 9i.

get_line_nchar
procedure get_line_nchar (
file in record,
buffer out nvarchar2,
len in binary_integer default
);


get_raw
procedure get_raw (
file in record,
buffer out raw,
len in binary_integer default
);


is_open
function is_open returns boolean
(
file in record
);


new_line
procedure new_line (
file in record,
lines in binary_integer default
);


put
procedure put (
file in record,
buffer in varchar2
);


putf
procedure putf (
file in record,
format in varchar2,
arg1 in varchar2 default,
arg2 in varchar2 default,
arg3 in varchar2 default,
arg4 in varchar2 default,
arg5 in varchar2 default
);


putf_nchar
procedure putf_nchar (
file in record,
format in nvarchar2,
arg1 in nvarchar2 default,
arg2 in nvarchar2 default,
arg3 in nvarchar2 default,
arg4 in nvarchar2 default,
arg5 in nvarchar2 default
);


put_line
procedure put_line (
file in record,
buffer in varchar2,
autoflush in boolean default
);

Writes a line into the opened file.
If a line was already written, it starts the line with CR/LF. This implies that the file, when being written into, does not end with CR/LF.
The maximum line length that can be written is 32K in 9i.

put_line_nchar
procedure put_line_nchar (
file in record,
buffer in nvarchar2
);


put_nchar
procedure put_nchar (
file in record,
buffer in nvarchar2
);


put_raw
procedure put_raw (
file in record,
buffer in raw,
autoflush in boolean default
);