summarising console output from inserts into a docker oracle database

October 3, 2023

I am using the official Oracle 23 Docker image. I have a large amount of test data which needs to be run into the database so it’s available for developers using the image.

I know I can put the .sql scripts into /docker-entrypoint-initdb.d/startup on the image, and they will be run through SQL Plus on the image automatically.

The thing is, I don’t want to see:

db_1      | 1 row created.
db_1      |
db_1      |
db_1      | 1 row created.
db_1      |
db_1      |
db_1      | 1 row created.
db_1      |
db_1      |
db_1      | 1 row created.
db_1      |
db_1      |
db_1      | 1 row created.
db_1      |
db_1      |
db_1      | 1 row created.
db_1      |
db_1      |
(etc...)
db_1      | Commit complete.

In my console output. I’d rather instead just see something like:

db_1      | 10,000 rows created.
db_1      |
db_1      |
db_1      | Commit complete.

Or even batching into groups of 100 or something would greatly reduce the amount of output.

Is this possible?

I have tried using INSERT ALL... but this still prints each insert individually.

The final script should be backwards compatible with 19c if possible.

>Solution :

Option 1: Multiple Inserts (your current solution)

If you do:

INSERT INTO table1 (a, b, c) VALUES (1, 2, 3);

INSERT INTO table1 (a, b, c) VALUES (4, 5, 6);

INSERT INTO table2 (d, e) VALUES (7, 8);

Then you get statements for each row:

1 rows affected
1 rows affected
1 rows affected

Option 2: Multiple row inserts using `INSERT ... SELECT`

If you do:

INSERT INTO table1 (a, b, c)
SELECT 1, 2, 3 FROM DUAL UNION ALL
SELECT 4, 5, 6 FROM DUAL;

INSERT INTO table2 (d, e) VALUES (7, 8);

Then you get statements for each table:

2 rows affected
1 rows affected

Option 3: Multiple row inserts using `INSERT ALL`

If you do:

INSERT ALL
  INTO table1 (a, b, c) VALUES (1, 2, 3)
  INTO table1 (a, b, c) VALUES (4, 5, 6)
  INTO table2 (d, e) VALUES (7, 8)
SELECT 1 FROM DUAL;

Then you get a single statement for all tables:

3 rows affected

Option 4: Wrap inserts in a PL/SQL block

What you can also do is wrap all the INSERT statements in a PL/SQL anonymous block:

BEGIN
  INSERT INTO table1 (a, b, c) VALUES (1, 2, 3);
  INSERT INTO table1 (a, b, c) VALUES (4, 5, 6);
  INSERT INTO table2 (d, e) VALUES (7, 8);
END;
/

Then you get a single statement for success (or failure) of the entire block: