A here document is a special-purpose code block. It uses a form of I/O redirection to feed a command list to an interactive program or a command, such as ftp, cat, or the ex text editor.
The syntax looks like this:
COMMAND <<InputComesFromHERE ... Input Comes From HERE
A limit string delineates (frames) the command list. The special symbol
<< designates the limit string. This has the effect of redirecting the output of a file into the stdin of the program or command. It is similar to interactive-program
< command-file, where command-file contains
Choose a limit string sufficiently unusual that it will not occur anywhere in the command list and confuse matters. Note that here documents may sometimes be used to good effect with non-interactive utilities and commands, such as, for example, wall.
Let’s look at a sample section from a script file:
cat << marker These words are to be printed with the cat command, until the line with "marker" is found. marker echo "End of text."
The text down to (but not including)
marker will be printed on the screen when this script is run. Then the echo command is executed, giving an output of:
These words are to be printed with the cat command, until the line with "marker" is found. End of text.
Calling sql scripts from shell scripts
SQL *Plus can be called within shell scripts like any other command.
A username and password can be provided to avoid being prompted for them.
A sql script can be provided as an argument.
The –S option can be used to suppress the SQL *Plus banner and prompt.
#!/bin/sh sqlplus –S system/manager@database_status.sql
This short script will allow to easily execute a SQL script with given permissions.
Calling sqlplus in this manner may expose your username and password to others on the system.
Files which contain usernames and passwords must be properly secured to avoid exposing the passwords.
Getting past the password problems
A combination of two methods can be used to get around the password problems with the previous method.
Place the password in a variable so it will not display in a process listing.
Rather than placing the password in the shell script store it in a separate, secure file.
Placing the password in a variable
#!/bin/bash system_pw=manager sqlplus –S system/$system_pw@database_status.sql
When this command is running a process listing (ps) will show the variable name (
$system_pw) instead of the password.
Reading the password from a secure file:
#!/bin/bash system_pw=`cat /u01/app/oracle/admin/oss/pw/system.pw` sqlplus –S system/$system_pw@database_status.sql
By reading the password from a text file the script is no longer required to have the password embedded in it. This has the added advantage of providing a single location where passwords can be changed for all scripts at once.
Securing the password files
In order to keep the passwords secure the files which contain them should have as restrictive permissions as possible. Using the chmod command we can grant the owner (typically the oracle user) read and write permissions and revoke all permissions for other users.
chmod u=rw,g=,o= /u01/app/oracle/admin/oss/pw/system.pw
Using SQL directly in shell scripts:
By using file markers we can call SQL directly from our shell scripts. This can make scripts easier to move and maintain as there is only one file to work with.
#!/bin/bash sqlplus –S john/john@oracle << EOF select ̳employee name is ̳||ename||‘ working as ̳||job||‘ since ̳||hiredate from emp; EOF
The above script connects to the database and fetches the result of the input query. The advantage is that we are interacting with database from the OS level. Similarly, other transactions like inserting, deleting and so on can be done.
Using File markers
The shell will interpret everything that follows
<< EOFas input to the
sqlpluscommand until it encounters another EOF on its own line
Multiple file markers can be used within the same script but they will need to be unique. Common file markers are
EOF2, etc. or
File markers do not have to be all upper-case but it does make them easier to spot in a script.
Be sure to provide appropriate commits!
Getting information out of SQL plus
The output of sqlplus can be sent to a file on the system for further processing. Output is redirected with the > symbol.
sqlplus –S john/john@oracle << EOF > outfile1 set feedback off select * from emp; EOF
It is easiest to look at this as two separate statements, the redirection of input(
<< EOF) and the redirection of the output