无法在BASH脚本中建立Oracle SQL会话

#!/bin/bash
#Oracle DB Info for NEXT
HOST="1.2.3.4"
PORT="5678"
SERVICE="MYDB"
DB_USER=$(whoami)
DB_PASS=$(base64 -d ~/.passwd)
DB_SCHEMA="my_db"

#Section for all of our functions.
function SQLConnection(){
sqlplus "$DB_USER"/"$DB_PASS"@"$HOST":"$PORT"/"$SERVICE"
}

function Connected(){
SQLConnection <<EOF
select sys_context('USERENV','SERVER_HOST') from dual;
EOF
}

function GetJMS(){
SQLConnection <<EOF
set echo on timing on lines 200 pages 100
select pd.destination from ${DB_SCHEMA}.pd_notification pd where pd.org_id = '$ORGID';
EOF
}
TODAY=$(date +"%A %B %d, %Y")
read -r -p $'\n\nWhat is the ORG ID? ' ORGID
read -r -p $'\n\nWhat is the REMOTE QUEUE MANAGER NAME? ' RQM
read -r -p $'\n\nWhat is the IP address of the REMOTE QUEUE MANAGER? ' CONN
read -r -p $'\n\nWhat is the PORT of the REMOTE QUEUE MANAGER? ' PORT
echo -en "* $(whoami)\n* $TODAY\n* MQ Setup $ORGID\n\nDEFINE +\n\tCHANNEL('$RQM.LQML') +\n\tCHLTYPE(SDR) +\n\tCONNAME('$CONN($PORT)') +\n\tXMITQ('BUF.2.$ORGID.XMQ')\n\tCHAUTH(TLS_RSA_WITH_AES_256_CBC_SHA256)\n\nDEFINE +\n\tCHANNEL('LQML.$RQM') +\n\tCHLTYPE(RCVR) +\n\tTRPTYPE(TCP)\n\nDEFINE +\n\tQLOCAL('$RQM') +\n\tTRIGDATA('LQML.$RQM') +\n\tINITQ('SYSTEM.CHANNEL.INITQ') +\n\tTRIGGER USAGE(XMITQ)\n\n" > ~/mqsetup.mqsc

CONNECTED=$(Connected | awk 'NR==16')
echo -en "\n\nHello From: $CONNECTED\n\n"

for JMSDESTINATION in $(GetJMS | awk 'NR>=16&&NR<=24{print 
 
 
#!/bin/bash
#Oracle DB Info for NEXT
HOST="1.2.3.4"
PORT="5678"
SERVICE="MYDB"
DB_USER=$(whoami)
DB_PASS=$(base64 -d ~/.passwd)
DB_SCHEMA="my_db"

#Section for all of our functions.
function SQLConnection(){
sqlplus "$DB_USER"/"$DB_PASS"@"$HOST":"$PORT"/"$SERVICE"
}

function Connected(){
SQLConnection <<EOF
select sys_context('USERENV','SERVER_HOST') from dual;
EOF
}

function GetJMS(){
SQLConnection <<EOF
set echo on timing on lines 200 pages 100
select pd.destination from ${DB_SCHEMA}.pd_notification pd where pd.org_id = '$ORGID';
EOF
}
TODAY=$(date +"%A %B %d, %Y")
read -r -p $'\n\nWhat is the ORG ID? ' ORGID
read -r -p $'\n\nWhat is the REMOTE QUEUE MANAGER NAME? ' RQM
read -r -p $'\n\nWhat is the IP address of the REMOTE QUEUE MANAGER? ' CONN
read -r -p $'\n\nWhat is the PORT of the REMOTE QUEUE MANAGER? ' PORT
echo -en "* $(whoami)\n* $TODAY\n* MQ Setup $ORGID\n\nDEFINE +\n\tCHANNEL('$RQM.LQML') +\n\tCHLTYPE(SDR) +\n\tCONNAME('$CONN($PORT)') +\n\tXMITQ('BUF.2.$ORGID.XMQ')\n\tCHAUTH(TLS_RSA_WITH_AES_256_CBC_SHA256)\n\nDEFINE +\n\tCHANNEL('LQML.$RQM') +\n\tCHLTYPE(RCVR) +\n\tTRPTYPE(TCP)\n\nDEFINE +\n\tQLOCAL('$RQM') +\n\tTRIGDATA('LQML.$RQM') +\n\tINITQ('SYSTEM.CHANNEL.INITQ') +\n\tTRIGGER USAGE(XMITQ)\n\n" > ~/mqsetup.mqsc

CONNECTED=$(Connected | awk 'NR==16')
echo -en "\n\nHello From: $CONNECTED\n\n"

for JMSDESTINATION in $(GetJMS | awk 'NR>=16&&NR<=24{print $1}')
    do
        read -r -p $'\n\nWhich REMOTE QUEUE NAME matches with this ${JMSDESTINATION}?' RNAME
        QDESC=$(echo "$JMSDESTINATION" | tr '.' ' ' | tr '[[:upper:]]' '[[:lower:]]')
        echo -en "\n\nDEFINE +\n\tQR($JMSDESTINATION) +\n\t\tREPLACE DESCR('$ORGID $QDESC Queue') +\n\t\tREPLACE MAXDEPTH(5000) +\n\t\tXMITQ('BUF.2.$ORGID.XMQ') +\n\t\tRNAME('$RNAME') +\n\t\tRQMNAME('$RQM')" >> ~/mqsetup.mqsc
    done
 
 
Here is the script I've built, hoping to automate the setup of IBM MQ Queues and Channels. My problem is that outside this script, I can establish an SQL Session without an issue, directly from the shell, provided I input the variables seen in the script. I can call the functions and everything returns just as I'd hope it would. When I run the exact same things from within the script, I get timeout errors ... the "Hello From" is blank, which tells me there is no DB connection.
 
 
这是我构建的脚本，希望自动化IBM MQ队列和通道的设置。我的问题是，在这个脚本之外，我可以直接从shell建立一个没有问题的SQL Session，前提是我输入了脚本中看到的变量。我可以调用函数，一切都会像我希望的那样返回。当我从脚本中运行完全相同的东西时，我得到超时错误......“Hello From”是空白的，这告诉我没有数据库连接。
 
  
  
 
I'm totally stumped as to why it all works great from outside the script, but inside it times out.
 
 
我完全难以理解为什么一切都从剧本外部发挥得很好，但在其内部超时。
 
 
I appreciate the eyes and the help!
 
 
我很欣赏眼睛和帮助！
 
 
1 个解决方案
 
 
 
  
#1
 
  
 
  
 
   
1  
 
   
You're overwritng a variable value. You have this at the top of the script:
 
   
你正在覆盖一个变量值。你在脚本的顶部有这个：
 
   
PORT="5678"
 
   
but then later on you do:
 
   
但后来你做了：
 
   
read -r -p $'\n\nWhat is the PORT of the REMOTE QUEUE MANAGER? ' PORT
 
   
which overwrites your 5678 value with whatever is entered there. That port may not be listening on the DB server at all, or may be doing something else, or if you don't enter a value it'll default to port 1521 when you connect. But either way the connection is going to fail, either quickly or slowly depending on the port state (e.g. slower maybe if a firewall blocks it).
 
   
用你输入的任何内容覆盖你的5678值。该端口可能根本没有在数据库服务器上侦听，或者可能正在执行其他操作，或者如果您没有输入值，则在连接时它将默认为端口1521。但无论哪种方式，连接都将根据端口状态快速或缓慢地失败（例如，如果防火墙阻止它，则速度会更慢）。
 
   
If you test the connection by adding a Connected call before the read calls (as I initially did) then it seems to be working fine; but the connections after the reads don't work because port value it tries to connect to is now wrong.
 
   
如果你通过在读取调用之前添加一个Connected调用来测试连接（就像我最初那样）那么它似乎工作正常;但是读取后的连接不起作用，因为它尝试连接的端口值现在是错误的。
 
   
Use a different name for the two variables, e.g. RQ_PORT for the second one - both in its read command and the subsequent creation of the ~/mqsetup.mqsc file.
 
   
为这两个变量使用不同的名称，例如RQ_PORT用于第二个 - 在其read命令和随后创建的〜/ mqsetup.mqsc文件中。
 
   
You may also find it useful to add the -l flag to your SQL*Plus call so that if the connection fails for some reason it won't re-prompt for credentials, which in some circumstances can make the script appear to hang until you hit enter a few times.
 
   
您可能还会发现在SQL * Plus调用中添加-l标志很有用，这样如果连接由于某种原因失败，它将不会重新提示输入凭据，这在某些情况下会使脚本看起来一直挂起，直到您命中输入几次。
 
   
 
   
Not directly relevant to the problem, but when automating anything like this I usually also use the -s flag to suppress the banners (which can vary between environments); and if you're only interested in capturing query output I'd usually set headings and/or pagination off, and feedback off, and generally set SQL*Plus up to generate as little noise as possible - it makes parsing out the interesting bits easier.
 
   
与问题没有直接关系，但在自动执行此类操作时，我通常也会使用-s标志来抑制横幅（可以在不同环境之间变化）;如果你只对捕获查询输出感兴趣，我通常会设置标题和/或分页，并提供反馈，并且通常设置SQL * Plus以产生尽可能少的噪声 - 这样可以更轻松地解析有趣的部分。
 
  
 
 
 

}')
    do
        read -r -p $'\n\nWhich REMOTE QUEUE NAME matches with this ${JMSDESTINATION}?' RNAME
        QDESC=$(echo "$JMSDESTINATION" | tr '.' ' ' | tr '[[:upper:]]' '[[:lower:]]')
        echo -en "\n\nDEFINE +\n\tQR($JMSDESTINATION) +\n\t\tREPLACE DESCR('$ORGID $QDESC Queue') +\n\t\tREPLACE MAXDEPTH(5000) +\n\t\tXMITQ('BUF.2.$ORGID.XMQ') +\n\t\tRNAME('$RNAME') +\n\t\tRQMNAME('$RQM')" >> ~/mqsetup.mqsc
    done
#!/bin/bash
#Oracle DB Info for NEXT
HOST="1.2.