Reading files with different encoding using UTL_FILE
Experts Please Help!
Background ( UTF 8 Environment )
========
There are some automated processes that handle files that are uploaded using ftp automatically and some manually. currently the process works fine. Now that we are heading to UTF8 environment,
one process fails because the file contains Trademark Symbol (™) and this process uses the UTL_FILE process to load data.
If data file is dropped on the ftp site with ANSI encoding, the data that is loaded has an upside question mark
example
testsymbol:¿:test:test.
which is not desirable as there are some client facing applications displaying this data.
The file on the client side has the (™)
Case2
======
If the data file is dropped on the ftp site with UTD encoding, the data is loaded correctly
testsymbol:™:test:test
.
However there is no control on what type of encoding the users will drop data files. FTP automatically adds the escape sequences
for multibyte data. If for the process that loads uses UTL_FILE , data that is not desirable makes it to the DB.
1. Is there any way we could automatically convert the files that are dropped to UTF 8?
or
2. Is there any routine to read the file which after it is dropped on the ftpsite ( which will have escape sequences)?
or
3. Is there any way to set FTP Server To xfer all data in UTF8
Successful attempts.
This data file as dropped by the user load perfectly using sqlloader or external tables.
Changing all the processes to load data using sqlloader or external table will be a huge undertaking.
Metalink note 227531.1 shed some light but did not help.
Thanks in advance.
Chalam