将 CHAR 或 CLOB sqlplus 列存储到 shell 脚本变量中

发布于 2024-10-20 08:11:27 字数 973 浏览 3 评论 0原文

当列值包含空格时，我在将列值存储到 shell 脚本变量中时遇到问题，因为所有结果都是按空格而不是实际的列值分割的。

例如，这就是我现在得到的：

set -A SQL_RESULTS_ARRAY `sqlplus -s un/pass@database << EOF
SET ECHO OFF
SET FEED OFF
SET HEAD OFF
SET SPACE 0
SELECT EMAIL_SUBJECT, MAIL_TO FROM EMAIL_TABLE;
EOF`

echo "${SQL_RESULTS_ARRAY[0]}"
echo "${SQL_RESULTS_ARRAY[1]}"

这不起作用，因为 EMAIL_SUBJECT 的值是一个完整的句子，即“消息主题测试”，所以这些回声最终只是打印

Message
subject

而不是

Message subject test
[email protected] [email protected]

基本上，我如何最终只得到数组中的两个项目（每列一个），而不是五个项目（每个单词一个）？通过单个连接就可以实现这一点吗？（我不想每列启动一个新连接）

编辑：另一件事，我的另一个 CLOB 列是 EMAIL_BODY，它基本上可以是任何文本 - 因此我不想有预设的分隔符，因为 EMAIL_BODY 可以有各种逗号、管道、换行符等等......

原文

I'm having trouble storing column values into shell script variables when these include white spaces, since all the results are split on whitespaces instead of actual column values.

For example, this is what I got now:

set -A SQL_RESULTS_ARRAY `sqlplus -s un/pass@database << EOF
SET ECHO OFF
SET FEED OFF
SET HEAD OFF
SET SPACE 0
SELECT EMAIL_SUBJECT, MAIL_TO FROM EMAIL_TABLE;
EOF`

echo "${SQL_RESULTS_ARRAY[0]}"
echo "${SQL_RESULTS_ARRAY[1]}"

This doesn't work because the value of EMAIL_SUBJECT is an entire sentence, ie "Message subject test", so those echos just end up printing

Message
subject

Instead of

Message subject test
[email protected] [email protected]

Basically, how do I end up with only two items in the array (one per column), instead of five items (one per word)? Is this at all possible with a single connection? (I'd rather not start a new connection per column)

EDIT: Another thing, another one of my CLOB columns is EMAIL_BODY, which can basically be any text-- thus I'd rather not have a preset separator, since EMAIL_BODY can have all sorts of commas, pipes, new lines, etc...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

顾忌 2024-10-27 08:11:27

您缺少的关键是将 shell 的 IFS（内部字段分隔符）设置为与查询结果相同。这是一个 ksh 会话：

$ results="Message subject test,[email protected] [email protected]"
$ set -A ary $results
$ for i in 0 1 2 3 4; do print "$i. ${ary[$i]}"; done
0. Message
1. subject
2. test,[email protected]
3. [email protected]
4. 
$ IFS=,
$ set -A ary $results
$ for i in 0 1 2 3 4; do print "$i. ${ary[$i]}"; done
0. Message subject test
1. [email protected] [email protected]
2.
3.
4.

您可能想要执行以下操作：

results=`sqlplus ...`
old_IFS="$IFS"
IFS=,
set -A SQL_RESULTS_ARRAY $results
IFS="$old_IFS
print "${SQL_RESULTS_ARRAY[0]}"
print "${SQL_RESULTS_ARRAY[1]}"

The key you're missing is to set the shell's IFS (internal field separator) to be the same as your query results. Here's a ksh session:

$ results="Message subject test,[email protected] [email protected]"
$ set -A ary $results
$ for i in 0 1 2 3 4; do print "$i. ${ary[$i]}"; done
0. Message
1. subject
2. test,[email protected]
3. [email protected]
4. 
$ IFS=,
$ set -A ary $results
$ for i in 0 1 2 3 4; do print "$i. ${ary[$i]}"; done
0. Message subject test
1. [email protected] [email protected]
2.
3.
4.

You'll probably want to do something like this:

results=`sqlplus ...`
old_IFS="$IFS"
IFS=,
set -A SQL_RESULTS_ARRAY $results
IFS="$old_IFS
print "${SQL_RESULTS_ARRAY[0]}"
print "${SQL_RESULTS_ARRAY[1]}"

回复收藏 0 原文