Update field based on flat file

[Ratzie]

How would I update a record based on one the first column of a flat file and update with second column.
I know I would use set in sql but how to open the flat file to do the imput.


#!/usr/bin/sh
cat GSG.class |while read i
do echo $i
var=`sqlplus -s admin/admin <set echo off head off
set CID = $2
FROM table
WHERE CCT = '$i';
exit
EOF`

So basically update record that has GSG44U104186000TSYS000 in CCT colume and update the CID column with 1234

Flat file looks like:
GSG44U104186000TSYS000,1234
GSG44U105141000TSYS000,3456
GSG44U105142000TSYS000,5747
GSG44U105143000TSYS000,4473
GSG44U105144000TSYS000,2233
GSG44U105169000TSYS000,3342
GSG44U107608000TSYS000,3422

[James R. Ferguson]

Hi:

If you want to read your file and split its lines into comma-delimited fields, do:

#!/usr/bin/sh
OLDIFS=${IFS}
IFS=","
while read X Y
do
echo "$X = $Y"
done < filein
IFS=${OLDIFS}

Regards!

...JRF...

[Sandman! · Accepted solution]

Hi,

Here's a script to update columns of a table from values listed in a flat file:

=============================================
#!/usr/bin/sh

IFS=","
while read CCT CID
do
var=`sqlplus $USERID <<-EOF
set echo off head off
update tablename
set CID = $CID
where CCT = $CCT
/
exit
EOF`
done =============================================

cheers!

[Sandman!]

One small note on performance. It would be advisable to use one of the Oracle built-in functions like UTL_FILE in order to read the flat file and update the records in one fell swoop instead of looping through them one-by-one.

The script reads one line of input, opens up a database sqlplus session and updates one record of the table and then exits. It repeats this procedure until end of flat file is reached. This is quite resource intensive and the recommended thing would be to use the Oracle supplied UTL_FILE package.

cheers!

[Ratzie]

thanks