CODEWITHSUNDEEP
sqloracle-databaseplsqldeveloper
IVR Avenger
IVR Avenger

Reputation: 15494

PL/SQL Developer: Multiple statements?

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?

Upvotes: 11

Views: 31592

Answers (4)

jay gohel
jay gohel

Reputation: 39

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.

Upvotes: 2

Priyanka
Priyanka

Reputation: 31

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.

Upvotes: 3

dursun
dursun

Reputation: 1846

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

Upvotes: 2

GolezTrol
GolezTrol

Reputation: 116110

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.

Upvotes: 7

Related Questions