insert/update/delete multiple tables in single SQL statement

Question

When I use JMeter with JDBC request on Oracle DB it doesn't allow ; in statement.

SQL query. Do not enter a trailing semi-colon.

For example

insert into a select '1' from dual;
insert into b select '1' from dual

Will produce exception (while it work when split to 2 separate request):

 java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

If I want to execute clear/insert of test data I can't insert multiple tables using 1 JDBC request and I must create at least 1 request per table (can be with multiple values per table).

Is there a way to insert/update/delete except using a callable statement ?

Is this limitation connected to solely to SQL injection?

Answer 1

Insert would work, using INSERT ALL, such as

insert all
  into a (col) values ('1')
  into b (col) values ('2')
select * From dual

Update and delete wouldn't; I'm afraid that you'll have to use one statement per table, unless you want to use PL/SQL and call a procedure which will do all that job.

Answer 2

Why not try : Insert Into tableA a, tableB b Value (a.field = (Select '1' From Dual), b.field = (Select '1' From Dual)), I'm not totally sure but I think it can work.

Answer 3

I found INSERT ALL with multiple tables works:

INSERT ALL
 Into a (a) values ('1')
 into b (b) values('1')
 SELECT * FROM dual

You can also use the INSERT ALL statement to insert multiple rows into more than one table in one command.

Answer 4

The ; isn't part of "sql" it's a terminator for the program that is sending the sql. In sqldev/sqlcl/sqlplus the ";" is seen to know the end of the statement to sent everything up to that point to the database as a statement. Then process the next set of text until the next ";" or "/"

This example presents to the database as one big string such as:

"insert into a select '1' from dual; insert into b select '1' from dual"

To perform 2 statements in 1 call to the database, it'll need a begin/end around to convert it to an executable block such as

BEGIN
insert into a select '1' from dual;
insert into b select '1' from dual;
END;

Then in JMeter change to "Callable Statement"