How to pass input from shell script to sqlplus?

Question

I have been asked to change a part of the existing SQLPLUS script to a shell script to allow for more options.

Here is what the original SQLPLUS script was prompting for:

SPOOL results.txt;
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
PROMPT
PROMPT This script upgrades the database to the most recent version of eV8.0.
SET VERIFY OFF;
SET SERVEROUTPUT ON;

ACCEPT continue_flag CHAR PROMPT 'Are You ready to continue Y/N? '
PROMPT

My shell script now looks like :

 #!/bin/sh

echo "Please enter database username"
read eval_user
echo "Please enter database password"
read eval_pass
echo "Please enter the database name"
read db_name

$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF >> ${LOGFILE}
connect $eval_user/$eval_pass@$db_name

set serveroutput on format wrapped
set pages 0 lines 300 trimout on trimspool on
set echo off feedback off sqlprompt ''

SPOOL results.txt;
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
PROMPT
SET VERIFY OFF;
SET SERVEROUTPUT ON;

ACCEPT continue_flag CHAR PROMPT 'Are You ready to continue Y/N? '
PROMPT

I get this error when the SQLPLUS prompt line is reached:

Are You ready to continue Y/N? You have entered an invalid response. Exiting.
BEGIN
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 13

I am new to sqlplus, so I know it is something to do with the settings I set in the beginning, if I set echo off feedback off sqlprompt ' '. But, on removing the line, the script just hangs.

Should I take the prompts completely out of SQLPLUS and use it only in the shell part?

One trick I use to use with DB scripts is to just get a script to generate the SQL and then pipe it through sqlplus when I wanted to execute it. The benefit being that the script generator can be complicated using all the power of Python/Perl. It's analogous to something like echo rm afile.txt checking the output and then doing echo rm afile.txt | sh when ready to commit. — sotapme
– sotapme, Commented Feb 5, 2013 at 20:20

user123664 · Accepted Answer · 2013-02-05 20:34:45Z

2

Easiest is to keep the interactions from the sql script. It does want to read something that you currently do not give it. The here document is a fine way to pass variables. Don't forget the effect that it has on shell special characters ... As always, there are more ways to do the same.

But yes, the answer on your question: YES, take the interactions out and prepare the decisions in the calling shell.

answered Feb 5, 2013 at 20:34

user123664

Sign up to request clarification or add additional context in comments.

3 Comments

roymustang86 Over a year ago

Is there anyway to redirect the outputs from the SQLPLUS script onto the screen when the shell script is run? Or, is spool our only option?

user123664 Over a year ago

yes, you can spool to the standard output, no problem, just don't redirect. If you want to catch specific results, use spool for that. As long as you do not set termout off, output is written to stdout.

roymustang86 Over a year ago

How do I catch the errors that are produced in the sqlplus? Like ORA's?

Collectives™ on Stack Overflow

How to pass input from shell script to sqlplus?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related