|
·
Article |
UTL_FILE Enhancement |
|
·
Database Version |
Oracle
Version 9.2.0.2 |
|
·
Article Revision Date |
|
|
·
Web |
www.SamOraTech.com |
|
·
Author |
Sameer Wadhwa |
Here are simple examples to
understand the UTL_FILE enhancement in Oracle 9i Release 2
Examples discussed in this
article are
·
COPYING OS FILE ON DIFFERENT LOCATION
·
RENAME OS FILE
· GETTING ATTRIBUTE OF A OS FILE
To understand, please make the following setup,
which is on WIN2K machine. You can make the similar set for Unix.
Create a director LOC1 and LOC2 under C:\TEMP
U:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Feb
19
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
NAME TYPE VALUE
------------------------------------ -----------
---------------------
utl_file_dir string
Now tell oracle about your directories as follows (Connect as sys)
create or replace
directory dbdir as 'C:\TEMP\LOC1\';
Grant read on directory dbdir
to Scott;
Create or replace directory DBDIR2 as 'C:\TEMP\LOC2\';
Grant read on directory DBDIR2 to Scott;
SQL> select * from dba_directories;
OWNER
DIRECTORY_NAME
DIRECTORY_PATH
---------- ---------------
---------------------------------
SYS
DBDIR C:\TEMP\LOC1\
SYS DBDIR2 C:\TEMP\LOC2\
For sample test let us create a file NEWFILE.TXT
through UTL_FILE
Create or Replace procedure UTLTEST as
f1 utl_file.file_type;
begin
-- Open a
file in write mode
f1 := utl_file.fopen('DBDIR','NEWFILE.TXT','W');
-- Write
a line to a file
utl_file.put_line(f1,'1.
This is a test of UTL_FILE packages');
utl_file.put_line(f1,'2.
Oracle has added a new procedures in the package');
utl_file.put_line(f1,'3.
We will see all the procedure one by one');
-- Close a file
utl_file.fclose(f1);
end;
/
show error;
SQL> execute utltest;
The execution of the above procedure will create a
file in C:\TEMP\LOC1 with a name NEWFILE.TXT
TEST FOR COPYING A FILE ON
SAME LOCATION.
PROCEDURE FCOPY
Argument Name
Type In/Out
Default?
------------------------------
----------------------- ------ --------
SRC_LOCATION VARCHAR2 IN
SRC_FILENAME VARCHAR2 IN
DEST_LOCATION VARCHAR2 IN
DEST_FILENAME VARCHAR2 IN
START_LINE BINARY_INTEGER IN
DEFAULT
END_LINE BINARY_INTEGER IN
DEFAULT
Create or Replace procedure UTLTESTCOPY as
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('DBDIR','NEWFILE.TXT','R');
UTL_FILE.FCOPY(SRC_LOCATION=>'DBDIR',
SRC_FILENAME=>'NEWFILE.TXT',
DEST_LOCATION=>'DBDIR',
DEST_FILENAME=>'NEWFILECOPY.TXT');
UTL_FILE.FCLOSE(F1);
END;
/
SHOW ERRORS
Sql> Execute UTLTESTCOPY;
You will see two files now NEWFILE.TXT as well as
NEWFILECOPY.TXT
By opening the NEWFILE.TXT is open in 'W' will make
it blank.
TEST FOR COPY A FILE ON DIFFERENT LOCATION.
Create or Replace procedure UtlTestCopyDIffLoc
as
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('DBDIR','NEWFILE.TXT','R');
UTL_FILE.FCOPY(SRC_LOCATION=>'DBDIR',
SRC_FILENAME=>'NEWFILE.TXT',
DEST_LOCATION=>'DBDIR2',
DEST_FILENAME=>'NEWFILECOPY.TXT',
START_LINE=>2,
END_LINE=>4);
UTL_FILE.FCLOSE(F1);
END;
/
SHOW ERRORS
You will see a file NEWFILE.TXT in C:\TEMP\LOC1 and
COPYNEWFILE.TXT in C:\TEMP\LOC2.
SQL> create or replace directory networkdir as '\\ORCL8i\TEMP';
Directory created.
SQL> grant read on directory networkdir
to scott;
Grant succeeded.
Create or Replace procedure UTLTESTCOPY as
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('DBDIR','NEWFILE.TXT','R');
UTL_FILE.FCOPY(SRC_LOCATION=>'DBDIR',
SRC_FILENAME=>'NEWFILE.TXT',
DEST_LOCATION=>'NETWORKDIR',
DEST_FILENAME=>'NEWFILECOPY.TXT');
UTL_FILE.FCLOSE(F1);
END;
/
SHOW ERRORS
To make this work you have to controlpanel->Adminstrative tools->Services->OracleServiceXXXX
Right click on service and click properties ->
Press
password on
which remote machine is accesible. Stop and start the
service after that.
PROCEDURE FRENAME
Argument Name
Type In/Out Default?
------------------------------
----------------------- ------ --------
SRC_LOCATION VARCHAR2 IN
SRC_FILENAME VARCHAR2 IN
DEST_LOCATION VARCHAR2 IN
DEST_FILENAME VARCHAR2 IN
OVERWRITE BOOLEAN IN DEFAULT
Create or Replace procedure UTLTESTRENAME as
begin
UTL_FILE.FRENAME(SRC_LOCATION=>'DBDIR',
SRC_FILENAME=>'NEWFILE.TXT',
DEST_LOCATION=>'DBDIR2',
DEST_FILENAME=>'NEWFILE4.TXT',
OVERWRITE=>FALSE);
END;
/
SHOW ERRORS
SQL> execute UTLTESTRENAME;
It will move the file NEWFILE.TXT from C:\TEMP\LOC1
directory and C:\TEMP\LOC2 directory and rename it as NEWFILE4.TXT
It copies and rename a
files
If the file is not there ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 18
If the file is in sharing mode you can not rename
it.
You will get ORA-29292: file
rename operation failed or if the overwrite is false than the
destination location should
not have the file with the
same name.
PROCEDURE FGETATTR
Argument Name
Type In/Out
Default?
------------------------------
----------------------- ------ --------
LOCATION VARCHAR2 IN
FILENAME VARCHAR2 IN
FEXISTS BOOLEAN OUT
FILE_LENGTH NUMBER OUT
BLOCK_SIZE BINARY_INTEGER OUT
Create or Replace procedure UTLTESTattr
as
Present boolean;
FlENGTH number;
bsize pls_integer;
begin
UTL_FILE.FGETATTR(LOCATION=>'DBDIR2',
FILENAME=>'NEWFILE4.TXT',
FEXISTS=>PRESENT,
FILE_LENGTH=>FLENGTH,
BLOCK_SIZE=>BSize);
If present then
dbms_output.put_line('FILE FOUND');
dbms_output.put_line('HAVING LENGTH ='||FLENGTH);
dbms_output.put_line('HAVING BLOCK SIZE ='||BSIZE);
end if;
END;
/
SHOW ERRORS
FILE FOUND
HAVING LENGTH =138
HAVING BLOCK SIZE =0
PL/SQL procedure successfully completed.
LENGTH is the number of chars with spaces found in
the file NEWFILE4.TXT
file_open
charsetmismatch
invalid_path
invalid_mode
invalid_filehandle
invalid_operation
read_error
write_error
internal_error
invalid_maxlinesize
invalid_filename
access_denied
invalid_offset
delete_failed
rename_failed
Hope this will help
Cheers !!!!
Sam…
Copyright © Sameer wadhwa Oracle Techniques All right reserved