Shell Programming and Scripting

$  
$ # display contents of input data file
$ cat /tmp/input.csv                   
a,ab,abc                               
b,bc,bcd                               
c,cd,cde                               
d,de,def                               
$                                      
$ # display contents of the shell script
$ cat -n load_xtern.sh                  
     1  #!/bin/bash                     
     2                                  
     3  sqlplus -s /nolog <<EOF         
     4  connect test/test               
     5  -- create directory if not created already
     6  create or replace directory data_dir as '/tmp';
     7  -- create external table                       
     8  create table t_xtern (                         
     9    c1  varchar2(1),                             
    10    c2  varchar2(2),                             
    11    c3  varchar2(3)                              
    12  )                                              
    13  organization external                          
    14    ( default directory data_dir                 
    15      access parameters                          
    16      ( records delimited by newline             
    17        fields terminated by ','                 
    18      )
    19    location ('input.csv')
    20  );
    21  -- load into main table using external table; it's
    22  -- assumed that main table :
    23  -- t_main (x varchar2(1), y varchar2(2), z varchar2(3))
    24  -- exists at this point
    25  insert into t_main (x, y, z)
    26  select c1, c2, c3 from t_xtern;
    27  commit;
    28  exit
    29  EOF
    30
$
$ # now run the shell script
$ . load_xtern.sh

Directory created.


Table created.


4 rows created.


Commit complete.

$
$ # check the data loaded into the main table - t_main
$ echo "select * from t_main;" | sqlplus -s test/test

X Y  Z
- -- ---
a ab abc
b bc bcd
c cd cde
d de def

$
$