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@xxxxxxxxx>
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
@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@xxxxxxxxx>
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@xxxxxxxxx>fuzzy.graybeard@xxxxxxxxx> 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/>
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@myhost ~]$ cat test.sh
#!/bin/bash
sqlplus hr/hr@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@myhost ~]$ ./test.sh
list
1 select count(*)
2* from employees
/
COUNT(*)
----------
107
spool off
[oracle@myhost ~]$
....................
/Hans
--
Veerabasaiah C B
"Only put off until tomorrow what you are willing to die having left
undone. - Picasso"
