CODEWITHSUNDEEP
linuxbashoracle-databasescriptingsqlplus
Aman Singh
Aman Singh

Reputation: 75

Manipulate spool command output in Bash Scripting

I am running an sql query from a bash script using spool and getting the below output.

#!/bin/bash

/usr/lib/oracle/12.2/client64/bin/sqlplus -s esg/esg << EOF
spool /tmp/test

< SOME SELECT QUERY >

spool off;
EOF
exit;

I am getting output as below:

cat /tmp/test


TABLESPACE_NAME          USED_PCT
------------------------------ ----------
TEMP_TBS1                  23
SYSAUX                     4
GTEMP                      3
SYSTEM                     3
UNDOTBS2                   0
UNDOTBS1                   0
TEMP                       0
USERS                      0
DATA_TBS1                  23
INDEX_TBS1                 11

10 rows selected.

I want to get rid of everything except the values i.e., I do not want header,"--","10 rows selected" and ofcourse blank lines. This would look like below

TEMP_TBS1                  23
SYSAUX                     4
GTEMP                      3
SYSTEM                     3
UNDOTBS2                   0
UNDOTBS1                   0
TEMP                       0
USERS                      0
DATA_TBS1                  23
INDEX_TBS1                 11

Upvotes: 0

Views: 1710

Answers (2)

Paul Hodges
Paul Hodges

Reputation: 15246

I recommend using Alex's solution - but if you do want all that output in your spool log, and just want to strip out the relevant bits for something else, try sed, which will allow layers conditions.

$: sed -n '/^---/,/^$/{ /^[A-Z]/p }' test
TEMP_TBS1                  23
SYSAUX                     4
GTEMP                      3
SYSTEM                     3
UNDOTBS2                   0
UNDOTBS1                   0
TEMP                       0
USERS                      0
DATA_TBS1                  23
INDEX_TBS1                 11

Breaking that down:

sed -n says don't print anything unless explicitly ordered.
/^---/,/^$/ says do the following command on lines in this range only.
{ ... } says apply the contents of the braces when the previous conditions apply, so since the previous condition was a range select, this allows multiple commands to be executed collectively on each line in the range.
/^[A-Z]/p says print only if the first character is a capital letter.

Collectively, all this says "for lines between dashes and a blank line, print if and only if they begin with a capital letter". Everything else will be ignored.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

This isn't really a shell question, but in your heredoc, before the spool, add some SQL*Plus formatting commands; e.g.:

/usr/lib/oracle/12.2/client64/bin/sqlplus -s esg/esg << EOF
set feedback off
set pagesize 0
set trimspool on
spool /tmp/test
...

Setting pagesize to zero "suppress[es] all headings, page breaks, titles, the initial blank line, and other formatting information".

Setting feedback off removes all feedback like '10 rows selected'.

There is a summary of the set commands in the documentation. There is also a section on formatting SQL*Plus reports.

Upvotes: 2

Related Questions