At one of the projects we do for KPN we use Oracle as a database. For some daily batches we use the external tables mechanism to load data from files we receive from another supplier in the chain.
The files are in the comma separated values (csv) format like below (I used the Oracle example):
56november, 15, 1980 baker mary alice 09/01/2004 87december, 20, 1970 roper lisa marie 01/01/1999
Oracle can represent this file as a table internally and you can subject it to SQL-queries this way. A special driver is used to acces these files and originally could only acces a local filesystem on the server. At the client we used a volume that was mounted from a SAN through fiber channel.Unfortunately we rain into a problem when we wanted to use this mechanism on our production servers. I could reproduce the problem on our development servers using iSCSI (we don’t have fiber channel), that is roughly the same mechanism used by the fiber channel driver.
I created an iSCSI target and a volume and mounted it on v:\mount.
As a dba create this directory in sqlplus/your IDE to point to a directory on an iSCSI drive:
CREATE DIRECTORY ext_tab_dir AS ‘v:\test’; GRANT READ,WRITE ON DIRECTORY ext_tab_dir TO SCOTT;
As user SCOTT we create this table:
CREATE TABLE emp_load 2 (employee_number CHAR(5), 3 employee_dob CHAR(20), 4 employee_last_name CHAR(20), 5 employee_first_name CHAR(15), 6 employee_middle_name CHAR(15), 7 employee_hire_date DATE) 8 ORGANIZATION EXTERNAL 9 (TYPE ORACLE_LOADER 10 DEFAULT DIRECTORY ext_tab_dir 11 ACCESS PARAMETERS 12 (RECORDS DELIMITED BY NEWLINE 13 FIELDS (employee_number CHAR(2), 14 employee_dob CHAR(20), 15 employee_last_name CHAR(18), 16 employee_first_name CHAR(11), 17 employee_middle_name CHAR(11), 18 employee_hire_date CHAR(10) date_format DATE mask “mm/dd/yyyy” 19 ) 20 ) 21 LOCATION (‘emp_load.dat’) 22 );
The following error will occur if you try to view data in this table as user SCOTT , for example with this SQL: select * from emp_load;ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04027: file name check failed: V:\test\EMP_LOAD_1488_1176.log
Now to prove that it is the external tables driver that is not working, I will show you that the utl_file API mechanism (another driver to access files through pls/sql) DOES work:
Try this anoymous pl/sql code
declare l_file UTL_FILE.file_type; l_location VARCHAR2(100) := ‘EXT_TAB_DIR’; l_text VARCHAR2(32767); BEGIN – Open file. l_file := utl_file.fopen(l_location, ‘emp_load.dat’, ‘r’, 32767); – Read and output first line. utl_file.get_line(l_file, l_text, 32767); –header record dbms_output.put_line(l_text); utl_file.fclose(l_file); END;
It will print:
56november, 15, 1980 baker mary alice 09/01/2004
So how to make external tables work in this situation? Use a network share pointing to the same location as the fiber channel/ SCSI drive with a mapped network drive (let’s call it this share test). First map the network location to the drive on the windows command prompt of the Oracle server:
net use l: \192.168.50\test
Now change the Oracle directory object to point to the network share containing the file:
CREATE DIRECTORY ext_tab_dir AS ‘\192.168.50.5\tmp\’; GRANT READ,WRITE ON DIRECTORY ext_tab_dir TO SCOTT