How to store sql query output in variable in shell script

Question

I am trying to store the value of sql query output in a variable using shell script.

size=`${PATH_TO_CLIENT}sqlplus $IMPUSER/$IMPPWD@$ENDPOINT<< EOF
select owner, sum(bytes)/1024/1024/1024 Size_GB from dba_segments where owner = 'XXXX' group by owner;
exit;
EOF`
echo "Total data is ${size}"

The output I am getting is

**Total data is**
SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 14 11:06:42 2021
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Fri May 14 2021 11:01:02 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL>
OWNER
--------------------------------------------------------------------------------
   SIZE_GB
----------
XXXXXXX
12.2345


SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

Inside the variable full connection string and sql query output all are getting stored. I just want to get value like $size=12.2345 Please tell me how to get that

you should remove ${PATH_TO_CLIENT} which precedes sqlplus command by properly setting ORACLE_HOME variable. — Barbaros Özhan
– Barbaros Özhan, Commented May 14, 2021 at 16:44

Barbaros Özhan · Accepted Answer · 2021-05-14 16:22:19Z

3

The size value might be assigned to the current variable through use of the following code block

size=$(sqlplus -S /nolog << EOF
 conn $IMPUSER/$IMPPWD@$ENDPOINT
 whenever sqlerror exit sql.sqlcode
 SET PAGES 0
 SELECT SUM(bytes)/1024/1024/1024 FROM dba_segments WHERE owner = 'XXXX';
EOF
)
echo "Total data is "$size

where

keeping owner column and group by clause are redundant as returning only one column value for a single schema
no need to alias the calculated value as not needed for the returning result while hiding the column title through use of SET PAGES 0 command
using direct connection is not safe, but use sqlplus -S /nolog before schema connection in order to hide the password while listed by anbody through ps command.

answered May 14, 2021 at 16:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Pankaj Saini · Accepted Answer · 2021-05-16 13:01:03Z

3

You can use this:

size=`${PATH_TO_CLIENT}sqlplus -s $IMPUSER/$IMPPWD@$ENDPOINT <<EOF
set echo off
set feedback off
set heading off
set pages 0
select sum(bytes)/1024/1024/1024 Size_GB from dba_segments where owner = 'SYS';
exit;
EOF`
echo "Total data is ${size}"

edited May 16, 2021 at 13:01

answered May 14, 2021 at 16:22

Pankaj Saini

1,1941 gold badge7 silver badges4 bronze badges

2 Comments

naruto uzumaki Over a year ago

this worked but the value was coming in newline after the total data statement Total data is = 11.2105713

Pankaj Saini Over a year ago

Just add one more line after set heading off "set pages 0". I have updated the answer above.

MichalH · Accepted Answer · 2021-05-14 15:52:28Z

1

If the output is consistent with newlines, you could use:

size=`${PATH_TO_CLIENT}sqlplus $IMPUSER/$IMPPWD@$ENDPOINT<< EOF | sed -n '/^\s*SIZE_GB$/{n;n;n;p}'
select owner, sum(bytes)/1024/1024/1024 Size_GB from dba_segments where owner = 'XXXX' group by owner;
exit;
EOF`

It will return the third line after line which contains 'SIZE_GB'.

answered May 14, 2021 at 15:52

MichalH

1,0849 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to store sql query output in variable in shell script

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related