There are two simple ways to create another file, one uses the cat command in conjunction with the redirect symbol, the other way is to use the echo command in conjunction with the redirect symbol. The example Indented Cat is a good example of the cat method in the Pipes and Redirects section. This example only contains litteral text however. It is more appropriate to see something like the example below, which shows a variable being used in the
Example cat and variables
sqlplus -s $uid/$password@database @$sql0 $sql_arg_1 > $log0 The file created has its name stored in the variable $sql0 and as we can see the block between the EOA flags is the data that goes into the file. The data block is actually a segment of SQL*Plus statements, as indicated by the filename variable. As is common with SQL*Plus code, the key words are picked out in ALL CAPS, with objects (tables, procedures, columns, etc.) all in lower case. The SELECT line contains a reference to a called, packaged, PL/SQL function which has a column name as an argument. Here the column name is held in a variable called $column and this will be substituted at script run-time by the real value.
There are some unfortunate consequences of generating SQL*Plus statements from within a shell script which you have to be aware of. Firstly, don't forget to put the EXIT statement at the end of the block or you will end up with a script that stays in SQL*Plus forever. Secondly, don't forget to put the semi-colons (;) at the end of every SQL statement, or each statement will overwrite the previous one or just create one long unprocessable mess.
Thirdly, some internal database tables may contain the dollar symbol, which is special to the shell, so escape them with the back-slash (\) as shown on the FROM line.
On the WHERE line there is a reference to a SQL*Plus positional parameter '&1' which will pick up its value from the variable $sql_arg_1 at run-time as shown in the last line, just after the end of block flag. Did I say this was a simple example? W ell, at least you don't have to worry about quoting when using this method. All quotes find their way to the destination file unscathed.
Now to do the same thing using echo instead of cat, see the example below.
Example simple echo
echo "SET ECHO OFF" >> $sql0
echo "SET FEEDBACK OFF" >> $sql0
echo "SET HEADING OFF" >> $sql0
echo "SELECT my_package.my_function($column)" >> $sql0 echo " FROM v\$database" >> $sql0
echo " WHERE name LIKE '%&1%';" >> $sql0
echo "EXIT" >> $sql0
sqlplus -s $uid/$password@database @$sql0 $sql_arg_1 > $log0
Complex File Creation:
So what's the point of all this extra typing? Well for one thing it allows you to put special bits of code into the block which will only be used at certain times, by hiding them in complex command groups. This example shows how this is done below.
Example complex echo forms
echo "SET ECHO OFF" >> $sql0
echo "SET FEEDBACK OFF" >> $sql0
echo "SET HEADING OFF" >> $sql0
echo "SELECT my_package.my_function($column)" >> $sql0 echo " FROM v\$database" >> $sql0
if [ "$db_type" = "m" ] then
echo " WHERE name = '$db_name';" >> $sql0 else
echo " WHERE name LIKE '%&1%';" >> $sql0 fi
echo "EXIT" >> $sql0
sqlplus -s $uid/$password@database @$sql0 $sql_arg_1 > $log0 This is basically the same block except the WHERE clause has been hidden inside an if statement. Now, depending on the Database Type in the $db_type variable, the WHERE clause can take one of two forms. Conveniently, the additional argument which is not required by SQL*Plus in the first form, is ignored at execution time, even though it is still available on the last line. This is common with all scripts, arguments are only used if they are referenced from within the script.
So there you have the first two ways of creating another file from a script. The version using cat can only cope with a single output form, the version using echo can output a multitude of forms depending on the complex command forms you use. The choice is yours. There are, however, other ways to create output files. You can use direct generation as in the example List to create a list of files. Or the indirect method shown in the example Counted List where lines are built inside a loop construct and then appended to the file to create a menu file. Or in the example Sorted List where a list of words is sorted into alphabetic order, duplicates are removed, then the rest stored in a file.
Example list ls -1 *.log > $lst0
Example counted list
for file in `ls -1 *.log`
do
echo "$count: $file" >> $mnu0 count=`expr $count + 1`
done
Example sorted list
echo $@ | tr ' ' '\n' | sort -u > $lst0