SQLPLUS Doesn't print the number of affected rows
I'm running below Script in a .sql file. I am using windows command line console to invoke sqlplus. When the Script finishes, everything looks fine, except that I couldn't see the number of records added by the INSERT statement. You can also see the Output:
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;
/
DISPLAY OUTPUT
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 run the same SQL on a tool like TOAD / SQLNavigator I could see how many rows were added (see ** marked line below).
OUTPUTDISPLAYED TO 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 parameter will probably help me get the number of rows affected by this SQL even when I run this Script through a simple one 'sqlplus'
?
source to share
Seems to work for me with 11g client:
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.
EDIT:
Works the same with 10g SQLPlus. I ran your exact script today:
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 line or 9. I get a message. I am guessing something that you are missing from your example script. Is INSERT inside a BEGIN / END block? This will suppress the message.
source to share
use
set echo on
set autotrace on
which displays the command executed and the statistics about the statement in this case is 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;
/
source to share