Setting Unix Environment Variables From SQL

If you need to use a value from a table in a UNIX shell script, you can retrieve the value in sqlplus, set a shell environment variable from that retrieved value, and use that environment variable in your shell script.  For example, suppose you are creating a shell script to run an sql script using runsqlplus, but want to use a printer entered in the first parameter of the job submission screen instead of the printer specified in the Printer environment variable set by the job submission screen.  The following retrieves parameter number 01 for the current job being run by job submission and stores it in the unix environment variable named "printer". The tic (or whatever it's called) symbol, ` , is below the tilde symbol, ~ , on the top left of most keyboards.

`echo \`sqlplus -S $UID/$PSWD <<EOF
select gjbprun_value from gjbprun where gjbprun_number = '01'
   and gjbprun_one_up_no = $ONE_UP;
EOF\` | sed 's/.* \(.*\)/export printer=\1/'`

Then, you could do something like:
    runsqlplus $UID $PSWD mysqlscript $ONE_UP $HOME/$TEMP.lis $LOG $printer
instead of the current:
    runsqlplus $UID $PSWD mysqlscript $ONE_UP $HOME/$TEMP.lis $LOG $PRNT

This technique can be used to retrieve any value from the database and set an environment variable independent of job submission.  You would just have to substitute a user ID and password for $UID and $PSWD, and change the select statement to any other select statement that returns just one value that didn't contain embedded blanks.  If the value did contain blanks, you would have to modify the sed command to just strip off the header instead of everything up through the last blank.  (This exercise is left to the reader.)

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.