How to collect sqlplus errors?

Question

Oracle

Hello!
There is a 4Gb file with inserts.
Some inserts are not executed - and the log file is clogged with "1 row inserted" It is
necessary to find out all the inserts with errors - in order to understand how to fix them.
Is there any setting in sqlplus to make success messages or send errors to a separate file?
Thanks

Answer 1

Once upon a time I created triggers that log errors. Now I quickly threw a trigger for your purposes only on the schema (you can change it to on database yourself):
orasql.org/scripts/errlog_ddl.sql
If this script does not give errors after execution, then turn on the trigger and run your script. Example:
1. Set the trigger:

SQL> @tests/errlog_ddl.sql

Table created.


Sequence created.


Trigger created.

No errors.

2. Since there were no errors at the previous stage, we turn on the trigger:

SQL> alter trigger tr_errorlog enable;

Trigger altered.

3. We check by performing an insert into a non-existent table:

SQL> insert into nonexistent values(1);
insert into nonexistent values(1)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

4. We look at the errors:

SQL> col err_sql format a50
SQL> select id,err_level, err_sql from errorlog;

        ID  ERR_LEVEL ERR_SQL
---------- ---------- -----------------------------------
         1          1 insert into nonexistent values(1)

Answer 2

Unfortunately sqlplus doesn't have a separation between stdout and stderr. Those. there is simply nothing to redirect. You can only parse the resulting log.
PS IMHO, if Oracle has a lot of ways to import and export data, it's also worth changing something in the conservatory so as not to drive gigabyte files with inserts anymore :)

Answer 3

grep would be very useful to you

Answer 4

I'm sorry on Stackoverflow, but there are two answers that seem to suit you:
stackoverflow.com/a/12074039/497055
stackoverflow.com/a/13016411/497055

Answer 5

Alternatively, you can install
spool \home\tmp\sqlplus.log
and then hack this file.