Script does not execute Insert Statement - Shell Programming and Scripting

Amruta Pitkar · #1 08-24-2006

Hi

I have a text file , contents are
Line1:field1,field2,field3,field4,field5,field6.......field20
Line2:field1,field2,field3,field4,field5,field6.......field20
Line3:field1,field2,field3,field4,field5,field6.......field20
....and so on...

I want to read this file and insert the data into table.

How can I do that...can anybody help ?
I tried to read the text file , each line, cut the fields, make connection to database and then execute the Insert Statement....
But when I do this the Insert statement does not execute.
Can anybody guide me how do I achieve this ?

Code:

#####Connecting sqlplus to check the connectivity
sqlplus -s /nolog <<EOF>/dev/null
connect ${DB_LOGIN}/${DB_PASSWORD}@${DB_NAME}

cat /$FILEPATH/$FILENAME | while read LINE
do
    echo ${LINE}
    acctno=`echo $LINE | cut -f1 -d','`
    echo $acctno
    salutation=`echo $LINE | cut -f2 -d','`
    name=`echo $LINE | cut -f3 -d','`
    billdate=`echo $LINE | cut -f4 -d','`
    totaldue=`echo $LINE | cut -f5 -d','`
    billdeductiondate=`echo $LINE | cut -f6 -d','`
    billduedate=`echo $LINE | cut -f7 -d','`
    templatecode=`echo $LINE | cut -f8 -d','`
    billid=`echo $LINE | cut -f9 -d','`
    emailid=`echo $LINE | cut -f10 -d','`
    brnno=`echo $LINE | cut -f11 -d','`
    billsummaryid=`echo $LINE | cut -f12 -d','`
    batchno=`echo $LINE | cut -f13 -d','`
    emailfile=`echo $LINE | cut -f16 -d','`
    echo "Insert into table"

    spool ${SYS_TEMP_DIR}/${JOB_ID}_db_sql.log
    INSERT INTO SendMailDetails (acctno) values ('${acctno}');
    echo "Table Appended"
    commit;
    spool off;
    exit;
    EOF
done

I also tried:

Code:

MYCOMMAND=`awk '$1' ${FILEPATH}${FILENAME}`
sqlplus -s /nolog <<EOF>/dev/null
connect ${DB_LOGIN}/${DB_PASSWORD}@${DB_NAME}

spool my.log
Insert into SendMailDetails(acctno) values($MYCOMMAND);
spool off
commit;
exit
EOF;

But am unsuccessfull, Please help.....

mukundranjan · #2 08-24-2006

I think its pretty easy to use sql loader for your purpose.

Amruta Pitkar · #3 08-24-2006

Thanks Mukund

Atleast I got a direction...

Thanks
Amruta

blowtorch · #4 08-24-2006

Amruta, you could use the shell to do this quite easily:

Code:

#####Connecting sqlplus to check the connectivity
sqlplus -s /nolog <<EOF>/dev/null
connect ${DB_LOGIN}/${DB_PASSWORD}@${DB_NAME}
oldIFS=$IFS
IFS=,
while read acctno salutation name billdate totaldue billdeductiondate billduedate templatecode billid emailid brnno billsummaryid batchno field14 field15 emailfile rest_of_the_fields; do
    echo "Insert into table"
# insert into whatever table, whichever values that you want
# after you are done, go ahead
    echo "Table Appended"
    commit;
    spool off;
    exit;
    EOF
done

field14, field15: hold the 14th and 15th fields, rest_of_the_fields: holds the fields from 17 to end of line. Everything else goes into the appropriately named variables.

Amruta Pitkar · #5 08-24-2006

Hi there
I worked on Mukund's suggestion to use sqlloader...it worked successfully.
this is the main shellscript
LoadMain.sh

Code:

#!/usr/bin/ksh

DB_LOGIN=SOMESYSTEM
DB_PASSWORD=SOMEPASSWD
DB_NAME=SOMENAME
FILENAME="$1"

sqlldr ${DB_LOGIN}/${DB_PASSWORD}@${DB_NAME} control=tryloader log=mlist2.log data=mlist2.txt

echo "I am done"

-------------
data=mlist.txt is the Input text file which contains data with delimiters.
control=tryloader.ctl is the Control file which contains information as to how the data will be appended to the table...
My tryloader.ctl contains :

Code:

LOAD DATA
APPEND INTO TABLE SendMailDetails
(
  AcctNo CHAR TERMINATED BY ",",
  Salutation CHAR TERMINATED BY ",",
  Name CHAR TERMINATED BY ",",
  BillDate CHAR TERMINATED BY ",",
  TotalDue DECIMAL EXTERNAL TERMINATED BY ",",

 ...and other fields
)

And again..thanks Guys....

Amruta Pitkar

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
(sed) parsing insert statement column that crosses multiple lines	jjordan	Shell Programming and Scripting	3	10-08-2007 09:23 PM
Need to execute the same statement	Legend986	Shell Programming and Scripting	8	10-01-2007 01:59 PM
How do i execute in IF ELSE Statement	laknar	Shell Programming and Scripting	1	06-07-2007 11:54 PM
Insert TAB in echo statement	sunils27	Shell Programming and Scripting	5	08-26-2005 12:36 AM
awk command for INSERT statement	nattynatty	Shell Programming and Scripting	4	05-10-2002 11:11 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode