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

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> 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>
> 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-l