Put Select Result In A Ksh Variable

using sql loader, I know I can reference a ksh variable in my ctl file. For example I can write LOAD DATA INFILE '$PATH_IN_KSH/my_file.dat' ... I would like to add a WHEN clau

Solution 1:

As a basic outline you can run SQL*Plus with a heredoc to perform the query, and assign the output to a variable:

P_NUMBER=`sqlplus -s /nolog <<!EOF
connectusername/password
wheneversqlerrorexitfailuresetpagesize 0
setfeedbackoffselectyour_valuefromyour_tablewhereyour_key = 'something'; 
exit 0
!EOF`

Enclosing in backticks assigns the result to the variable. $P_NUMBER will then hold whatever value your query got (or an error message if the credentials were wrong, say). It helps if you're sure the query will return exactly one result. You can also test the return code with $? to look for errors, before you try to use your variable.

Including the -s flag, turning off feedback and setting the pagesize to zero collectively suppress all the noise so you only get the result and don't have to strip out banners, headings etc.

And finally I've used /nolog and put the connect statement inside the heredoc so that the credentials don't appear in the process list, which is an often-overlooked security issue. If you don't want to do that and do put the credentials as sqlplus username/passwd, you can add the -l flag so that it only tries to log in once; otherwise if login fails for some reason it'll try to use the rest of the heredoc as further credentials, and can appear to get hung up with short scripts.