1

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?

Ori Marko
  • 56,308
  • 23
  • 131
  • 233

4 Answers4

2

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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • I just answer similar, you still need to remove `;` for it to work – Ori Marko Nov 08 '18 at 10:18
  • Well, `;` is a terminator which is OK as far as (Oracle) is concerned. If it shouldn't/can't be used in you Java program, remove it. `insert all` is what counts, I presume :) I've removed it. – Littlefoot Nov 08 '18 at 10:20
0

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.

Zorglube
  • 664
  • 1
  • 7
  • 15
  • Different error: `ORA-00926: missing VALUES keyword`, tried also `Insert Into tableA a, tableB b Values (a.field = (Select '1' From Dual), b.field = (Select '1' From Dual))` – Ori Marko Nov 08 '18 at 10:11
  • So replace `Value` by `Values`, we're not here to do all your job ! – Zorglube Nov 08 '18 at 10:13
0

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.

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
0

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"

Kris Rice
  • 3,300
  • 15
  • 33