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?

Solution 1

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.

Solution 2

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.

Solution 3

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'
/

Solution 4

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.

View more solutions

Comments

[WCTech]

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?

[GolezTrol]

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?).

[WCTech]

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?

[GolezTrol]

If found thread that suggests to use a blank line between the statements. 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.

[Tom Stickel]

Nice to now, but that ends up on different Tabs