Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why
# 1  
Old 05-12-2017
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.
# 2  
Old 05-12-2017
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  
Old 05-13-2017
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}<&-

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calling Oracle stored procedure from ksh script

Friends, I'm newbie with ksh so wanting some help.... 1. I'm trying to call oracle stored procedure from ksh script by taking variable value from runtime, feed into script and execute procedure. 2. Put name1 and name2 value from script run replacing $3 & $4 I'm trying to put name1 in... (4 Replies)
Discussion started by: homer4all
4 Replies

2. Shell Programming and Scripting

How to accept arguments in shell script when calling in perl

I have a shell script like this: #!/bin/sh $PYTHON MetarDecoder.py < ../data/mtrs/arg1/arg2 And I'm calling it with this in perl: my $output = `./metar_parse.sh --options`; It's successful when I put in actual values for arg1 and arg2 in the shell script, but I'd like to pass arguments... (1 Reply)
Discussion started by: civilsurfer
1 Replies

3. Shell Programming and Scripting

Calling SQL script from ksh job and send mail on some error.

Hi, I am trying to call sql script from ksh job with parameters.The parameters passed from ksh job will be used in SELECT query in sql file to SPOOL the data in extract file.My questions are: 1) How to call a sql script from ksh job with parameters? 2) How to use the parameter in sql file to... (1 Reply)
Discussion started by: anil029
1 Replies

4. Shell Programming and Scripting

calling a sql file in my shell script

Hi, I want to call a sql file in my shell script. see the below code:- if ] then ( isql -U${S_USER} -S${S_SERV} -w100 -b -h0 <<ENDSQL | sed -e "s/Password://" ${S_PWD} set nocount on go use ${S_DB} go // need to call a file name... (16 Replies)
Discussion started by: dazdseg
16 Replies

5. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

6. UNIX for Advanced & Expert Users

Calling sql file from shell script

Hi I have a shell script that call a sql file. The sql file will create a spool file. My requirement is, when ever i get an OS error like file not found. I have to log it in a log file. Could some who worked in a like scenario help me by giving the code sample. Many Thanks.. (1 Reply)
Discussion started by: chintapalli001
1 Replies

7. Shell Programming and Scripting

calling a PL/SQL stored procedure from KSH

Hi I have a stored procedure which should be called from KSH. Could ayone please help me with this. Thanks (1 Reply)
Discussion started by: BlAhEr
1 Replies

8. Shell Programming and Scripting

calling sql file from shell script

Hello everybody I need help calling sql file from shell script. Can anyone help me creating a small shell script which calls an sql file . The .sql file should contain some select statements like select emp_no from emp_table; select emp_id from emp_table; And the results should be... (6 Replies)
Discussion started by: dummy_needhelp
6 Replies

9. Shell Programming and Scripting

Problem with Calling sql file from shell script

I have created abc.sh file which will set the environment variables (UNIX env variables as well as ORACLE required variables like ORACLE_SID,ORACLE_HOME etc) and then calls a function file which checks for starts some logs and then it will try to execute the .sql file. The .sh, function file are as... (1 Reply)
Discussion started by: sskc
1 Replies

10. UNIX for Advanced & Expert Users

Problem while calling Oracle 10g SQLPLUS files

Hi all, Iam facing a lot of problem while calling Oracle 10g SQLPLUS files from shell. What is the standard procedures to be taken care. Any help would be useful for me. Thanks in advance, Ganapati. (2 Replies)
Discussion started by: ganapati
2 Replies
Login or Register to Ask a Question