cancel
Showing results for 
Search instead for 
Did you mean: 

Update field based on flat file

SOLVED
Go to solution
Ratzie
Super Advisor

Update field based on flat file

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
4 REPLIES
James R. Ferguson
Acclaimed Contributor

Re: Update field based on flat file

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!
Honored Contributor
Solution

Re: Update field based on flat file

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!
Honored Contributor

Re: Update field based on flat file

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
Super Advisor

Re: Update field based on flat file

thanks