When using SQLcl with Oracle Database, you can set SQLFORMAT to insert in order to output the query results as INSERT statements.

Example

Here’s an example to demonstrate:

SET SQLFORMAT insert;
SELECT * FROM regions;

Result:

REM INSERTING into REGIONS
SET DEFINE OFF;
Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe');
Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas');
Insert into REGIONS (REGION_ID,REGION_NAME) values (3,'Asia');
Insert into REGIONS (REGION_ID,REGION_NAME) values (4,'Middle East and Africa');

4 rows selected.

Just to be clear, here are the query results when using ansiconsole:

SET SQLFORMAT ansiconsole;
SELECT * FROM regions;

Result:

   REGION_ID               REGION_NAME 
____________ _________________________ 
           1 Europe                    
           2 Americas                  
           3 Asia                      
           4 Middle East and Africa    

Export to a File

If you need to store the INSERT statements in a file, you can use the SPOOL command to export the results to a file.

SET SQLFORMAT insert;
SPOOL '/Users/barney/data/insert_regions.sql';
SELECT * FROM regions;
SPOOL off;
SET SQLFORMAT ansiconsole;

That exported the result to a file called insert_regions.sql at the specified location.

Once the query was exported to a file, I set SPOOL to off and SQLFORMAT to ansiconcole.

Here’s what the resulting file looks like:

REM INSERTING into REGIONS
SET DEFINE OFF;
Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe');
Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas');
Insert into REGIONS (REGION_ID,REGION_NAME) values (3,'Asia');
Insert into REGIONS (REGION_ID,REGION_NAME) values (4,'Middle East and Africa');

4 rows selected. 

Remove Feedback

You can remove the X rows selected with SET FEEDBACK off:

SET SQLFORMAT insert;
SET FEEDBACK off;
SELECT * FROM regions;
SET FEEDBACK on;
SET SQLFORMAT ansiconsole;

Result:

REM INSERTING into REGIONS
SET DEFINE OFF;
Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe');
Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas');
Insert into REGIONS (REGION_ID,REGION_NAME) values (3,'Asia');
Insert into REGIONS (REGION_ID,REGION_NAME) values (4,'Middle East and Africa');

In this case I turned FEEDBACK back on after exporting the file, and set SQLFORMAT back to ansiconsole.