Here Documents

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

Note

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.

Oracle Connection:

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.

Warning

  • 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 << EOF as input to the sqlplus command 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 EOF1, EOF2, etc. or EOA, EOB, EOC, etc.

  • 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 (> outfile1).

← Functions
Arrays →

Subscribe For More Content