如何从shell提示符将oracle脚本文件的参数传递给sqlplus？

Question

目标:从shell提示符调用sqlplus，并以sql脚本文件路径作为参数

场景：

以root身份登录，并希望以oracle的身份执行sql脚本文件。

命令：

sudo -u oracle bash -c ". ~/.bash_profile; sqlplus / as sysdba@/tmp/downloads/oracle/instl/script/createschema.sql

预期:将执行createschema.sql中的 sql命令

实数：只获取sql提示符

此外，已尝试：

a)

sudo -u oracle bash -c ". ~/.bash_profile; sqlplus@/tmp/downloads/oracle/instl/script/createschema.sql / as sysdba

b)

sudo -u oracle bash -c ". ~/.bash_profile; sqlplus -s /as sysdba << EOF 

每当sqlerror退出sql.sqlcode；设置回波关闭；设置标题；sql.sqlcode退出；EOF“

但在a)和b中都有错误。

请指导我使用sql脚本文件路径作为参数从shell提示符调用sqlplus。

Answer 1

示例1

#!/bin/sh

username=\"Scott\"
password=\"@T!ger\"
host=10.x.xx.xxx
port=1521
service=esmd
ezconnect=$host:$port/$service

echo username:  $username
echo password:  $password
echo host:      $host
echo port:      $port
echo service:   $servive
echo ezconnect  $ezconnect

echo -e 'show user \n  select 1 from dual; \n select sysdate from dual; \nexit;' |  sqlplus -s  $username/$password@$ezconnect

输出：

oracle@esmd:~> ./test_echo.sh
username: "Scott"
password: "@T!ger"
host: 10.x.xx.xxx
port: 1521
service:
ezconnect 10.x.xx.xxx:1521/esmd
USER is "Scott"

         1
----------
         1


SYSDATE
---------
19-FEB-20

oracle@esmd:~>

示例2

#!/bin/sh

username=\"Scott\"
password=\"@T!ger\"


echo username:  $username
echo password:  $password

testoutput=$(sqlplus -s $username/$password  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dual;
@ulcase1.sql
exit;
EOF
)

echo $testoutput

输出：

oracle@esmd:~> ./test_Upper_case.sh
username: "Scott"
password: "@T!ger"
USER is "Scott" 19-02-2020 15:08 Test passed
oracle@esmd:~>

示例3 test.sh

#!/bin/bash

sudo  -H -E  -u oracle  -s "/opt/oracle/test_Upper_case.sh"

输出

esmd:~ # ./test.sh
username: "Scott"
password: "@T!ger"
USER is "Scott" 19-02-2020 15:50 Test passed

/opt/oracle/test_opt_case.sh.opt

!/bin/sh

username=\"Scott\"
password=\"@T!ger\"


echo username:  $username
echo password:  $password

testoutput=$($ORACLE_HOME/bin/sqlplus -s $username/$password  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dual;
@/opt/oracle/ulcase1.sql
exit;
EOF
)

echo $testoutput