Conversion
During the parsing of the columns an Oracle SQL*Loader or SQL Server BULK Insert conversion string can be used and is required in the case of dates for Oracle. Refer to the Oracle Sql loader manual for syntax. Any Oracle function can be used if enclosed in quotes and with the column name prefixed by a colon. For example: when loading a column called 'product_name', use the following syntax to bring over only the first 30 characters:
product_name "substr(:product_name,1,30)"
A special variable %FILE_NAME% can be used in an Oracle File based load. This will be substituted with the actual source file name.
To load the full file name (including the path) into a field, enter this transformation:
"RTRIM('%FILE_NAME%')"
To remove the path from the full file name on a UNIX or Linux system, enter this transformation:
"SUBSTR('%FILE_NAME%',INSTR('%FILE_NAME%','/',-1,1)+1)"
Note: If running from the windows scheduler then a wildcard must be used in the file name if you wish to use the %FILE_NAME% variable. If a wild card is not used then the literal file name appears enclosed in double quotes. When a wild card is used Windows looks up the file and returns the actual file name with no enclosing double quotes.