将选择结果放入ksh变量中 [英] Put select result in a ksh variable

问题描述

使用sql loader，我知道可以在ctl文件中引用ksh变量.例如我可以写

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'
...

我想添加这样的WHEN子句

I would like to add a WHEN clause like this

    WHEN (125:125) = '$P_NUMBER'

P_NUMBER将具有我将通过选择查询检索的表中一列的值.

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

有可能这样做吗?从带有select的列中检索值，然后以某种方式将其放在ksh变量中，以便ctl文件可以看到它. (是否包含sql plus?)

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?)

谢谢

推荐答案

作为基本概述，您可以使用带有Heredoc的SQL * Plus来执行查询，并将输出分配给变量:

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`

用反引号括起来会将结果分配给变量.然后，$ P_NUMBER将保存查询所获得的任何值(例如，如果凭据错误，则会显示一条错误消息).如果您确定查询将恰好返回一个结果，则将有帮助.在尝试使用变量之前，还可以使用$?测试返回代码以查找错误.

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.

包括-s标志，关闭反馈并将页面大小设置为零可共同抑制所有杂音，因此您只需得到结果，而不必去除横幅，标题等.

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.

最后，我使用了/nolog并将connect语句放在heredoc中，以便凭据不会出​​现在进程列表中，这是一个经常被忽视的安全性问题.如果您不想这样做，而是将凭据设置为sqlplus username/passwd，则可以添加-l标志，使其仅尝试登录一次；否则，您可以添加-l标志.否则，如果由于某种原因登录失败，它将尝试使用Heredoc的其余部分作为进一步的凭据，并且似乎会被简短的脚本挂断.

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.

这篇关于将选择结果放入ksh变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！