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

From: Ghassan Salem <salem.ghassan_at_gmail.com>
Date: Tue, 8 Mar 2016 13:15:58 +0100
Message-ID: <CALEzESif5q3z69Vu-LejYPWSY6zN30S+x8dXjgXNMr+sMDhNLQ_at_mail.gmail.com>

Nan,
it is not sqlplus that does not display the command, try any other linux tool, you'll have the same issue.

bc <<EOF

> 2+3

> quit

> EOF
5

As you can see, bc does not display the 2+3 line.

Regards

On Tue, Mar 8, 2016 at 10:28 AM, Nan Xiao <xiaonan830818_at_gmail.com> 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>
> 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

Received on Tue Mar 08 2016 - 13:15:58 CET