配置SQL * Plus以仅返回数据 [英] Config SQL*Plus to return nothing but data
问题描述
我需要编写一个简单的shell函数，该函数从Oracle DB返回单个字段.例如，将其想象为SELECT 'ABC' FROM dual;，而我追求的是ABC.
I need to write a simple shell function that returns a single field from an Oracle DB. Think of it as for example SELECT 'ABC' FROM dual; and ABC is what I am after.
这是我的功能:
function getSomeOraVal
{
sqlplus $USER/$PASSWD@$ORADB<<!!
SET sqlprompt ''
SET sqlnumber off
SET verify off
SET pages 0
SET echo off
SET head on
SET feedback off
SET feed off
SET serveroutput on
SET escape '\'
VARIABLE v_someVal VARCHAR2(30);
BEGIN
SELECT 'ABC' INTO v_someVal FROM dual;
END;
/
SELECT :v_someVal FROM dual;
!!
}
但是，我想将sqlplus输出(仅数据->'ABC')通过管道传递到一个shell变量中，然后该函数返回该函数，并可以从其他shell脚本中调用该变量.不幸的是，sqlplus返回了大量垃圾，例如顶部是"Welcome to Oracle"，底部是"Disconnected ...".
However, I want to pipe the sqlplus output (data only -> 'ABC') into a shell variable, which the function then returns and can be called from other shell scripts. Unfortunately, sqlplus returns a whole lot of garbage, such as "Welcome to Oracle" on top and "Disconnected..." in the bottom.
我如何仅从SQL结果集中提取数据，或者在这种情况下如何从单个值中提取数据，然后将其传递到UNIX变量中，以便在Shell中进行进一步的处理?
How can I extract just the data from a SQL result set, or in this case a single value and pass it into a UNIX variable for further manipulation within the shell?
谢谢
推荐答案
在一种常见的方法是，除了要返回的值(在Tom的示例中，是字符串"KEEP")之外，还选择一个常量标记，然后使用sed(或您最喜欢的命令行解析器)提取您实际感兴趣的数据
One common approach is to select a constant token in addition to the value that you want to return (in Tom's example, that is the string "KEEP") and then use sed (or your favorite command-line parser) to extract the data you're actually interested in
#!/bin/ksh
x=`sqlplus / <<endl | grep KEEP | sed 's/KEEP//;s/[ ]//g'
select 'KEEP' , max(sal) from emp;
exit
endl`
echo the answer is $x
Other approaches, such as approaches that allow you to read multiple lines of output are also discussed in that thread.
如果您不想打印标题，则应指定
If you don't want the header to be printed, you should be specifying
set head off
在您的SQL * Plus脚本中-如果您不希望标题，我不确定为什么要在脚本中显式设置标题...是否要保留标题的某些部分?
in your SQL*Plus script-- I'm not sure why you're explicitly setting the header on in the script if you don't want the header... Do you want to keep some part of the header?
这篇关于配置SQL * Plus以仅返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
