Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why

Shell Programming and Scripting
1

Hi,

I am trying to write a script that calls an Oracle SQL file who in turns call another SQL file. This same SQL file has to be run against the same database but using different username and password at each loop.

The first SQL file is basically a connection test and it is supposed to sort of pause and the user is to press enter to continue or CTRL-C to exit. CTRL-C wouldn't be the right choice, I will be changing to a Y or N preferably at some point, at the moment, the main problem is getting the script to prompt/pause.

Below is a sample run of the scripts, I have to remove the real username, password and database.

Here it is running the UNIX script. I am expecting it to prompt/pause but it didn't. The script call sqlplus to run x_main.sql that is supposed to prompt for a user input before running the x_test.sql script

For the run below, it run without prompting/pausing. It didn't even run the script twice like we are expecting too since the while loop has two username/password entries to read.

$ ./x_main.ksh
- username => [username] // pasword => [password]

SQL*Plus: Release 10.2.0.5.0 - Production on Fri May 12 20:56:43 2017

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

'--- Run this script with nolog option in SQLPLUS ------------'
------ CONNECTING TO TARGET SCHEMA as OWNER ---------
Connected.
Enter if connected successfuly, else Ctrl+C to abortUSER is "[username]"

GLOBAL_NAME
--------------------------------------------------------------------------------
TESTDB.TESTDB.COM

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

Here it is not using x_main.ksh. We run sqlplus from the command line instead and run x_main.sql as below. This time it prompt/pause as we've expected.

$ sqlplus /nolog @x_main.sql [username] [password] TESTDB.TESTDB.COM

SQL*Plus: Release 10.2.0.5.0 - Production on Fri May 12 20:56:50 2017

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

'--- Run this script with nolog option in SQLPLUS ------------'
------ CONNECTING TO TARGET SCHEMA as OWNER ---------
Connected.
Enter if connected successfuly, else Ctrl+C to abort
USER is "[username]"

GLOBAL_NAME
--------------------------------------------------------------------------------
TESTDB.TESTDB.COM

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

Below is the x_main.ksh script

#!/bin/ksh

sqlfile="x_main.sql"
env="TESTDB.TESTDB.COM"
while read line
do
   username=`echo $line | awk -F"/" '{ print $1 }'`
   password=`echo $line | awk -F"/" '{ print $2 }'`

   echo "- username => $username // pasword => $password"

   sqlplus /nolog @${sqlfile} $username $password $env
done < x_schema.txt

Below are the .sql file

==> x_main.sql <==
prompt '--- Run this script with nolog option in SQLPLUS ------------'
SET SERVEROUTPUT ON
spool x.log

define env=&3
define schema=&1
define pass=&2
DEFINE logFile = '_Patch.log'
spool &schema&logFile
prompt ------ CONNECTING TO TARGET SCHEMA as OWNER ----------

connect &schema/&pass@&env
accept continue prompt "Enter if connected successfuly, else Ctrl+C to abort"

----
---- Run .sql file here
----
@@x_test.sql

spool off
SET SERVEROUTPUT OFF
QUIT;

==> x_test.sql <==
show user
select * from global_name;

x_schema.txt contains username/password it has two entries when I ran the x_main.ksh script. Its entries are in the form like below:

user01/password01
user02/password02

Please advise if anyone knows what am I doing wrong. Thanks in advance.

2

Without digging deeper, it looks like x_main.ksh 's stdin is redirected from x_schema.txt so there's no channel to the user's terminal.

3

What you are trying to do won't work with this method of file reading:

while read line
do
    # something
done < myfile

because the next line from "myfile" will be fed as a response to the "accept" command in the sqlplus script within the loop.

You will have to read your file using the file descriptor method.
Change your "x_main.ksh" to something like this:

#!/bin/ksh
sqlfile="x_main.sql"
env="TESTDB.TESTDB.COM"
FILENAME="x_schema.txt"
# open file for reading; assign descriptor
exec {FD}<${FILENAME}
while read -u ${FD} LINE
do
    username=`echo ${LINE} | awk -F"/" '{ print $1 }'`
    password=`echo ${LINE} | awk -F"/" '{ print $2 }'`
    echo "- username => $username // pasword => $password"
    sqlplus /nolog @${sqlfile} $username $password $env
done
# close file
exec {FD}<&-