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

"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.**
>

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