how can you add multiple rows in sqlcl?

Question

i am trying to add multiple rows in my table. i tried to follow some of the online solutions but i keep getting ORA-00933: SQL command not properly ended. how do i add multiple rows at once.

insert into driver_detail values(1003,'sajuman','77f8s0990',1), (1004,'babu ram coi','2g64s8877',8);

Answer 1

INSERT ALL is one way to go.

SQL> create table driver_detail (id integer, text1 varchar2(20), text2 varchar2(20), some_num integer);

Table DRIVER_DETAIL created.

SQL> insert all
  2  into driver_detail (id, text1, text2, some_num) values (1003, 'sajuman', '77f8s0090', 1)
  3  into driver_detail (id, text1, text2, some_num) values (1004, 'babu ram coi', '2g64s887', 8)
  4* select * from dual;
2 rows inserted.

SQL> commit;

Commit complete.

SQL> select * from driver_detail;
     ID           TEXT1        TEXT2    SOME_NUM 
_______ _______________ ____________ ___________ 
   1003 sajuman         77f8s0090              1 
   1004 babu ram coi    2g64s887               8 

But SQLcl is a modern CLI for the Oracle Database, surely there might be a better way?

Yes.

Put your rows into a CSV.

Use the LOAD command.

SQL> delete from driver_detail;

0 rows deleted.

SQL> help load
LOAD
-----

Loads a comma separated value (csv) file into a table.
The first row of the file must be a header row.  The columns in the header row must match the columns defined on the table.

The columns must be delimited by a comma and may optionally be enclosed in double quotes.
Lines can be terminated with standard line terminators for windows, unix or mac.
File must be encoded UTF8.

The load is processed with 50 rows per batch.
If AUTOCOMMIT is set in SQLCL, a commit is done every 10 batches.
The load is terminated if more than 50 errors are found.

LOAD [schema.]table_name[@db_link] file_name
SQL> load hr.driver_detail /Users/thatjeffsmith/load_example.csv
--Number of rows processed: 4
--Number of rows in error: 0
0 - SUCCESS: Load processed without errors
SQL> select * from driver_detail;
     ID             TEXT1          TEXT2    SOME_NUM 
_______ _________________ ______________ ___________ 
   1003 'sajuman'         '77f8s0990'              1 
   1004 'babu ram coi'    '2g64s8877'              8 
      1  'hello'           'there'                 2 
      2  'nice to'         'meet you'              3 


SQL>