SQL not working in a for loop

Applications Programming
1

Need help.

Any reason why the update is not working in this sql:

#!/bin/ksh
#setup your environment
. /opt/lnpsite/nm00/scripts/setup_env nm00

for tn in `cat /home/cpac/Resync/sv_tn.list`
do
    `sqlplus -s ${DB_USERID} << EOF
    SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
    alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
    update sv set old_ts=sysdate, status=7 where status=1 where tn='$tn' and rownum < 1;
    commit;
EOF`

echo "Set TN=${tn} to OLD"
done

Here is the error I get:

sv_table_tn_update.ksh[8]: update: not found [No such file or directory]
Set TN=2535314426 to OLD

---------- Post updated at 09:40 AM ---------- Previous update was at 08:52 AM ----------

never mind. found the root cause.

---------- Post updated at 09:41 AM ---------- Previous update was at 09:40 AM ----------

Here is the fix and it works:

do
    `sqlplus -s ${DB_USERID} << EOF
    SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
    alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
    update sv set old_ts=sysdate, status=7 where status=1 and tn='$tn' and rownum < 1;
    commit;
EOF`
2

I guess the spaces at the beginning of the lines were the problem(?)

In any case, a couple of observations:

  • The clause "rownum < 1" is always false. Oracle's pseudo-column rownum has an integer value of 1 or higher. Looks like in your case the shell script does not throw any error, but the update statement doesn't update anything either.

  • The backticks from "sqlplus" to the "EOF" are not necessary if do not want to assign the output of the sqlplus command to a shell variable.

  • If your file: sv_tn.list has a large number of rows, then connecting to sqlplus and updating the database for each row may be too expensive and inefficient.
    If you only want to update all rows with the "tn" values in the sv table, then you may want to go through the sv_tn.list file, collect all "tn" values into a comma-delimited list and then issue a single update like so:

update sv set old_ts=sysdate, status=7 where status=1 where tn in $tn_list

where tn_list has the form:

('123','456','789')

and can be created like so:

$
$ cat sv_tn.list
45879
8928712
9901
$
$ tn_list=`awk '{printf("%c%s%c,", 39,$0,39)}' sv_tn.list | sed -e 's/^\(.*\),$/\(\1\)/'`
$
$ echo $tn_list
('45879','8928712','9901')
$
$
3

How can I make that sql run fatser by updating and committing in chunks of 1000 for ex?

Please use this working version:

for tn in `cat /home/cpac/mnaji/Resync/sv_tn.list.split1`

do
    `sqlplus -s ${DB_USERID} << EOF
    SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
    update sv set old_ts=sysdate, status=7 where status=1 and tn='$tn';
    commit;
EOF`
count=`expr $count + 1`
echo "Record # $count - Setting TN=${tn} to OLD on SV"
done
4

Why is sqlplus in backticks?

Why not run sqlplus once for 10,000 statements instead of 10,000 times for 10,000 statements? Connection overhead can be significant.

There is no point putting cat in backticks here.

(

count=0

echo "SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF"

while read tn
do
        echo "update sv set old_ts=sysdate, status=7 where status=1 and tn='$tn';"
        echo "commit;"
        count=`expr $count + 1`
        # Printing to stderr avoids feeding this text into sqlplus
        echo "Record # $count - Setting TN=${tn} to OLD on SV" >&2
done < /home/cpac/mnaji/Resync/sv_tn.list.split1 ) | sqlplus -s ${DB_USERID}
1 Like
5

Where would I tell it to commit every 100k rows updated for ex !

6

Borrowing heavily from Corona688, I assume you mean something like:

(   count=0
    echo "SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF"
    while read tn
    do  echo "update sv set old_ts=sysdate, status=7 where status=1 and tn='$tn';"
        count=$((count + 1))
        [ $((count % 100000)) -eq 0 ] && echo "commit;"
        # Printing to stderr avoids feeding this text into sqlplus
        echo "Record # $count - Setting TN=${tn} to OLD on SV" >&2
    done < /home/cpac/mnaji/Resync/sv_tn.list.split1
    [ $((count % 100000)) -eq 0 ] || echo "commit;"
) | sqlplus -s ${DB_USERID}
2 Likes