oracle - Getting an external table's location and file name? -

I am processing several files as part of an external table. Is there a way that I can get names in external names Can I process the filename and put it in the table?

Currently, I can find the only solution which is enclosing the file name in every file that is in a flat file that is not ideal for efficiency and involves modifying the original data in it. Surely the external tables know that any time the file is being processed?

I have no way to capture file names within the address parameters. Instead of modifying the original files, as an alternative solution, you can use a preprocessor to add a file name on the fly. If you had two files, then c, d with file_1.csv containing a, b, 1 and file_2.csv 2 View , you append_filename.sh : #! There may be a small shell script like / bin / bash, while the print line "% s,% S \ n" "$ {line}" "$ {1 ## * /}" did & lt; $ 1

You can prove something useful by calling the script directly:

  $. /append_filename.sh file_1.csv a, b, 1, then you can call your external table through the call, such as:  
  Table e42 (col1 varchar2 (10), To create col2 Varchar2 (10), col3 number, file name varchar2 (30)) organization external (type 'oracle_loader default directory d42 access parameters' (' Record ',' eliminated by 'newline preprocessor' append_filename.sh ') location (' File_1.csv '' File_2.csv ')); Table E42 created  
 
 Then the file name is automatically raised: 
 
  select * from e42 *; COL1 COL2 COL3 FILENAME ---------- ---------- ---------- ---------------- -------------- A1 file_1.csv cd 2 file_2.csv  
 
 I have stripped the directory path so that you can see only the name of the file - If you want you can keep the complete path, but this can not be necessary and OS details can tell those people who can only ask the table. pay attention ; I have kept it simple by using a directory for everything, but you should put preprocessor somewhere else. And of course it's assuming a UNIX-W platform or GNU device; If you are using Windows, then something similar should be possible with a batch file. 
 
 
 This line reading line will be relatively slow for large files; By combining file name with 1.5 million-line test file, it takes about 80 seconds on my platform. Other built-in tools will be faster; With  sed  this version takes only one second for the same file: 
 
  #! / Bin / bash sed -e 's! $ !, '"$ {1 ## * /}"'! ' $ 1  
 
 You can also make other options such as  awk ; You will probably need to see some of what works best (or faster enough) in your environment.