Overview  Deprecated  Index  Generator  
File and operating system interaction with SQL and PLSQL
SUMMARY: FIELD | TYPE | METHOD DETAIL: FIELD | TYPE | METHOD


Package FILE_TYPE

FILE_TYPE is an object type and represents a file handle. A file handle points to an existent or non-existent file in the database server's filesystem. All file operations are done as the operating user under which the Oracle executable runs. FILE_TYPE has the following attributes:

file_path VARCHAR2(4000) The full path to the file
file_name VARCHAR2(4000) The simple name of the file
file_size NUMBER The size of the file in bytes
is_dir CHAR(1) 'Y' when the file is a directory, 'N' otherwise
is_writeable CHAR(1) 'Y' when the file is writeable, 'N' otherwise
is_readable CHAR(1) 'Y' when the file is readable, 'N' otherwise
file_exists CHAR(1) 'Y' when the handle points a an existing file; 'N' otherwise

A file handle can point to existing files as well as to nonexisting files. The last attribute file_exists denotes whether the file handle points to an existing file. If not all other attributes except the file_path and the file_name are NULL.

Example I: Obtaining a file handle for the /tmp directory (see documentation for FILE_PKG) 

 SQL> select file_pkg.get_file('/tmp') from dual;
 

  FILE_PKG.GET_FILE('/TMP')(FILE_PATH, FILE_NAME, FILE_SIZE, LAST_MODIFIED, IS_DIR
  --------------------------------------------------------------------------------
  FILE_TYPE('/tmp', 'tmp', 77824, '26.03.08', 'Y', 'Y', 'Y', 'Y')
Example II: Obtaining a file handle for the (nonexisting) /tmp1 directory (see documentation for FILE_PKG) 
 SQL> select file_pkg.get_file('/tmp1') from dual;
 

  FILE_PKG.GET_FILE('/TMP1')(FILE_PATH, FILE_NAME, FILE_SIZE, LAST_MODIFIED, IS_DI
  --------------------------------------------------------------------------------
  FILE_TYPE('/tmp1', NULL, NULL, NULL, NULL, NULL, NULL, 'N')

The database user needs appropriate java privileges in order to operate on operating system files. The DBA must provide proper privileges using the built-in package DBMS_JAVA. The following call for example grants the privileges to read the directory /tmp and its contents to the database user SCOTT: 
begin
   dbms_java.grant_permission(
     grantee =>           'SCOTT',
     permission_type =>   'SYS:java.io.FilePermission',
     permission_name =>   '/tmp/*',
     permission_action => 'read'
   );
end;
To grant read and write access to the whole file system, issue ... 
begin
   dbms_java.grant_permission(
     grantee =>           'SCOTT',
     permission_type =>   'SYS:java.io.FilePermission',
     permission_name =>   '/-',
     permission_action => 'read,write'
   );
end;

Field Summary
 char(1)file_exists
           Flag whether this file exists - if a file does not exist, all attributes except this and the full pathname are set to SQL NULL.
 varchar2(4000)file_name
           Filename
 varchar2(4000)file_path
           Full absolute path to this file
 numberfile_size
           File size in bytes
 char(1)is_dir
           Flag whether this file is a directory ("Y"); "N" otherwise
 char(1)is_readable
           Flag whether this file is readable
 char(1)is_writeable
           Flag whether this file is writeable
 datelast_modified
           LastModified date of this file

Method Summary
 numberappend_to_file(p_content clob)
           appends to a file
 numberappend_to_file(p_content blob)
           appends to a file
 numberappend_to_file(p_content varchar2)
           appends to a file
 close_stream()
           Closes the "Input Stream".
 file_typecopy(p_target_file file_type)
           copies the file.
 FILE_TYPEcreate_dir(p_dirname varchar2)
           Creates a new empty directory.
 file_typecreate_file(p_filename varchar2)
           Creates a new empty file.
 file_typedelete_file()
           deletes the file or directory.
 file_typedelete_recursive()
           deletes the file or directory.
 bfileget_bfile(p_directory_name varchar2)
           returns a BFILE object, which represents this file handle.
 blobget_content_as_blob()
           Returns the file contents as a BLOB.
 clobget_content_as_clob(p_charset varchar2)
           Returns the file contents as a CLOB.
 varchar2get_directory()
           Looks up a directory object which represents either, if the file handle is a directory, the handle itself, or the folder in which the file handle resides in.
 file_typeget_file(p_file_path varchar2)
           gets a simple file handle.
 numberget_free_space()
           returns the space (in bytes), which is available on the device containing this file handle.
 file_typeget_parent()
           Returns the parent directory
 numberis_stream_open()
           Checks whether an Input Stream is open on the file.
 file_typemake_all_dirs()
           Creates a new empty directory similar to MAKE_DIR.
 FILE_TYPEmake_dir()
           Creates a new empty directory.
 FILE_TYPEmake_file()
           Creates a new empty file.
 file_typemove(p_target_file file_type)
           performs a "move" / "rename" operation
 open_stream()
           Opens an "Input Stream" on the file.
 numberread_byte()
           Reads a single byte from the file input stream.
 rawread_bytes(p_amount number)
           Reads the given amount of bytes from the file input stream.
 rawread_bytes(p_amount number, p_position number)
           reads the specified amount of bytes at the specified position from the file.
 varchar2read_string(p_amount number, p_charset varchar2)
           Reads the given amount of characters from the file input stream
 varchar2read_string(p_amount number, p_position number, p_charset varchar2)
           reads the specified amount of bytes at the specified position from the file and returns them as a string.
 skip_bytes(p_amount number)
           Skips the given amount of bytes in the file input stream i.e. moves the "pointer" the given amount of bytes forward.
 numberwrite_bytes(p_bytes raw, p_offset number)
           writes bytes at the specified position to the file.
 numberwrite_string(p_string varchar2, p_offset number, p_charset varchar2)
           writes a string at the specified position to the file.
 numberwrite_to_file(p_content clob)
           writes to a file - existing file content is being overwritten
 numberwrite_to_file(p_content blob)
           writes to a file - existing file content is being overwritten

Field Detail

file_path

  public varchar2(4000) file_path
Full absolute path to this file

file_name

  public varchar2(4000) file_name
Filename

file_size

  public number file_size
File size in bytes

last_modified

  public date last_modified
LastModified date of this file

is_dir

  public char(1) is_dir
Flag whether this file is a directory ("Y"); "N" otherwise

is_writeable

  public char(1) is_writeable
Flag whether this file is writeable

is_readable

  public char(1) is_readable
Flag whether this file is readable

file_exists

  public char(1) file_exists
Flag whether this file exists - if a file does not exist, all attributes except this and the full pathname are set to SQL NULL.
Method Detail

move

public file_type move(p_target_file file_type)
performs a "move" / "rename" operation
Parameters:
p_target_file - FILE_TYPE object denoting the new file name and path.
Returns:
FILE_TYPE object pointing to the target file.

delete_file

public file_type delete_file()
deletes the file or directory. If a directory contains other files an error message is thrown. 
SQL> select file_pkg.get_file('/home/oracle/old_file.txt') file_exists from dual;

FILE_EXISTS
-----------------------------------------------------------------------------------------
FILE_TYPE('/home/oracle/old_file.txt', 'old_file.txt', 0, '03.02.10', 'N', 'Y', 'Y', 'Y')

SQL> select file_pkg.get_file('/home/oracle/old_file.txt').delete_file() file_not_exists from duaL;

FILE_NOT_EXISTS
--------------------------------------------------------------------------------
FILE_TYPE('/home/oracle/old_file.txt', NULL, NULL, NULL, NULL, NULL, NULL, 'N')
Returns:
FILE_TYPE object pointing to the location of the deleted file with the FILE_EXISTS attribute being set to "N"

delete_recursive

public file_type delete_recursive()
deletes the file or directory. Directory contents are being deleted recursively.
Returns:
FILE_TYPE object pointing to the location of the deleted directory with the FILE_EXISTS attribute being set to "N"

make_file

public FILE_TYPE make_file()
Creates a new empty file. Must be called on a FILE_TYPE object with the FILE_EXISTS attribute set to "N" 
SQL> select file_pkg.get_file('/home/oracle/new_file.txt') file_not_exists from duaL;

FILE_NOT_EXISTS
--------------------------------------------------------------------------------
FILE_TYPE('/home/oracle/new_file.txt', NULL, NULL, NULL, NULL, NULL, NULL, 'N')

SQL> select file_pkg.get_file('/home/oracle/new_file.txt').make_file() file_exists from dual;

FILE_EXISTS
-----------------------------------------------------------------------------------------
FILE_TYPE('/home/oracle/new_file.txt', 'new_file.txt', 0, '03.02.10', 'N', 'Y', 'Y', 'Y')
Returns:
FILE_TYPE object pointing to the new file

make_dir

public FILE_TYPE make_dir()
Creates a new empty directory. Work similar to the MAKE_FILE function.
Returns:
FILE_TYPE object pointing the new directory

create_dir

public FILE_TYPE create_dir(p_dirname varchar2)
Creates a new empty directory.
Parameters:
p_dirname - absolute path to the new directory
Returns:
FILE_TYPE object pointing to the new directory

create_file

public file_type create_file(p_filename varchar2)
Creates a new empty file.
Parameters:
p_filename - absolute path to the new file
Returns:
FILE_TYPE object pointing to the new file

copy

public file_type copy(p_target_file file_type)
copies the file.
Parameters:
p_target_file - FILE_TYPE object pointing to the target file and path.
Returns:
FILE_TYPE object pointing to the target file.

make_all_dirs

public file_type make_all_dirs()
Creates a new empty directory similar to MAKE_DIR. Creates also all needed parent directories, if not exists.
Returns:
FILE_TYPE object pointing to the new directory.

get_content_as_clob

public clob get_content_as_clob(p_charset varchar2)
Returns the file contents as a CLOB.
Parameters:
p_charset - encoding of the file contents in IANA notation (e.g. "iso-8859-1")
Returns:
File contents as a CLOB

write_to_file

public number write_to_file(p_content clob)
writes to a file - existing file content is being overwritten
Parameters:
p_content - CLOB content to be written to the file.
Returns:
new file size in bytes.

append_to_file

public number append_to_file(p_content clob)
appends to a file
Parameters:
p_content - CLOB content to be appended to the file.
Returns:
new file size in bytes.

write_to_file

public number write_to_file(p_content blob)
writes to a file - existing file content is being overwritten
Parameters:
p_content - BLOB content to be written to the file.
Returns:
new file size in bytes.

append_to_file

public number append_to_file(p_content blob)
appends to a file
Parameters:
p_content - BLOB content to be appended to the file.
Returns:
new file size in bytes.

append_to_file

public number append_to_file(p_content varchar2)
appends to a file
Parameters:
p_content - VARCHAR2 content to be appended to the file.
Returns:
new file size in bytes.

get_content_as_blob

public blob get_content_as_blob()
Returns the file contents as a BLOB.
Returns:
File contents as BLOB

get_parent

public file_type get_parent()
Returns the parent directory
Returns:
FILE_TYPE object pointing to the parent directory.

open_stream

public  open_stream()
Opens an "Input Stream" on the file. The "Input Stream" allows to read the file contents in chunks. The functionality is similar to the Java FileInputStream class.

Version:
0.9

close_stream

public  close_stream()
Closes the "Input Stream".

Version:
0.9

is_stream_open

public number is_stream_open()
Checks whether an Input Stream is open on the file.
Returns:
1 if an input stream is open, 0 otherwise

Version:
0.9

read_bytes

public raw read_bytes(p_amount number)
Reads the given amount of bytes from the file input stream.
Parameters:
p_amount - amount of bytes to read from the stream. Values between 1 and 32767 are allowed.
Returns:
the read bytes as RAW datatype

Version:
0.9

read_string

public varchar2 read_string(p_amount number, 
                            p_charset varchar2)
Reads the given amount of characters from the file input stream
Parameters:
p_amount - amount of bytes to read from the stream. Values between 1 and 32767 are allowed.
p_charset - encoding of the file contents in IANA notation (e.g. "iso-8859-1")
Returns:
the read characters as VARCHAR2

Version:
0.9

read_byte

public number read_byte()
Reads a single byte from the file input stream.
Returns:
the read byte

Version:
0.9

skip_bytes

public  skip_bytes(p_amount number)
Skips the given amount of bytes in the file input stream i.e. moves the "pointer" the given amount of bytes forward.
Parameters:
p_amount - amount of bytes to skip

Version:
0.9

write_bytes

public number write_bytes(p_bytes raw, 
                          p_offset number)
writes bytes at the specified position to the file.
Parameters:
p_bytes - bytes to write to the file
Returns:
new length of the file

Version:
0.9

write_string

public number write_string(p_string varchar2, 
                           p_offset number, 
                           p_charset varchar2)
writes a string at the specified position to the file.
Parameters:
p_string - bytes to write to the file
p_charset - encoding of the string
Returns:
new length of the file

Version:
0.9

read_bytes

public raw read_bytes(p_amount number, 
                      p_position number)
reads the specified amount of bytes at the specified position from the file.
Parameters:
p_amount - amount of bytes to read
p_position - position in the file
Returns:
the read bytes

Version:
0.9

read_string

public varchar2 read_string(p_amount number, 
                            p_position number, 
                            p_charset varchar2)
reads the specified amount of bytes at the specified position from the file and returns them as a string.
Parameters:
p_amount - amount of bytes to read
p_position - position in the file
p_charset - encoding of the string
Returns:
the read bytes as a string

Version:
0.9

get_file

public file_type get_file(p_file_path varchar2)
gets a simple file handle. A handle is always being returned, even if the file does not exist. 
SQL> select file_pkg.get_file('/home/oracle/nonexisting_file.txt') file_handle from duaL;

FILE_HANDLE
----------------------------------------------------------------------------------------------
FILE_TYPE('/home/oracle/nonexisting_file.txt', NULL, NULL, NULL, NULL, NULL, NULL, 'N')

SQL> select file_pkg.get_file('/home/oracle/existing_file.txt') file_handle from dual;

FILE_EXISTS
---------------------------------------------------------------------------------------------------
FILE_TYPE('/home/oracle/existing_file.txt', 'existing_file.txt', 0, '03.02.10', 'N', 'Y', 'Y', 'Y')
Parameters:
p_file_path - the absolute path to the file
Returns:
the file handle

get_bfile

public bfile get_bfile(p_directory_name varchar2)
returns a BFILE object, which represents this file handle. The file handle must represent a file - folders are not allowed. If the parameter P_DIRECTORY_NAME is empty or NULL, the function tries to look up a DIRECTORY object (ALL_DIRECTORIES), which matches the folder containing this file handle and uses this to create the BFILE. If none is found, an Exception is thrown. If the name of a directory object is given in P_DIRECTORY_NAME, the function uses this to construct the BFILE. 
SQL> select file_pkg.get_file('/home/oracle/exttab-files/test.txt').get_bfile() from dual;

FILE_PKG.GET_FILE('/HOME/ORACLE/EXTTAB-FILES/TEST.TXT').GET_BFILE()
--------------------------------------------------------------------------------
bfilename('DIR_EXTTAB_FILES', 'test.txt')

SQL> select file_pkg.get_file('/home/oracle/exttab-files/test.txt').get_bfile('OTHER_DIR') from dual;

FILE_PKG.GET_FILE('/HOME/ORACLE/EXTTAB-FILES/TEST.TXT').GET_BFILE('OTHER_DIR')
--------------------------------------------------------------------------------
bfilename('OTHER_DIR', 'test.txt')

1 Zeile wurde ausgewählt.

SQL> select file_pkg.get_file('/home/oracle/exttab-files/subdir').get_bfile('OTHER_DIR') from dual;
select file_pkg.get_file('/home/oracle/exttab-files/subdir').get_bfile() from dual

ERROR at line 1:
ORA-20000: CANNOT CONVERT DIRECTORY INTO BFILE
ORA-06512: at "SYS.FILE_TYPE", line 9
ORA-06512: at line 1
Parameters:
p_directory_name - Name of a directory object to create the BFILE - if given, the function will not try to find one.
Returns:
a BFILE representing this file

get_directory

public varchar2 get_directory()
Looks up a directory object which represents either, if the file handle is a directory, the handle itself, or the folder in which the file handle resides in. If no or multiple matching directory objects are found, an exception is thrown.
Returns:
The name of the matching directory object

get_free_space

public number get_free_space()
returns the space (in bytes), which is available on the device containing this file handle. Only available on Oracle 12.1 or higher.
Returns:
free space on the device in bytes

Overview  Deprecated  Index  Generator  
File and operating system interaction with SQL and PLSQL
SUMMARY: FIELD | TYPE | METHOD DETAIL: FIELD | TYPE | METHOD