Oracle, Execute immediate Insert

Question

I'm trying to insert random generating data into table, here's code"

begin
  FOR x in 1..300 LOOP
    Execute immediate 'insert into emp values ('||prac_seq.nextval||','''||'name'||x||''','||trunc(dbms_random.value(1,300))||');';
  end loop;
/

table emp has 3 columns - id,name,idmgr;

above query in execute immediate statement looks like:

insert into emp values (13,'name25',193);

This block did not run. When I tried to run single execute immediate statement (f.e.

begin
  Execute immediate 'insert into emp values ('||prac_seq.nextval||','''||'name23'','||trunc(dbms_random.value(1,300))||');'
end;
    /

ORA gives me an error:

Execute immediate 'insert into emp values ('||prac_seq.nextval||','''||'name23'','||trunc(dbms_random.value(1,300))||');'; end; Error report: ORA-00911: invalid character ORA-06512: at line 3 00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action:

And why? Commas, quotes.. everything is checked and fine.

There is no need for using native dynamic SQL(execute immediate) in this case at all - use static SQL. — Nick Krasnov
Before immediate I use JUSt 2000 insert into statements. It takes... a lot of time. With dynamic SQL - 7.54 sec! So yes - there is need. — Michał M

vercelli vercelli · Accepted Answer · 2016-07-28T09:28:44

Why are you using execute immediate for this. Try connect by level.

select prac_seq.nextval, 'name'||level, trunc(dbms_random.value(1,300)) as rnd
from dual
connect by level <= 300;

Oracle, Execute immediate Insert

2 Answers