0

I would like to write PL/SQL generator of dummy data (e.g. names as combination of first and last most popular names) for Oracle 12c.

So I need to populate names and surnames tables with source data first.

I cannot use sqlldr and all I have access to is SQL*Plus and SQLDeveloper.

I can populate my source tables with lots of individual insert statements like:

INSERT INTO names(id, name) VALUES(1, 'Oliver');
INSERT INTO names(id, name) VALUES(2, 'Jack');
⋮
INSERT INTO names(id, name) VALUES(50, 'Aaron');

I wonder whether there are any other (perhaps more elegant) options.

Edit:

I was hitting another issue coming from a fact that value of generated identity column is not incremented during INSERT ALL. There is a nice workaround described in this related answer at SO.

Community
  • 1
  • 1
Mr. Tao
  • 843
  • 8
  • 17
  • You could use [`INSERT ALL`](https://www.techonthenet.com/oracle/questions/insert_rows.php) – Gurwinder Singh Jan 23 '17 at 17:52
  • @GurV like this? `INSERT ALL INTO names (id, name) VALUES (1, 'Oliver’) INTO names (id, name) VALUES (2, 'Jack’) ⋮ INTO names (id, name) VALUES (50, 'Aaron’) SELECT * FROM dual;` – Mr. Tao Jan 23 '17 at 17:57

2 Answers2

0

You can do this in oracle:

INSERT INTO names(id, name)
select 1, 'Oliver' from dual union all
select 2, 'Jack' from dual union all
. . .

Then there is INSERT ALL:

INSERT ALL 
  INTO names (id, name) VALUES (1, 'Oliver')
  INTO names (id, name) VALUES (2, 'Jack')
select * from dual;

I like the first one better.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • How do I use first form with table with generated id (like `create table names( id number generated always as identity primary key, name varchar2(50) not null);` I am getting _ORA-01400 cannot insert NULL into_ – Mr. Tao Jan 23 '17 at 19:09
  • You can't insert values into `generated always` column. Use `insert into names(name) select 'a' from dual union all select 'b' from dual`. The id will be generated automatically – Gurwinder Singh Jan 23 '17 at 19:12
  • You can, of course. Just don't put the value for it in your insert. the value is **generated** by Oracle – Gurwinder Singh Jan 23 '17 at 19:23
  • I must be missing something hiding in plain sight. `INSERT INTO names(name) select 'Oliver' from dual union select 'Jack' from dual;` ends with ORA-01400 – Mr. Tao Jan 23 '17 at 23:57
0

You might also want to check out http://plsql.ninja/npg/package/random_ninja. Morten Egan has been building a very interesting library of utilities and his random_ninja package will generate all sorts of data for you.

Steven Feuerstein
  • 1,914
  • 10
  • 14