Re: Why doesn't output display SQL statement when using sqlplus in bash script?
From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Mon, 7 Mar 2016 20:35:12 -0700
Message-ID: <56DE4870.7040005_at_gmail.com>
On 07/03/2016 8:25 PM, Hans Forbrich wrote:
> SQLPlus was originally a report generator. The idea of listing the
> command would generally distract from the report and so would not be
> as great a priority as simply executing the command. There are an
> incredible number of useful reporting capabilities in the tool, and it
> saddens me greatly that there are so few people who actually know and
> understand the power of SQL*Plus, SQL*Developer and now SQLcl (which
> does everything that SQLPlus does and a whole lot more - see
> http://www.thatjeffsmith.com/archive/tag/sqlcl/.
>
> To answer your question ... bear with me, some concept stuff is included:
>
> When you type a command into SQLPlus, it is entered into a command
> buffer. There are several ways to 'close' that buffer:
>
> - A semi-colon, while watching for command block nesting;
> - A blank line, if not in a command block
>
> A semi-colon on a simple SQL statement is interpreted as 'close the
> command buffer, and execute the command'
>
> A blank line on a simple SQL statement is interpreted as 'close the
> command buffer but do NOT execute'
>
> Whatever is currently in the command buffer may be re-executed using
> either
>
> - / (slash), which means execute without listing
> - 'RUN', which means LIST and then execute.
>
> The LIST command is the inverse of '/' and simply lists the buffer
> without executing.
>
> There are also a number of crude, but effective, commands useful for
> editing the buffer, including:
> - # (buffer line number) makes that the current line;
> - ap appends to the end of the current line
> - del deletes the current line
> - in inputs a new line(s) after the current one
> - c/old/new/ replaces old string with new string, using / as separator
> (fist char after c is separator)
>
> These things are discussed in the SQL*Plus manual. Refer to
> http://docs.oracle.com/database/121/SQPUG/ch_four.htm#SQPUG383 and
> http://docs.oracle.com/database/121/SQPUG/ch_twelve001.htm#SQPUG023
>
> HTH
> /Hans
[oracle_at_myhost ~]$ cat test.sh
#!/bin/bash
sqlplus hr/hr_at_localhost:1521/samples.example.com <<EOF >>a.log 2>&1 set sqlprompt " "
set echo off
select count(*)
from employees
107
Date: Mon, 7 Mar 2016 20:35:12 -0700
Message-ID: <56DE4870.7040005_at_gmail.com>
On 07/03/2016 8:25 PM, Hans Forbrich wrote:
> SQLPlus was originally a report generator. The idea of listing the
> command would generally distract from the report and so would not be
> as great a priority as simply executing the command. There are an
> incredible number of useful reporting capabilities in the tool, and it
> saddens me greatly that there are so few people who actually know and
> understand the power of SQL*Plus, SQL*Developer and now SQLcl (which
> does everything that SQLPlus does and a whole lot more - see
> http://www.thatjeffsmith.com/archive/tag/sqlcl/.
>
> To answer your question ... bear with me, some concept stuff is included:
>
> When you type a command into SQLPlus, it is entered into a command
> buffer. There are several ways to 'close' that buffer:
>
> - A semi-colon, while watching for command block nesting;
> - A blank line, if not in a command block
>
> A semi-colon on a simple SQL statement is interpreted as 'close the
> command buffer, and execute the command'
>
> A blank line on a simple SQL statement is interpreted as 'close the
> command buffer but do NOT execute'
>
> Whatever is currently in the command buffer may be re-executed using
> either
>
> - / (slash), which means execute without listing
> - 'RUN', which means LIST and then execute.
>
> The LIST command is the inverse of '/' and simply lists the buffer
> without executing.
>
> There are also a number of crude, but effective, commands useful for
> editing the buffer, including:
> - # (buffer line number) makes that the current line;
> - ap appends to the end of the current line
> - del deletes the current line
> - in inputs a new line(s) after the current one
> - c/old/new/ replaces old string with new string, using / as separator
> (fist char after c is separator)
>
> These things are discussed in the SQL*Plus manual. Refer to
> http://docs.oracle.com/database/121/SQPUG/ch_four.htm#SQPUG383 and
> http://docs.oracle.com/database/121/SQPUG/ch_twelve001.htm#SQPUG023
>
> HTH
> /Hans
All that having been said, I find that I normally spool the results output to a file and cat that file. In part because I spool the whole SQL session to a log file for error analysis.
By way of example:
...................
[oracle_at_myhost ~]$ cat test.sh
#!/bin/bash
sqlplus hr/hr_at_localhost:1521/samples.example.com <<EOF >>a.log 2>&1 set sqlprompt " "
set echo off
select count(*)
from employees
spool a.txt
list
/
spool off
exit
EOF
cat a.txt
...............
which yields
[oracle_at_myhost ~]$ ./test.sh
list
1 select count(*)
2* from employees
/
COUNT(*)
107
spool off
[oracle_at_myhost ~]$
....................
/Hans
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 08 2016 - 04:35:12 CET