cheng01120

Get output from a query returned into a shell script variable

Get output from a query returned into a shell script variable
The utility of this is endless as most anything from a query in SQL*Plus can be returned into a shell variable. Although there are other ways of adding a timestamp as part of a file's name, this example uses the date returned from a query and places it into a file's name (e.g., a spool file).

TS=`sqlplus -s username/password@SID <<EOF
set heading off feedback off verify off
select to_char(sysdate, 'YYYYMMDD_SSSSS') from dual;
exit
EOF
`
TS=`echo $TS | tr "[a-z]" "[A-Z]"`
echo "The timestamp is $TS"

As a test, put this into my_new_script.ksh and run it.

linux.box.net> my_new_script.ksh
The timestamp is 20060720_18200
If you are familiar or comfortable using date formatting/masking in UNIX, this approach does everything UNIX does and more because of the numerous ways in which dates can be formatted. You can also include a random number (concatenated with the date query) if the 86,400 seconds in a day in combination with the date is not enough to make a file name unique.

Two things to take note of include the back tick single quotation mark and the use of the tr command. The "`" character is hard to see, especially if your editor allows color mode, so don't forget to include it (or check for it during troubleshooting). The other item is the "tr" (translate) command. This helps to stop carriage/line return when referencing the variable. If your output looks like what is shown below, the tr command's conditioning of the variable's value will make the variable more "normal."

The timestamp is
20060720_18200

posted by cheng01120 @ 7:04 PM