Shell Programming and Scripting

#!/bin/ksh

ORACLE_SID=db1
ORACLE_HOME=/user/oracle/product/920
PATH=$PATH:$ORACLE_HOME/bin
INPUT=/tmp/input.txt

export ORACLE_SID
export ORACLE_HOME

sqlplus -s db/db@db1 << xxx
set FEEDBACK ON
set TRIMS ON
set ECHO OFF
set LINESIZE 1500
set PAGESIZE 0
set COLSEP '|'
set TERM OFF
set AUTOCOMMIT ON

whenever sqlerror exit sql.sqlcode
whenever oserror exit sql.oscode

drop table my_table_tmp;
create table my_table_tmp (
 field1   VARCHAR2(10),
 field2   VARCHAR2(10),
 field3   VARCHAR2(15)
  ) tablespace USERS;
INSERT INTO my_table_tmp 
VALUES ('field1', 'field2', 'field3')
FROM $INPUT
commit;
exit 
xxx

BULK INSERT my_table_tmp
FROM '"{$input}"'
WITH (fieldterminator = ',' ,roterminator = '\n')

FROM /tmp/input.txt WITH (fieldterminator= '|', roterminator = '\n')
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

#!/bin/ksh
ORACLE_SID=db1
ORACLE_HOME=/user/oracle/product/920
PATH=$PATH:$ORACLE_HOME/bin
INPUT=/tmp/input.txt
 
export ORACLE_SID
export ORACLE_HOME
 
sqlplus -s db/db@db1 << xxx
set FEEDBACK ON
set TRIMS ON
set ECHO OFF
set LINESIZE 1500
set PAGESIZE 0
set COLSEP '|'
set TERM OFF
 
whenever sqlerror exit sql.sqlcode
whenever oserror exit sql.oscode
 
drop table my_table_tmp;
create table my_table_tmp (
 field1   VARCHAR2(10),
 field2   VARCHAR2(10),
 field3   VARCHAR2(15)
  ) tablespace USERS;
INSERT INTO my_table_tmp
VALUES ('field1', 'field2', 'field3')
FROM $INPUT 
WITH (fieldterminator= '|', roterminator = '\n')
commit;
exit 
xxx

INSERT INTO my_table_tmp
VALUES ('field1', 'field2', 'field3')
FROM $INPUT 
WITH (fieldterminator= '|', roterminator = '\n')

BULK INSERT <tablename>
FROM <file> ...

$
$ # Let's say the input file looks like this.
$ cat input.txt
a,ab,abc
b,bc,bcd
c,cd,cde
d,de,def
$
$ # And this has to modified in such a way that each line must
$ # have an "INSERT" statement that inserts the delimited data
$ # as a single row in the Oracle table. Then, you may do this -
$
$ perl -pi.bak -e "s/,/','/g;
                   s/^/insert into t\(x,y,z\) values \('/g;
                   s/$/'\);/" input.txt
$
$ # check the script
$ cat input.txt
insert into t(x,y,z) values ('a','ab','abc');
insert into t(x,y,z) values ('b','bc','bcd');
insert into t(x,y,z) values ('c','cd','cde');
insert into t(x,y,z) values ('d','de','def');
$
$ # The original data is saved in a backup file - "input.txt.bak"
$
$