在sqlplus comman中传递变量user，pass，sid [英] Passing variable user,pass,sid in sqlplus comman

问题描述

我想在我的bash脚本中的sqlplus命令中传递变量，可能会给出答案，但对我不起作用.

I want to pass variables in sqlplus command in my bash script, answer might be given but doesn't work for me.

我尝试过类似的事情

#!/bin/bash
ssh oracle@10.116.12.26 <<XX
echo Please enter an Oracle Username:
read USERNAME

echo "Please enter the Oracle Username's Password:"
read -s PASS 

SID=XE

export conn_str=$USERNAME/$PASS@$SID

sqlplus $conn_str  << EOF
select * FROM tabs;

exit
EOF
XX

也尝试过

sqlplus $USERNAME/$PASS@SID            #with option -s  and -l

我还找到了此 但对我没用.

I also find solution like this but not worked for me.

oracle:11克 ，os:浅顶软呢帽18

oracle : 11g ,os : fedora 18

有没有解决方案? 谢谢.

Is there any solution available for this ? thanks.

推荐答案

您尚未说出实际发生的情况，但是我想您可能没有被提示输入凭据，也许找不到.在Red Hat盒子上，它可以工作:

You haven't said what actually happens, but I'm guessing you aren't prompted for the credentials, and that maybe it can't find sqlplus. On a Red Hat box this works:

#!/bin/bash
echo Please enter an Oracle Username:
read USERNAME
echo "Please enter the Oracle Username's Password:"
read -s PASS
SID=XE
conn_str=$USERNAME/$PASS@$SID

ssh oracle@10.116.12.26 << EOF
# set these for your specific environment
ORACLE_HOME=<path to ORACLE_HOME>
PATH=$PATH:$ORACLE_HOME/bin # or without .../bin depending on client
TNS_ADMIN=<path to tnsnames.ora directory, if not default>

sqlplus -s /nolog
connect $conn_str
select * FROM user_tables;
exit
EOF

这是在本地计算机上从用户那里收集值，以避免出现因为stdin不是终端而不会分配伪终端"的问题.

This is collecting the values from the user on the local machine to avoid the 'Pseudo-terminal will not be allocated because stdin is not a terminal' issue.

然后在远程服务器上一次设置Oracle环境-您需要设置的内容取决于您使用的是哪个客户端(特别是您是否正在使用即时客户端，即使您以似乎不太可能，您可能可以运行oraenv).

It is then setting up the Oracle environment once on the remote server - what you need to set depends on which client you're using (particularly whether you're using the instant client, though if you're connecting as oracle that seems unlikely, and you can probably run oraenv).

我还对其进行了调整，使其先运行/nolog然后再运行connect，然后再运行SQL * Plus，因此凭据不会显示在ps输出中.我已经从旧的tabs切换到了更常见的user_tables.

I've also tweaked it to run SQL*Plus with /nolog and then connect once running, so the credentials aren't exposed in the ps output. And I've switched from the old tabs to the more common user_tables.

嵌套的heredocs可以工作，但不是必需的； SQL命令已经在正确的位置输入，并且将由SQL * Plus而不是远程Shell看到.

The nested heredocs work but aren't necessary; the SQL commands are already being entered in the right place and will be seen by SQL*Plus rather than the remote shell.

当然，由于我不知道您实际看到的是什么错误，很大程度上是推测.将客户端本地安装并使用SQL * Net连接而不是SSH可能会更简单，但是可能存在我们不了解的防火墙限制.

Of course, since I don't know what error you were actually seeing this is largely speculation. It would probably be simpler to have the client installed locally and use a SQL*Net connection rather than SSH, but there could be firewall restrictions we don't know about.

这篇关于在sqlplus comman中传递变量user，pass，sid的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！