Put select result in a ksh variable

Question

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 clause like this

    WHEN (125:125) = '$P_NUMBER'

P_NUMBER would have the value of a column in a table that I would retrieve with a select query.

Is it possible to do that ? retrieve a value from a column with a select and somehow put it in the ksh variable so the ctl file can see it. (something with sql plus?)

Thank you

Answer 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
connect username/password
whenever sqlerror exit failure
set pagesize 0
set feedback off
select your_value from your_table where your_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.