I have ~130 SQL scripts that INSERT about ~750,000 records into an Oracle 12c database.

I have a master SQL that kicks all this off using this template:

set termout off
set echo off;

delete from table1;
set termout on;
REM ---
REM completed table1_DATA_TABLE.sql
REM ---
set echo off;
set termout off;

<repeat for every script>

The issue is, I'm getting thousands of 1 row inserted. and it's drowning out errors. Is there a way I can suppress the successful INSERT notifications and only leave the errors? Or, is there a way I can retrieve all the errors of a session/script ?

  • I would seriously question a design that inserts 750000 rows using insert statements in a script. How are those scripts generated ? Have you considered generating flat files (CSV) and load from those files using sql*loader ? Or declaring those files as external tables ? Both approaches are an order of magnitude faster than running a massive SQL script. Also safer, manageable and easier to run: both generate a log of errors and throw rejected rows in separate files. – Albert Godfrind Feb 21 at 17:36
  • @AlbertGodfrind, This is an XY Problem. Long story short, expdp and impdp are bugged and an RMAN restore runs into issues. In my frustration, I scripted the database from sql*developer without knowing about sql*loader. I'll look into it, thanks! This is a one-off project so finding the perfect solution isn't as valuable. – Nathan Goings Feb 21 at 18:06
  • "expdp and impdp are bugged". In what way ? Did you report that to Oracle Support ? What did they say ? – Albert Godfrind Feb 22 at 18:14
  • @AlbertGodfrind, The particular 11g version I'm using has a bug with expdp and impdp. The resolution on the Support Document is to upgrade the DB to a new major version. – Nathan Goings Feb 24 at 3:53
  • What’s the bug number ? And what’s the big ? I’m surprised the only solution was to upgrade to a new release (what release ?). Generally bugs are fixed in regular patch sets or minor releases. – Albert Godfrind Feb 25 at 4:17

SET FEEDBACK OFF suppresses the insert notifications.

See the documentation.

You can also use SET ERRORLOGGING to log errors to a table of your choice. Again, see the documentation.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.