oracle SQLPLUS 不打印受影响的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10082308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLPLUS Doesn't print number of rows affected
提问by Vicky
Im executing below Script in a .sql file. I'm using Windows Command line console to invoke sqlplus. When script terminates everything looks good, except that I couldn't see number of records added by the INSERT statement. You can see the Output also below:
我在 .sql 文件中执行下面的脚本。我正在使用 Windows 命令行控制台来调用 sqlplus。当脚本终止时，一切看起来都不错，只是我看不到 INSERT 语句添加的记录数。您还可以在下面看到输出：
SCRIPT
脚本
WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK
SET FEEDBACK ON
SET VERIFY ON
BEGIN
DBMS_OUTPUT.put_line('Output Nothing');
END;
/
INSERT INTO .........
COMMIT;
QUIT;
/
OUTPUT DISPLAYED
输出显示
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 9 22:08:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64
bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
When I execute the same SQL on a tool like TOAD/SQLNavigator, I could see the number of rows added (See ** marked line below).
当我在 TOAD/SQLNavigator 之类的工具上执行相同的 SQL 时，我可以看到添加的行数（请参阅下面的 ** 标记行）。
OUTPUTDISPLAYED ON TOAD
在蟾蜍上显示的输出
Processing ...
SET FEEDBACK ON
SQL*Plus command ignored.
Processing ...
SET VERIFY ON
SQL*Plus command ignored.
Processing ...
BEGIN
DBMS_OUTPUT.put_line('Doing Nothing');
END;
Doing Nothing
Processing ...
INSERT INTO .......
**11 row(s) inserted**
Processing ...
COMMIT
Processing ...
QUIT;
SQL*Plus command ignored.
Can you tell me which setting probably will help me geting numbers of rows impacted by this SQL, even when I run this script through simple 'sqlplus'?
您能告诉我哪个设置可能会帮助我获得受此 SQL 影响的行数，即使我通过 simple 运行此脚本'sqlplus'？
回答by paxdiablo
SQL*Plus doesn't ouput the row count but you can do it explicitly with something like:
SQL*Plus 不输出行数，但您可以使用以下内容显式执行此操作：
INSERT blah blah blah;
DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
回答by REW
The default threshold for SET FEEDBACK ON is 6. If you want feedback on a fewer number, use "SET FEEDBACK 1".
SET FEEDBACK ON 的默认阈值是 6。如果您希望反馈更少的数字，请使用“SET FEEDBACK 1”。
回答by psaraj12
use
用
set echo on
set autotrace on
which display the command executed and statistics about the statement in this case the number of rows inserted
在这种情况下显示执行的命令和有关语句的统计信息插入的行数
WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK
SET FEEDBACK ON
SET VERIFY ON
set echo on
set autotrace on
BEGIN
DBMS_OUTPUT.put_line('Output Nothing');
END;
/
INSERT INTO .........
COMMIT;
QUIT;
/
回答by Roger Cornejo
rem to see Pl/SQL "print" statements:
set serveroutput on
回答by DCookie
Seems to work for me with the 11g client:
11g 客户端似乎对我有用：
C:\>sqlplus user/pw
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 9 21:12:12 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into testing (select 1,'XX' from dual connect by level < 11);
10 rows created.
SQL>
I didn't think the 10g client worked differently, but I can't test it right now.
我不认为 10g 客户端的工作方式不同，但我现在无法对其进行测试。
EDIT:
编辑：
Works the same with 10g SQLPlus. I ran your exact script today:
与 10g SQLPlus 的工作方式相同。我今天运行了你的确切脚本：
C:\>sqlplus user/pw@db
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 10 09:12:26 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
DB> WHENEVER SQLERROR EXIT 1 ROLLBACK
DB> WHENEVER OSERROR EXIT 1 ROLLBACK
DB> SET FEEDBACK ON
DB> SET VERIFY ON
DB> BEGIN
2 DBMS_OUTPUT.put_line('Output Nothing');
3 END;
4 /
Output Nothing
PL/SQL procedure successfully completed.
DB> insert into xx (select 'AA' from dual connect by level < 10);
9 rows created.
DB> COMMIT;
Commit complete.
DB> QUIT;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\>
It doesn't matter if I insert 1 row or 9. I get the message. I am guessing there's something you're leaving out of your script example. Is the INSERT inside a BEGIN/END block? That would suppress the message.
插入 1 行还是 9 行都没有关系。我收到消息。我猜你在脚本示例中遗漏了一些东西。INSERT 是否在 BEGIN/END 块内？那将抑制消息。
