Hello,
I have to read a file via external table but this file it's located into another directory inside the ASM.
As first step I have to move the file
So, I decided to use the preprocessor feature running a script
Here's my table (premise: all permissions are set correctly etc)
CREATE TABLE EXT_FILE
( "REC" VARCHAR2(3 BYTE),
"VAL" VARCHAR2(4 BYTE),
...
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "ACFS_EXECDIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
PREPROCESSOR ACFS_EXECDIR:'preprocess.sh'
FIELDS LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"REC" (1:3) CHAR(3),
"VAL" (4:7) CHAR(4),
...
)
)
LOCATION
( 'file.ftp'
)
)
REJECT LIMIT UNLIMITED ;
this file is located into a folder and the script (preprocess.sh) into another
so I thought to proceed like this:
* move the file into the ACFS_EXECDIR directory,
so when executing the SELECT * FROM EXT_FILE, the 'preprocess' feature would have executed the script which moves the file
into the default directory (ACFS_EXECDIR) then read it
But it doesn't work..because it cannot find the fileKUP-04040: file file.ftp in ACFS_EXECDIR not found
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
So I've created a blank file just to
pass the exception and it worked.
I thought the feature would have execute the script before anything else, then read it
of course the file is not there..
Another test I made was replacing the filename with the script's name to 'bypass' the exception again. It worked.
LOCATION
( 'preprocess.ftp'
)
)
so,
first question
Does the file have to be into the directory even before the execution of the preprocessor's script? Does it check if the file exists?second:
Do I have to cat the file to view it inside the table? Again, I thought the external table would 'view' the file automatically after the execution of the scriptthird:
I cannot find the documentation of this feature for oracle 11g, would you provide a link?Thank you
You can think of the preprocessor as like a unix pipe style option. It is applied to the file itself. Hence a workaround is to have the preprocessor script actually *output* the file contents, and then move it, for example, it could be something like:
#!/bin/sh
/bin/cat myfile.txt
/bin/mv myfile.txt some_other_directory
But, the file nominated in the LOCATION parameter must always *exist*, even if it is just an empty dummy file.
Docs are here
https://docs.oracle.com/cd/E18283_01/server.112/e17120/tables013.htm#CHDHJFJD