Package file_pkg

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_file_list

public file_list_type get_file_list(p_directory file_type)

gets all file handles in a directory (directory listing) as a virtual table. This function is used best in a SQL SELECT statement

select * from table(file_pkg.get_file_list(file_pkg.get_file('/')))

This function allows operations on multiple files within a single SQL SELECT command. The following SQL gets the content of all files in a directory as a BLOB:

select value(e).get_content_as_blob() from table(file_pkg.get_file_list(file_pkg.get_file('/'))) e

Note: This function prepares the full table in memory before returning - use the new GET_FILE_LIST_P function for optimized memory usage and better performance.

Parameters:

p_directory - Directory from which the file handles to be obtained

Returns:

Table of file handles

get_recursive_file_list

public file_list_type get_recursive_file_list(p_directory file_type)

gets all file handles in a directory and recursively in all subdirectories (directory listing) as a virtual table. This function is used best in a SQL SELECT statement

select * from table(file_pkg.get_file_list(file_pkg.get_file('/')))

This function allows operations on multiple files within a single SQL SELECT command. The following SQL gets the content of all files in a directory as a BLOB:

select value(e).get_content_as_blob() from table(file_pkg.get_recursive_file_list(file_pkg.get_file('/'))) e

Note: This function prepares the full table in memory before returning - use the new GET_RECURSIVE_FILE_LIST_P function for optimized memory usage and better performance.

Parameters:

p_directory - Directory from which the file handles to be obtained

Returns:

Table of file handles

get_path_separator

public varchar2 get_path_separator()

returns the operating system-specific path separator character; "/" on Unix, "\" on Windows. Can be used to determine the operating system the Oracle instance is running on.

Returns:

the path separator char

get_root_directories

public file_list_type get_root_directories()

gets file handles for all root directories (one for each drive letter on windows platforms) as a virtual table.

Returns:

Table of file handles

get_root_directory

public file_type get_root_directory()

returns file handle for the root directory (the first drive letter on windows platforms).

SQL> select file_pkg.get_root_directory() ROOT from dual;

ROOT
--------------------------------------------------------------------------------
FILE_TYPE('/', '', 4096, '22.01.10', 'Y', 'N', 'Y', 'Y')

Returns:

file handle

get_recursive_file_list_p

public file_list_type get_recursive_file_list_p(p_directory file_type)

gets all file handles in a directory and recursively in all subdirectories (directory listing) as a virtual table. This function is used best in a SQL SELECT statement

select * from table(file_pkg.get_file_list(file_pkg.get_file('/')))

This function allows operations on multiple files within a single SQL SELECT command. The following SQL gets the content of all files in a directory as a BLOB:

select value(e).get_content_as_blob() from table(file_pkg.get_recursive_file_list(file_pkg.get_file('/'))) e

Note: This new function returns the directory listing pipelined, which leads to optimized memory usage and better performance. Use this function instead of the "old" GET_RECURSIVE_FILE_LIST function.

Parameters:

p_directory - Directory from which the file handles to be obtained

Returns:

Table of file handles

Version:

0.9

get_file_list_p

public file_list_type get_file_list_p(p_directory file_type)

gets all file handles in a directory (directory listing) as a virtual table. This function is used best in a SQL SELECT statement

select * from table(file_pkg.get_file_list(file_pkg.get_file('/')))

This function allows operations on multiple files within a single SQL SELECT command. The following SQL gets the content of all files in a directory as a BLOB:

select value(e).get_content_as_blob() from table(file_pkg.get_file_list(file_pkg.get_file('/'))) e

Note: This new function returns the directory listing pipelined, which leads to optimized memory usage and better performance. Use this function instead of the "old" GET_FILE_LIST function.

Parameters:

p_directory - Directory from which the file handles to be obtained

Returns:

Table of file handles

Version:

0.9

set_fs_encoding

public  set_fs_encoding(p_fs_encoding varchar2, 
                        p_reset_session boolean)

sets the encoding to use for file names. This procedure is important when the database encoding does not match the filesystem encoding and filenames contain non-ASCII characters. In a Unicode database on a windows system with "windows-1252" filesystem encoding the procedure is being used as follows:

file_pkg.set_fs_encoding('windows-1252');

On a 10g database this call must be executed before the very first file system access.

Parameters:

p_fs_encoding - The encoding to use for filesystem access; e.g. windows-1252, iso-8859-1, utf-8

p_reset_session - 11g and higher: Reset java session before (default is true).

Version:

0.9.3

get_fs_encoding

public varchar2 get_fs_encoding()

gets the current file system encoding

Returns:

the current file system encoding

get_file

public file_type get_file(p_directory varchar2, 
                          p_filename varchar2)

gets a simple file handle by directory object and file name. The directory object must exist and be accessible for the current user (ALL_DIRECTORIES). If an invalid directory object is given, the function throws an exception. If the filename is NULL, the returned file handle represents the directory object itself.

SQL> select file_pkg.get_file('HOMEDIR', 'existing_file.txt') file_handle from dual;

FILE_HANDLE
---------------------------------------------------------------------------------------------------
FILE_TYPE('/home/oracle/existing_file.txt', 'existing_file.txt', 0, '03.02.10', 'N', 'Y', 'Y', 'Y')

Parameters:

p_directory - the name of the directory object

p_filename - the file name

Returns:

the file handle

get_file

public file_type get_file(p_bfile bfile)

gets a simple file handle by BFILE. The directory object within the BFILE must exist and be accessible for the current user (ALL_DIRECTORIES). If the BFILE contains an invalid directory object, the function throws an exception.

SQL> select file_pkg.get_file(bfilename('HOMEDIR', 'existing_file.txt')) file_handle from dual;

FILE_HANDLE
---------------------------------------------------------------------------------------------------
FILE_TYPE('/home/oracle/existing_file.txt', 'existing_file.txt', 0, '03.02.10', 'N', 'Y', 'Y', 'Y')

Parameters:

p_bfile - the bfile object

Returns:

the file handle

get_file_list

public file_list_type get_file_list(p_directory_name varchar2)

gets all file handles in a directory (directory listing) as a virtual table. This function expects the name of an existing directory object as VARCHAR2. If the given directory object does not exist or is not accessible, an exception is thrown. The function is used best in a SQL SELECT statement

select * from table(file_pkg.get_file_list('HOMEDIR'))

This function allows operations on multiple files within a single SQL SELECT command. The following SQL gets the content of all files in a directory as a BLOB:

select value(e).get_content_as_blob() from table(file_pkg.get_file_list('HOMEDIR')) e

Parameters:

p_directory_name - name of an existing directory object to be listed

Returns:

Table of file handles

get_file_list_p

public file_list_type get_file_list_p(p_directory_name varchar2)

gets all file handles in a directory (directory listing) as a virtual table. This function expects the name of an existing directory object as VARCHAR2. If the given directory object does not exist or is not accessible, an exception is thrown. The function is used best in a SQL SELECT statement

select * from table(file_pkg.get_file_list('HOMEDIR'))

This function allows operations on multiple files within a single SQL SELECT command. The following SQL gets the content of all files in a directory as a BLOB:

select value(e).get_content_as_blob() from table(file_pkg.get_file_list('HOMEDIR')) e

Note: This new function returns the directory listing pipelined, which leads to optimized memory usage and better performance. Use this function instead of the "old" GET_FILE_LIST function.

Parameters:

p_directory_name - name of an existing directory object to be listed

Returns:

Table of file handles

remove_multiple_separators

public varchar2 remove_multiple_separators(p_path varchar2)

This function "normalizes" a file path by removing trailing slashes and multiple slashes within the path. Useful to compare file paths.

select file_pkg.remove_multiple_separators('/path//to///a/file/') as NORMALIZED_PATH from dual;

NORMALIZED_PATH
-------------------------
/pah/to/a/file

Parameters:

p_path - filesystem path to normalize

Returns:

the normalized path