How to store result from SQLPlus to a shell variable

Question

My requirement is to store the result of an sqlplus operation into a variable in my shell script. I need the result of the following operation which is in my .sh file

sqlplus 'user/pwd' @test.sql

I have already tried

testvar = 'sqlplus 'user/pwd'
@test.sql'

but that doesn't work.

EDIT::

I changed it to

testvar=sqlplus foo/bar@SCHM @test.sql

and it says

SQL*Plus:: not found [No such file or directory]

I tried with

testvar=$(sqlplus foo/bar@SCHM
@test.sql)

and it gives the same error. When I try without the variable assignment like below

sqlplus foo/bar@schm @test.sql

it works fine

In a Bourne-type shell (such as bash), the syntax does not allow for spaces around the =. — glenn jackman
– glenn jackman, Commented Feb 10, 2011 at 15:28

Michael Ballent · Accepted Answer · 2011-02-16 09:16:26Z

20

Try this instead:

testvar=`sqlplus -s foo/bar@SCHM <<EOF
set pages 0
set head off
set feed off
@test.sql
exit
EOF`

-s switch will turn off all the header info when sqlplus launches. You also want to turn off the feedback, headers, and pagesize to 0. I am old school so I still use the back ticks :)

answered Feb 16, 2011 at 9:16

Michael Ballent

1,0889 silver badges15 bronze badges

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

2 Comments

agonen Over a year ago

should add verify off , in case of passing parameters

Nicolas de Fontenay Over a year ago

Thanks. I was looking for a syntax like this. I didn't want to use a sql file.

Jé Queue · Accepted Answer · 2011-02-10 07:31:31Z

12

Employ backticks:

testvar=`sqlplus foo/bar @test.sql`

or should that be of syntactical eyesore:

testvar=$(sqlplus foo/bar @test.sql)

You clearly know to take the right sql*plus commands to limit superfluous output, yes? :) and of course beware the backticking will collapse the whitespace of the output.

edited Feb 10, 2011 at 7:31

answered Feb 10, 2011 at 6:13

Jé Queue

10.7k14 gold badges59 silver badges62 bronze badges

5 Comments

Roman Cheplyaka Over a year ago

"the backticking will collapse the whitespace of the output" -- it won't. It will just remove trailing newlines. Also, there's nothing old-schoolish in using $(), it's rather the other way round.

Dennis Williamson Over a year ago

It's the other way around: backticks are "old school". The whitespace is not lost on assignment. It is lost on output if the variable isn't quoted.

Jé Queue Over a year ago

Yes, you are correct about $() I did actually edit later to add the other option and forgot to switch around.

sarego Over a year ago

I made it testvar=sqlplus foo/bar@SCHM @test.sql and it says SQL*Plus:: not found [No such file or directory] i tried with testvar=$(sqlplus foo/bar@SCHM @test.sql) and it gives the same error. When I try without the variable assignment like sqlplus foo/bar@schm @test.sql it works fine

Jé Queue Over a year ago

PATH and ORACLE_HOME exported?

user332325 · Accepted Answer · 2011-02-10 23:03:00Z

1

As commands within $() are executed in a subshell, make sure you have everything you need for the invocation of sqlplus exported. Right now you obviously stumbled into the PATH issue.

answered Feb 10, 2011 at 23:03

user332325

Comments

Trenton D. Adams · Accepted Answer · 2013-02-20 00:19:45Z

1

The solutions here are all hacks.

Your sql file should look like this...

set termout off
set showmode off
set heading off
set echo off
set timing off
set time off
set feedback 0
set pagesize 0
set embedded ON
set verify OFF

spool courses.sh
SELECT 'term="' || sfrstcr_term_code || '";', 'subj="' || sfrstcr_subj_code || '";' FROM sfrstcr WHERE sfrstcr_pidm = 1234567;
spool off

The following shell script will read and print out the shell environment variables.

while read -r row; do
  eval "$row"
  echo "term=$term"; 
  echo "subj=$subj"; 
done < courses.sh

It's important that all the variables are on one line, as the read command ensures that you can read each DB row per loop.

answered Feb 20, 2013 at 0:19

Trenton D. Adams

1,9612 gold badges16 silver badges21 bronze badges

1 Comment

agonen Over a year ago

@trenton-d-adams termout off - mean no display to terminal . I think it better to leave it on , unless you use spool command

Collectives™ on Stack Overflow

How to store result from SQLPlus to a shell variable

EDIT::

4 Answers 4

2 Comments

5 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT::

4 Answers 4

2 Comments

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related