PL/SQL Developer: Multiple statements?

Question

I have a script that generates a text file containing several SQL UPDATE statements:

UPDATE TableX SET Field1 = 'New value 1' WHERE Field2='1';
UPDATE TableX SET Field1 = 'New value 2' WHERE Field2='2';
UPDATE TableX SET Field1 = 'New value 3' WHERE Field2='3';
etc.

When I paste the above block of text into an SQL Window in PL/SQL Developer, it tells me that the semicolon is an invalid character. When I remove it, it informs me that my first statement was not terminated properly.

How do I run these statements in a single execution?

score 7 · Answer 1 · answered Aug 02 '11 at 23:01

7

I think you're using the Test window. This can only execute a single statement. The SQL Window and the Command Window are able to run multiple statements.

If you need to run this in a Test window, you can embed it in a begin..end block to make it a PL/SQL statement block.

answered Aug 02 '11 at 23:01

GolezTrol

114,394
18
182
210

The Command Window works, as it will just accept a giant batch of SQL, pasted in, as if I'd typed out every line. I'm in the SQL Window, and that's where I'm getting the error I describe, above. Is there a different way I should be terminating my statements in the SQL Window? – IVR Avenger Aug 03 '11 at 14:48
No. The semicolon is ok, and the syntax looks fine too. This SQL should run fine. Actually, it does. When I execute it, it parses fine and tells me 'Table or view not found' as expected. Now it seems to me that this is not your actual SQL (or do you have TableX with a Field1 and a Field2?). – GolezTrol Aug 03 '11 at 19:02
The above code, verbatim (without the etc), in the SQL Window, yields this error: "ORA-00911: invalid character", and then puts the cursor on the first semicolon. It works in Command, but not in SQL. Could I have a newer/older version of PL/SQL? – IVR Avenger Aug 04 '11 at 17:02
If found thread that suggests to use a blank line between the statements. http://osdir.com/ml/db.oracle.toad.free/2003-03/msg00160.html I don't think it's necessary, but it makes me wonder if it may have something to do with the kind of line-endings (Linux/Windows). I'll look up the PLSQLDev version when I'm back at work. Please tell me yours. – GolezTrol Aug 05 '11 at 06:03

score 3 · Answer 2 · answered Sep 03 '13 at 15:31

3

I also faced this error. You need to go to tools->preferences. In window types go to SQL window and select "Auto select statement". This should remove the error.

answered Sep 03 '13 at 15:31

Priyanka

31
1

score 2 · Answer 3 · answered Sep 05 '11 at 19:16

2

try this way;

UPDATE TableX SET Field1 = 'New value 1' WHERE Field2='1'
/
UPDATE TableX SET Field1 = 'New value 2' WHERE Field2='2'
/
UPDATE TableX SET Field1 = 'New value 3' WHERE Field2='3'
/

answered Sep 05 '11 at 19:16

dursun

1,861
2
21
38

Nice to now, but that ends up on different Tabs – Tom Stickel Dec 10 '15 at 18:16

score 1 · Answer 4 · edited Jul 23 '15 at 08:32

1

Hi,

you can try this.

Declare 
Begin 
 UPDATE TableX SET Field1 = 'New value 1' WHERE Field2='1';  
 UPDATE TableX SET Field1 = 'New value 2' WHERE Field2='2'; 
 UPDATE TableX SET Field1 = 'New value 3' WHERE Field2='3'; 
End;

in sql developer to execute multiple queries you need to create anonymous block.

hope this make your work easy.

edited Jul 23 '15 at 08:32

ksa

403
1
5
15

answered Dec 11 '12 at 12:41

jay gohel

29
5

PL/SQL Developer: Multiple statements?

4 Answers4