Check for external files
Somebody got to my blog searching for how to find an external file. I thought about why they might be looking for that. Since the lack of a file raises an error stack, I concluded that they want to verify whether the file exists before querying it. That way, they avoid the annoying ORA-29913
, ORA-29400
, and KUP-04040
error stack, as shown:
SQL> SELECT * FROM CHARACTER; SELECT * FROM CHARACTER * ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: DATA cartridge error KUP-04040: file CHARACTER.csv IN DOWNLOAD NOT found |
There are a number of ways to solve this type of problem but they all employ Java to read the external file system. Rather than write a custom Java module to look for a specific file, I’ll reuse the ListVirtualDirectory library from the Reading an external directory from SQL or PL/SQL blog page. I’m also using the GET_DIRECTORY_PATH
function from this blog page.
These are the steps to perform this small case. You can find broader information on the related blog pages for some elements of these steps.
1. The first thing to do is create a physical directory on the server, than a virtual directory in the database that points to the physical directory. This command creates a virtual DOWNLOAD
directory for uploads when run as the SYSTEM
user:
SQL> CREATE DIRECTORY download AS ‘C:\DATA\Files\Upload’; |
Using DOWNLOAD
for the virtual directory and Upload for the physical directory should help avoid confusion between virtual and physical directories. After you create a directory, you need to grant privileges to the schema that will read or write to the virtual directory (Oracle also needs file system level privileges to the directory). You grant read privileges by:
SQL> GRANT READ ON DIRECTORY download TO plsql; |
2. After creating the directory and granting permissions, you create an external table by using the following type of construction statement:
CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'character.bad' DISCARDFILE 'DOWNLOAD':'character.dis' LOGFILE 'DOWNLOAD':'character.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT UNLIMITED; |
The metadata or catalog information is stored in two views, the USER_EXTERNAL_TABLES
and USER_EXTERNAL_LOCATIONS
(they naturally have ALL_
or DBA_
views too). The virtual directory is stored in the USER_EXTERNAL_TABLES
view, while the physical file name is stored in the USER_EXTERNAL_LOCATIONS
view.
Note: At this point, you should be able to query the contents of the external table. Queries return an error when no file exists or when there aren’t adequate permissions to read the file. Permissions have two levels. One level is the file system, which requires that the Oracle account can read or write files in a physical directory. Another level is the database, which requires the SYSTEM
user grant read or write permission on the virtual directory.
3.This step requires you to follow the instructions from the Read an external directory from SQL or PL/SQL blog page. You should use the first (simplest) Java program and change the physical directory to your target directory when you call the DMBS_JAVA.GRANT_PERMISSION
procedure. The test script in that blog should return your character.csv
file as a file in the directory.
4.The next step requires opening up the access to the physical paths that support virtual directories. These are part of the catalog and not available without you making some changes in the catalog. The substeps that walk you through the changes are:
4(a). Connect as the SYS
user and grant SELECT
privilege on the catalog view:
SQL> GRANT SELECT ON dba_directories TO plsql; |
4(b). Connect as the SYSTEM
user and create the GET_DIRECTORY_PATH
function:
CREATE OR REPLACE FUNCTION get_directory_path ( virtual_directory IN VARCHAR2 ) RETURN VARCHAR2 IS – Define RETURN variable. directory_path VARCHAR2(256) := ”; – Define dynamic cursor. CURSOR get_directory (virtual_directory VARCHAR2) IS SELECT directory_path FROM sys.dba_directories WHERE directory_name = virtual_directory; – Define a LOCAL exception FOR name violation. directory_name EXCEPTION; PRAGMA EXCEPTION_INIT(directory_name,-22284); BEGIN OPEN get_directory (virtual_directory); FETCH get_directory INTO directory_path; CLOSE get_directory; – RETURN file name. RETURN directory_path; EXCEPTION WHEN directory_name THEN RETURN NULL; END get_directory_path; / |
4(c). You now grant EXECUTE
on the get_directory_path function to the PLSQL
user:
SQL> GRANT EXECUTE ON get_directory_path TO plsql; |
4(d). You should connect back as the PLSQL
user by:
SQL> CONNECT plsql/plsql |
5. The next step involves some tricky SQL that lets you pass a list of variables into a function called within a TABLE
function. The SQL statement returns a list of all tables and their file names after verifying whether the physical files exist. They’re illustrated in the next annotated screen shot:
SQL> COL TABLE_NAME FORMAT A20 SQL> COL file_name FORMAT A20 SQL> SELECT xt.table_name 2 , xt.file_name 3 FROM (SELECT uxt.table_name 4 , ixt.column_value AS file_name 5 FROM user_external_tables uxt CROSS JOIN 6 TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt 7 JOIN user_external_locations xl 8 ON xt.table_name = xl.table_name AND xt.file_name = xl.location; TABLE_NAME FILE_NAME -------------------- -------------------- CHARACTER CHARACTER.csv |
The first trick in this query is the CROSS JOIN
between the USER_EXTERNAL_TABLES
and TABLE
function call. You must list the table that provides the column value as a parameter to the GET_DIRECTORY_PATH
function first. If you list it second, the query will fail. The reason is that when it appears first, it places the column values in scope to the TABLE
function. The TABLE
function converts a SQL scalar collection into an aggregate table of one column, which can be used inside SQL statements. The return column name for any call to the TABLE
function is COLUMN_VALUE
. The second trick is that we create an alias for the COLUMN_VALUE
because of the restrictions that governs its use. The last (third) trick isn’t really a trick, it’s a join between the inline view aliased as XT
and the table aliased as XL
.
6. Lastly, you need to right a function that modifies the query so that it is parameter driven. The following depicts a function that returns a BOOLEAN
value of true when the file is present, and false when it isn’t. This should allow you to check whether the external file is present before querying.
CREATE OR REPLACE FUNCTION external_file_found ( table_in VARCHAR2 ) RETURN BOOLEAN IS -- Define a default return value. retval BOOLEAN := FALSE; -- Decalre a cursor to find external tables. CURSOR c (table_in VARCHAR2) IS SELECT xt.table_name , xt.file_name FROM (SELECT uxt.table_name , ixt.column_value AS file_name FROM user_external_tables uxt CROSS JOIN TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt JOIN user_external_locations xl ON xt.table_name = xl.table_name AND xt.file_name = xl.location AND xt.table_name = table_in; BEGIN FOR i IN c(table_in) LOOP retval := TRUE; END LOOP; RETURN retval; END; / |
The solution has demonstrated how you can avoid the annoying ORA-29913
, ORA-29400
, and KUP-04040
error stack in your code that queries external tables. Hope it is useful. If you find any problems, please post a comment to let me know.
Since writing this original post, I had another idea. It’s simpler. You can find it here.
maclochlainn
12 Jul 09 at 10:58 am
There is a undocumented package dbms_backup_restore.searchfiles that can (recursive) List all Filmes in a Directory.
Marco
Marco Patzwahl
16 Feb 19 at 12:52 am