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.