Re: Why doesn't output display SQL statement when using sqlplus in bash script?

If you used my script intact, the list command should have printed the buffer content to that file. It looks like you did not use the redirects and spool that I recommend.

In other words - fill the buffer, do NOT terminate with ';', add blank line, then either "list [newline] / [newline]" or "run [newline]"

/Hans.

On 08/03/2016 2:28 AM, Nan Xiao wrote:
> Hi Veerabasaiah,
>
> It also doesn't work, and the output is:
>
> $ ./test.sh
>
> SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 8 04:18:58 2016
>
> Copyright (c) 1982, 2014, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
> Production
> With the Partitioning, OLAP, Advanced Analytics and Real
> Application Testing options
>
> SQL> SQL> SQL> SQL>
> GROUP# MEMBERS BYTES/1024/1024 STATUS
> ---------- ---------- --------------- ----------------
> 1 1 102400 CURRENT
> 2 1 102400 UNUSED
> 3 1 102400 UNUSED
>
> SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition
> Release 12.1.0.2.0 - 64bit Production
> With the Partitioning, OLAP, Advanced Analytics and Real
> Application Testing options
>
>
> BTW, I use sqlplus in docker container environment, so I am not sure
> whether this is a factor.
>
> Hi Hans,
>
> Thanks for your useful explanations!
>
>
> Best Regards
> Nan Xiao
>
> On Tue, Mar 8, 2016 at 11:35 AM, Hans Forbrich
> <fuzzy.graybeard_at_gmail.com <mailto:fuzzy.graybeard_at_gmail.com>> wrote:
>
> 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
> <mailto: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-l