Re: Why doesn't output display SQL statement when using sqlplus in bash script?
Date: Wed, 9 Mar 2016 06:30:34 -0700
Message-ID: <56E0257A.1020604_at_gmail.com>
In the 'test.sql' I note the command is terminated by a semi-colon. That says 'execute without listing'.
Terminate without semi-colon, but with blank line. Then execute using 'run' command instead of semi-colon or slash.
/Hans
On 09/03/2016 2:58 AM, Nan Xiao wrote:
> Hi all,
>
> Thanks very much for all kind responses for this issue!
>
> I will try to dump the log session to another file for debugging later.
>
> Best Regards
> Nan Xiao
>
> On Wed, Mar 9, 2016 at 4:43 AM, Hans Forbrich
> <fuzzy.graybeard_at_gmail.com <mailto:fuzzy.graybeard_at_gmail.com>> wrote:
>
> Not what I want at all. But if it works for you, then go for it.
> There are usuallyseveral different ways to accompish a task.
>
> /Hans
>
>
> On 08/03/2016 1:14 PM, Veerabasaiah C wrote:
>> Hope this is what you want.
>>
>> $ cat test.bash
>> #!/bin/bash
>>
>> sqlplus / as sysdba <<EOF
>> _at_test.sql
>> exit
>> EOF
>>
>> [~]
>> $ cat test.sql
>> spool test.log
>> set echo on
>> set serveroutput on size 10000;
>> select group#,members,bytes/1024/1024,status from v$log;
>> spool off;
>>
>> [~]
>> $ bash test.bash
>>
>> SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 9 09:12:20 2016
>>
>> Copyright (c) 1982, 2011, Oracle. All rights reserved.
>>
>>
>> Connected to:
>> Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
>> Production
>> With the Partitioning, OLAP, Data Mining and Real Application
>> Testing options
>>
>> SQL> SQL> set serveroutput on size 10000;
>> SQL> select group#,members,bytes/1024/1024,status from v$log;
>>
>> GROUP# MEMBERS BYTES/1024/1024 STATUS
>> ---------- ---------- --------------- ----------------
>> 1 1 512 CURRENT
>> 2 1 512 INACTIVE
>> 3 1 512 INACTIVE
>>
>> SQL> spool off;
>> SQL> Disconnected from Oracle Database 11g Enterprise Edition
>> Release 11.2.0.3.0 - 64bit Production
>> With the Partitioning, OLAP, Data Mining and Real Application
>> Testing options
>>
>> [~]
>> $ cat test.log
>> SQL> set serveroutput on size 10000;
>> SQL> select group#,members,bytes/1024/1024,status from v$log;
>>
>> GROUP# MEMBERS BYTES/1024/1024 STATUS
>> ---------- ---------- --------------- ----------------
>> 1 1 512 CURRENT
>> 2 1 512 INACTIVE
>> 3 1 512 INACTIVE
>>
>> SQL> spool off;
>>
>> [~]
>> $
>>
>> On Wed, Mar 9, 2016 at 5:12 AM, Hans Forbrich
>> <fuzzy.graybeard_at_gmail.com <mailto:fuzzy.graybeard_at_gmail.com>> wrote:
>>
>> What is the content of a.txt?
>>
>> 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
>>>
>>>
>>
>>
>>
>>
>> --
>> Veerabasaiah C B
>> "Only put off until tomorrow what you are willing to die having
>> left undone. - Picasso"
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 09 2016 - 14:30:34 CET