UTL_FILE
--------
With the UTL_FILE package, your PL/SQL programs can read and write operating system text files.
UTL_FILE provides a restricted version of operating system stream file I/O.
PLSQL PROGRAMS IN UTL_FILE PACKAGE
---------------------------------
FOPEN Function---Opens a file for input or output.
IS_OPEN Function--- Determines if a file handle refers to an open file.
FCLOSE Procedure---Closes a file.
FCLOSE_ALL Procedure---Closes all open file handles.
GET_LINE Procedure----Reads text from an open file.
PUT Procedure---Writes a string to a file.
PUT_LINE Procedure--- Writes a line to a file. This appends an operating system-specific line terminator.
FREMOVE Function-- Deletes a disk file, assuming that you have sufficient privileges.
FCOPY Function--- Copies a contiguous portion of a file to a newly created file.
FGETATTR Procedure--- Reads and returns the attributes of a disk file.
FRENAME Function--- Renames an existing file to a new name, similar to the Unix mv function.
Syntax
--------
UTL_FILE.FREMOVE (location IN VARCHAR2,filename IN VARCHAR2);
Syntax
------
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);
Syntax
------
UTL_FILE.FRENAME (
location IN VARCHAR2,
filename IN VARCHAR2,
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);
--------------------------------------------
EXAMPLES
--------------------------------------------
CREATE DIRECTORY dir_test AS 'D:\';
grant read,write on DIRECTORY dir_test TO user;
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('DIR_TEST', 'test_file.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'WRITING INTO THE FILE\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/
----------------------------------------------------
create or replace
procedure proc_dump_EMP
as
v_file UTL_FILE.FILE_TYPE;
v_EMP VARCHAR2(128) := 'EMPDATA.csv';
BEGIN
v_file := UTL_FILE.FOPEN('DIR_TEST',v_EMP,'W',32767);
FOR i IN (Select
'"'||EMPNO|| '"'||','||
'"'||ENAME|| '"'||','||
'"'||DEPTNO|| '"'||','||
'"'||SAL|| '"'||','||
'"'||JOB|| '"' as datacsv
from EMP )
LOOP
UTL_FILE.PUT_LINE(v_file, i.datacsv);
END LOOP;
UTL_FILE.FCLOSE(v_file);
END;
------------------------------------------------
create table test (
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));
create or replace
PROCEDURE read_demo(file_name VARCHAR2) IS
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
BEGIN
vSFile := utl_file.fopen('DIR_TEST', file_name,'r');
IF utl_file.is_open(vSFile) THEN
LOOP
BEGIN
utl_file.get_line(vSFile, vNewLine);
IF vNewLine IS NULL THEN
EXIT;
END IF;
INSERT INTO test(fld1, fld2)
VALUES(vNewLine, file_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
utl_file.fclose(vSFile);
utl_file.frename('DIR_TEST', 'test.txt', 'DIR_TEST', 'x.txt', TRUE);
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END read_demo;
----------------------------------------------------------
Exceptions
---------------------------------------------------------
INVALID_PATH ---> File location is invalid.
INVALID_MODE ---> The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE ---> File handle is invalid.
INVALID_OPERATION---> File could not be opened or operated on as requested.
READ_ERROR---> Operating system error occurred during the read operation.
WRITE_ERROR--->Operating system error occurred during the write operation.
INTERNAL_ERROR--->Unspecified PL/SQL error
CHARSETMISMATCH--->A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions
such as PUTF or GET_LINE.
FILE_OPEN--->The requested operation failed because the file is open.
INVALID_MAXLINESIZE--->The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME---> The filename parameter is invalid.
ACCESS_DENIED---> Permission to access to the file location is denied.
INVALID_OFFSET--->The ABSOLUTE_OFFSET parameter for FSEEK() is invalid;
it should be greater than 0 and less than the total number of bytes in the file.
DELETE_FAILED---> The requested file delete operation failed.
RENAME_FAILED---> The requested file rename operation failed.
Ur's
AmarAlam
0 comments:
Post a Comment