Data Loading and Unloading - Expert Oracle Database Architecture - page 716

The ^M characters are carriage-return characters from the Windows environment that should be removed
before the data is loaded. To achieve this, we'll set up a couple of directories and then use an external table with the
PREPROCESSOR directive, which invokes a shell script to remove the special ^M characters before the data is retrieved:

EODA@ORA12CR1> create or replace directory data_dir as '/tmp';
Directory created.

EODA@ORA12CR1> create or replace directory exec_dir as '/orahome/oracle/bin';
Directory created.

Now create a shell script named run_sed.bsh that uses sed to remove ^M characters from a file. This example
places the run_sed.bsh script in the /orahome/oracle/bin directory:

#!/bin/bash
/bin/sed -e 's/^M//g' $*

■ also consider using the dos2unix utility to remove unwanted characters when transferring from Windows/DoS
to UniX/Linux.

Tip

The ^M character in the run_sed.bsh script is placed in there by pressing CTRL+V and then CTRL+M (or instead
of CTRL+M, you can press the Return key here ) ; you can't simply type in ^ and then an M . It needs to be the special
character ^M .
And this makes the script executable:

$ chmod +x run_sed.bsh

Next is the external table definition:

EODA@ORA12CR1> create table csv3
2 ( col1 varchar2(20)
3 ,col2 varchar2(20)
4 )
5 organization external
6 (
7 type oracle_loader
8 default directory data_dir
9 access parameters
10 (
11 records delimited by newline
12 preprocessor exec_dir:'run_sed.bsh'
13 fields terminated by '|' ldrtrim
14 )
15 location
16 (
17 data_dir:'load.csv'
18 )
19 )
20 /
Table created.