Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

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

Shell Programming and Scripting


Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 2 Weeks Ago
newbie_01 newbie_01 is offline
Registered User
 
Join Date: May 2009
Last Activity: 22 May 2017, 5:51 AM EDT
Posts: 225
Thanks: 44
Thanked 0 Times in 0 Posts
Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why

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.


Code:
$ ./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.


Code:
$ 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


Code:
#!/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


Code:
==> 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:


Code:
user01/password01
user02/password02

Please advise if anyone knows what am I doing wrong. Thanks in advance.
Sponsored Links
    #2  
Old Unix and Linux 2 Weeks Ago
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 29 May 2017, 6:37 AM EDT
Location: Aachen, Germany
Posts: 10,830
Thanks: 272
Thanked 3,324 Times in 3,061 Posts
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.
Sponsored Links
    #3  
Old Unix and Linux 2 Weeks Ago
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 26 May 2017, 8:32 PM EDT
Posts: 2,016
Thanks: 19
Thanked 339 Times in 305 Posts
What you are trying to do won't work with this method of file reading:

Code:
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:


Code:
#!/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}<&-

Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Control not returning from Sqlplus to calling UNIX shell script. vikas_trl Shell Programming and Scripting 1 08-04-2015 01:42 AM
How to accept arguments in shell script when calling in perl civilsurfer Shell Programming and Scripting 1 12-11-2011 08:06 PM
calling a unix shell script from sqlplus boopathyvasagam UNIX for Dummies Questions & Answers 2 11-24-2010 01:53 PM
Help supressing spool output from screen when calling sqlplus from script MxC Shell Programming and Scripting 3 08-20-2010 09:34 AM
Problem while calling Oracle 10g SQLPLUS files ganapati UNIX for Advanced & Expert Users 2 05-01-2007 03:03 AM



All times are GMT -4. The time now is 07:39 AM.