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

Try this

#!/bin/bash

sqlplus / as sysdba <<EOF
spool test.log
set echo on;
set serveroutput on size 10000;
select group#,members,bytes/1024/1024,status from v\$log; spool off;
exit
EOF I have tested, move the spool up.

Cheers

On Tue, Mar 8, 2016 at 3:34 PM, Nan Xiao <xiaonan830818_at_gmail.com> wrote:

> Hi Raza,
>
> "SET TERM ON" still doesn't work.
>
> Hi Binh,
>
> The test.log can display the SQL statement:
>
> SQL> select group#,members,bytes/1024/1024,status from v$log;
>
> GROUP# MEMBERS BYTES/1024/1024 STATUS
> ---------- ---------- --------------- ----------------
> 1 1 102400 CURRENT
> 3 1 102400 UNUSED
> 2 1 102400 INACTIVE
>
> SQL> spool off;
>
>
>
> Best Regards
> Nan Xiao
>
> On Mon, Mar 7, 2016 at 10:01 PM, Le, Binh T. <Binh.Le_at_lfg.com> wrote:
>
>> Try this
>>
>>
>>
>> #!/bin/bash
>>
>>
>>
>> sqlplus / as sysdba <<EOF
>>
>> set echo on;
>>
>> set serveroutput on size 10000;
>>
>> spool test.log
>>
>> select group#,members,bytes/1024/1024,status from v\$log;
>>
>> spool off;
>>
>> exit
>>
>> EOF
>>
>>
>>
>>
>>
>>
>>
>> Then go check out the test.log file.
>>
>>
>>
>> Binh Le
>>
>>
>>
>>
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Raza Siddiqui
>> *Sent:* Monday, March 07, 2016 8:50 AM
>> *To:* xiaonan830818_at_gmail.com
>> *Cc:* Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>; oracle-l <
>> oracle-l_at_freelists.org>
>> *Subject:* Re: Why doesn't output display SQL statement when using
>> sqlplus in bash script?
>>
>>
>>
>> Try SET TERM ON
>>
>> Raza
>>
>>
>> On Mar 7, 2016, at 0:41, Nan Xiao <xiaonan830818_at_gmail.com> wrote:
>>
>> Hi Hermant,
>>
>>
>>
>> I modify my script as this:
>>
>>
>>
>> #!/bin/bash
>>
>>
>>
>> sqlplus / as sysdba <<EOF
>>
>> set echo on;
>>
>> select group#,members,bytes/1024/1024,status from v\$log;
>>
>> exit
>>
>> EOF
>>
>>
>>
>> But it still doesn't output the SQL statement, just more "SQL>":
>>
>>
>>
>> $ ./test.sh
>>
>>
>>
>> SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 03:35:46 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>
>>
>> GROUP# MEMBERS BYTES/1024/1024 STATUS
>>
>> ---------- ---------- --------------- ----------------
>>
>> 1 1 102400 CURRENT
>>
>> 3 1 102400 UNUSED
>>
>> 2 1 102400 INACTIVE
>>
>>
>>
>> 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
>>
>>
>>
>>
>>
>>
>> Best Regards
>>
>> Nan Xiao
>>
>>
>>
>> On Mon, Mar 7, 2016 at 4:21 PM, Chitale, Hemant K <
>> Hemant-K.Chitale_at_sc.com> wrote:
>>
>> When using a script (instead of interactive SQLPlus session) you must add
>> SET ECHO ON at the beginning.
>>
>>
>>
>> Hemant K Chitale
>>
>>
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Nan Xiao
>> *Sent:* Monday, March 07, 2016 4:08 PM
>> *To:* oracle-l_at_freelists.org
>> *Subject:* Why doesn't output display SQL statement when using sqlplus
>> in bash script?
>>
>>
>>
>> Hi all,
>>
>>
>>
>> I am writing a Bash script which using sqlplus to connect Oracle
>> database:
>>
>>
>>
>> #!/bin/bash
>>
>>
>>
>> sqlplus / as sysdba <<EOF
>>
>> select group#,members,bytes/1024/1024,status from v\$log;
>>
>> exit
>>
>> EOF
>>
>>
>>
>> Executing it, the output is:
>>
>>
>>
>> $ ./test.sh
>>
>>
>>
>> SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 02:57:57 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>
>>
>> GROUP# MEMBERS BYTES/1024/1024 STATUS
>>
>> ---------- ---------- --------------- ----------------
>>
>> 1 1 102400 CURRENT
>>
>> 3 1 102400 UNUSED
>>
>> 2 1 102400 INACTIVE
>>
>>
>>
>> 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
>>
>>
>>
>> The SQL statement runs successfully. But I am curious about why the
>> statement doesn't display in output: There
>>
>> is empty after `SQL> `.
>>
>>
>>
>> Best Regards
>>
>> Nan Xiao
>>
>>
>> This email and any attachments are confidential and may also be
>> privileged. If you are not the intended recipient, please delete all copies
>> and notify the sender immediately. You may wish to refer to the
>> incorporation details of Standard Chartered PLC, Standard Chartered Bank
>> and their subsidiaries at
>> https://www.sc.com/en/incorporation-details.html
>>
>>
>>
>> Notice of Confidentiality: **This E-mail and any of its attachments may
>> contain
>> Lincoln National Corporation proprietary information, which is
>> privileged, confidential,
>> or subject to copyright belonging to the Lincoln National Corporation
>> family of
>> companies. This E-mail is intended solely for the use of the individual
>> or entity to
>> which it is addressed. If you are not the intended recipient of this
>> E-mail, you are
>> hereby notified that any dissemination, distribution, copying, or action
>> taken in
>> relation to the contents of and attachments to this E-mail is strictly
>> prohibited
>> and may be unlawful. If you have received this E-mail in error, please
>> notify the
>> sender immediately and permanently delete the original and any copy of
>> this E-mail
>> and any printout. Thank You.**
>>
>
>

-- 
Veerabasaiah C B
"Only put off until tomorrow what you are willing to die having left
undone. - Picasso"

--
http://www.freelists.org/webpage/oracle-l