Insert Text file values to database

Question

Requirement

I need to add text file values to database.

Below is my shell script

#!/bin/bash

DB_USERNAME='xxx'
DB_PASSWORD='xxxx'
DB_HOST='xxxx'
DB_PORT=xxx
DB_SERVICE='xxxx'
DB_CONNECTION=$DB_USERNAME/$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_SERVICE
echo $DB_CONNECTION
while read f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25 f26 f27
do
sqlplus -s $DB_CONNECTION <<EOF
insert into GPRS_FILTER_TABLE_2 values($f1,$f2,(TO_DATE($f3,'YYYY-MM-DD HH24-MI-SS')),$f4,$f5,$f6,$f7,$f8,$f9,$f10,$f11,$f12,$f13,$f14,$f15,$f16,$f17,$f18,$f19,
$f20,$f21,$f22,$f23,$f24,$f25,$f26,$f27);


commit;
EOF
done < /home/work/test.txt

exit

This is test.txt file values

'2341212121212' '17' '2020/05/25-15-55-03' NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL '33312212' 'vpn' '785' '0' NULL NULL NULL 'AUSTA' NULL '0' '39' 'ZBRCT1121300000001' 'Roaming GPRS' '223333'

Im getting below error

insert into GPRS_FILTER_TABLE_2 values('Roaming,GPRS',(TO_DATE('357217094875381','YYYY-MM-DD HH24-MI-SS')),,,,,,,,,,,,,,,,,
                                                                                                           *
ERROR at line 1:
ORA-00936: missing expression

seems like second line from text.txt take as a new line.

there is a line break in sample data, you need 27 fields not 3. thus formating yield incorect line. — Archemar
– Archemar, Commented Jun 11, 2020 at 10:46

Chris Davies · Accepted Answer · 2020-06-11 14:09:29Z

In your script, if you replace sqlplus -s $DB_CONNECTION with cat and you'll see the problem:

xxx/xxxx@xxxx:xxx/xxxx
insert into GPRS_FILTER_TABLE_2 values('2341212121212','17',(TO_DATE('2020/05/25-15-55-03','YYYY-MM-DD HH24-MI-SS')),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'33312212','vpn','785','0',NULL,NULL,
NULL,'AUSTA',NULL,'0','39','ZBRCT1121300000001','Roaming,GPRS' '223333');

The read statement takes no account of quotes in your source data file, so 'Roaming is a value by itself. Usually GPRS' would then be the next but because there are no futher variables to receive input, the last variable is given the entire remainder of the line, GPRS' '223333'.

There isn't an easy solution to the problem shown in your question; it's more about rearchitecting around the invariants. Ideally the data file can be changed so that you're not using the same character (a space) for delimiting values as is in your data.

Stack Exchange Network

Insert Text file values to database

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Insert Text file values to database

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions