如何将来自 Oracle 的 SELECT 放入 bash 变量中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17421618/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to put a SELECT from Oracle into a bash variable?
提问by oz380
I try to save the output of an Oracle SELECT command into a bash variable. I tried the following lines but it didn't work really well...
我尝试将 Oracle SELECT 命令的输出保存到 bash 变量中。我尝试了以下几行，但效果不佳......
ACCESS_SQL=`{
sqlplus << EOF
${USER}/${PASSWORD}@DB
set head off;
set feedback off;
set pagesize 5000;
set linesize 30000;
set serveroutput on;
DECLARE
data varchar(5000);
BEGIN
select ACCESS_ID, PROFILE_ID, START_DATE, END_DATE, PLATFORM, ACCESS_TYPE, PERM_FLAG, ACTIVE_FLAG into data from uam.access_list where USER_ID='${USER_ID}';
dbms_output.put_line(data);
END;
/
exit;
EOF
}`
The error statement I get is :
我得到的错误陈述是：
SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 select ACCESS_ID, PROFILE_ID, START_DATE, END_DATE, PLATFORM, ACCESS_TYPE, PERM_FLAG, ACTIVE_FLAG into data from uam.access_list where USER_ID='PZ230';
*
ERROR at line 4:
ORA-06550: line 4, column 110:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored
I was wondering if using a varchar is the right thing to do...
我想知道使用 varchar 是否正确...
回答by dogbane
You don't need to select intoa variable and then use dbms_output.put_lineto print it out. (Your select intostatement won't work anyway, because you can't select multiple columns into a single data variable.)
你不需要select into一个变量，然后用dbms_output.put_line它来打印出来。（select into无论如何，您的语句将不起作用，因为您无法将多个列选择到单个数据变量中。）
Instead, do it like this:
相反，这样做：
data=$(sqlplus -S ${USER}/${PASSWORD} << EOF
set head off
set feedback off
set pagesize 5000
set linesize 30000
select ACCESS_ID, PROFILE_ID, START_DATE, END_DATE, PLATFORM, ACCESS_TYPE, PERM_FLAG, ACTIVE_FLAG from uam.access_list where USER_ID='${USER_ID}';
exit
EOF)
echo "$data"
