Solaris: Shell script to write output to a file.

Hi Tintin
i cant get the script to copy the highest value of MAXID back into the /tmp/tempid file. Then next time the script runs the sql statement uses this highest value to pull out all data greater than this value and again copy the next highest value back into tempidfile.
Is this any clearer?  

What happens when you uncomment the line that *does* write the new value of MAXID back to the file, ie:

#echo $NEW_MAXID > $FILE

spool /tmp/tempid
set heading off
        select maxid .........;
spool off

a) Why do you read from $FILE in such a complicated way?
    Just do your "read" from $FILE. No need to redirect I/O streams.
b) To make sure we only read the value from $FILE, I slightly
    modified the "read" command. If you have something like
         20   # this is my start value
    in $FILE, it will only pick up the 20 into $line and put all the rest
    into $junk.
c) If you don't activate the line
       echo $NEW_MAXID > $FILE
    the contents of your file will never be altered.

#!/bin/sh
 
echo "Retrieve id"
 
FILE="/tmp/tempid"
MAXID=0
 
if [ ! -f $FILE ]; then
  echo "$FILE : deos not exist"
  exit 1
elif [ ! -r $FILE ]; then
  echo "$FILE : can not read"
  exit 2
fi
 
# read value from $FILE (the first word on line):
while read line ; do
        MAXID=$line junk
done < $FILE
 
# Query
 
sh -c "sqlplus etc ................ <<EOF       
spool /tmp/temp.csv
select blah blah blah.........;
spool off
quit;
 
 
NEW_MAXID=`expr $MAXID + 10`
echo $NEW_MAXID
 
# write New MAXID to the file
echo $NEW_MAXID > $FILE
 
exit 0

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

Oops -- I messed up with the code (bad copy-and-paste) :-((

Here's the correct one:

#!/bin/sh
 
echo "Retrieve id"
 
FILE="/tmp/tempid"
MAXID=0
 
if [ ! -f $FILE ]; then
  echo "$FILE : deos not exist"
  exit 1
elif [ ! -r $FILE ]; then
  echo "$FILE : can not read"
  exit 2
fi
 
# read value from $FILE (the first word on line):
while read line junk; do
  MAXID=$line
done < $FILE
 
# Query
 
sh -c "sqlplus etc ................ <<EOF       
spool /tmp/temp.csv
select blah blah blah.........;
spool off
quit;
 
 
NEW_MAXID=`expr $MAXID + 10`
echo $NEW_MAXID
 
# write New MAXID to the file
echo $NEW_MAXID > $FILE
 
exit 0

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

Thanks for this info.

I still have a few issues.

1. The script stops after the sql statement so the MAXID section does not get updated.
2. I dont want to just add 10 to the MAXID value i really need to use the highest value of MAXID from the database as this is what i need to use in my query to retreive only latest data since script was last run. Do i need to do another query for this eg select max(MAXID) from ......... ?

As a side issue this script runs ok as user "abc" but wont run from cron. Is this a Path issue
Thanks

1) As you can see from the code (all in green) you are missing a closing "
    and also the string EOF does no t appear.
    I thought that it can be found somewhere down (part of the code you
    did not post here).
2) The actual SQL part will have to be changed accordingly (see below).
3) When running scripts from cron, you don't have the full environment
    loaded! Especially, you usually won't find teh command sqlplus in your
    PATH. Either make sure to add it to your PATH variable in the script or
    use explicit paths for commands.

# Query some stuff from DB using sqlplus:
sh -c "sqlplus etc ................" << 'EOF'
 spool /tmp/temp.csv
 select blah blah blah......... ;
 spool off
 quit ;
EOF
 
# get MAXID from DB using sqlplus:
NEW_MAXID=`/path/to/sqlplus ... 'SELECT MAX(whatever) FROM sometable;'`
echo $NEW_MAXID
 
# write New MAXID to the file:
echo $NEW_MAXID > $FILE
 
exit 0

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Thanks
adding the EOF worked fine.

I must have the wrong format for the next part to get the NEWMAXID as i get this error:

Bind variable "SQLPLUS" not declared.
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ] where <option> ::= - | -? | [ [-M <o>] [-R <n>] [-S] ] <logon> ::=

<username>[/<password>][@<connect_string>] | / | /NOLOG <start> ::= @<filename>[.<ext>] [<parameter> ...] "-" displays

the usage syntax "-?" displays the SQL*Plus version banner "-M <o>" uses HTML markup options <o> "-R <n>" uses

restricted mode <n> "-S" uses silent mode

sh -c "sqlplus -s abc/abc"<<EOF
 
spool /tmp/temp.xls
 
        select blah blah blah .........;
 
spool off
quit;
EOF
 
# get MAXID from DB using sqlplus:
NEW_MAXID=`/full_path/sqlplus -s abc/abc 'SELECT MAX(id) FROM id;'`
echo $NEW_MAXID
 
# write New MAXID to the file:
echo $NEW_MAXID > $FILE
exit 0

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17: