Set BASH variable based on SQL output

Question

I need to set a BASH variable based on SQL output that gives an e-mail address. As a test I've done:

email=$(echo "SELECT email FROM adr" | sqlplus $user/$pass@$db)

An echo $email shows:

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Oct 25 10:45:06 2013 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production JServer Release 8.1.7.4.0 - Production SQL> 2 Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production JServer Release 8.1.7.4.0 - Production

So I guess I need some sort of trimming to just get the e-mail address?

A manual SELECT as follows shows the complete output:

SQL> SELECT email FROM adr;

EMAIL
--------------------------------------------------------------------------------
[email protected]

What is the simplest way to grab the e-mail address?

stackoverflow.com/questions/15663763/…

Mat
– Mat

2013-10-25 10:02:38 +00:00
Commented Oct 25, 2013 at 10:02 — Mat
– Mat, Commented Oct 25, 2013 at 10:02

dogbane · Accepted Answer · 2013-10-26 12:39:46Z

2

Use -s (silent mode). Also, turn off column headings and feedback as shown below:

email=$(sqlplus -s $user/$pass@$db << EOF
set serveroutput on
set heading off
set feedback off
SELECT email FROM adr;
exit;
EOF
)

edited Oct 26, 2013 at 12:39

answered Oct 25, 2013 at 10:06

dogbane

276k77 gold badges407 silver badges415 bronze badges

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

4 Comments

user2656114 Over a year ago

Thanks but the e-mail address seems to have a line separator before it so it's breaking the script. What's the best way to solve this?

dogbane Over a year ago

Use tr to remove the line break. Change the first line to: email=$(sqlplus -s /nolog $user/$pass@$db << EOF | tr -d '\n'

user2656114 Over a year ago

Thanks. Can I ask what /NOLOG is for? That causes an error message but when it's removed it works as expected.

dogbane Over a year ago

Sorry, you don't need /nolog. It's only used if you do not want to provide a user/passwd on the command line and want sqlplus to prompt for it instead.

Collectives™ on Stack Overflow

Set BASH variable based on SQL output

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related