Objective: To invoke sqlplus with sql script file path as parameter from shell prompt
Scenario:
logged in as root and would like to execute the sql script file as oracle
Command:
sudo -u oracle bash -c ". ~/.bash_profile; sqlplus / as sysdba@/tmp/downloads/oracle/instl/script/createschema.sql
Expected: sql commands in createschema.sql are to be executed
Actual: getting only sql prompt
Also, tried:
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
whenever sqlerror exit sql.sqlcode; set echo off; set heading off; /tmp/downloads/oracle/instl/script/createschema.sql; exit; EOF”
but getting error in both a) and b).
Please guide me in invoking sqlplus with sql script file path as parameter from shell prompt.
Advertisement
Answer
Example 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
Output:
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:~>
Example 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
Output:
oracle@esmd:~> ./test_Upper_case.sh username: "Scott" password: "@T!ger" USER is "Scott" 19-02-2020 15:08 Test passed oracle@esmd:~>
Example 3 test.sh
#!/bin/bash sudo -H -E -u oracle -s "/opt/oracle/test_Upper_case.sh"
Output
esmd:~ # ./test.sh username: "Scott" password: "@T!ger" USER is "Scott" 19-02-2020 15:50 Test passed
/opt/oracle/test_Upper_case.sh
!/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