2

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.

1 Answer 1

3

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
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.