Toad for Oracle..How to execute multiple statements?

Question

I have some 800-1200 INSERT statements generated from an excel sheet. I want to run these in TOAD - Oracle db.

If I press F9, it runs only one line and F5 gives me syntax issue and do not seem to work? What am I missing here?

Promoter

What is the error? Can you add a small example of the statements that fail? – Insanitary

F5, hangs and does nothing, and I need to cancel the operation after a few minutes. F9 on one line with multiple statements separated by ";" gives an error saying there's an invalid character after the first ";" – Promoter

It's been a while since I've used TOAD, but if I remember correctly there are a few cases where its parser doesn't work correctly. For example, the alternative quoting mechanism, e.g. q'!...!', and comments after semicolons. Not sure why F5 would hang, unless it opens another session and is waiting for your current session to commit/rollback? Either way, it would help if you could post a small test case. – Insanitary

Answer 1

F9 executes only one statement. By default Toad will try to execute the statement wherever your cursor is or treat all the highlighted text as a statement and try to execute that. A ; is not necessary in this case.

F5 is "Execute as Script" which means that Toad will take either the complete highlighted text (or everything in your editor if nothing is highlighted) containing more than one statement and execute it like it was a script in SQL*Plus. So, in this case every statement must be followed by a ; and sometimes (in PL/SQL cases) ended with a /.

Annapurna

Know why F5 gives output as plain text, while F9 gives output as a spreadsheet or as a new TOAD window for something like a describe statement? – Selfimmolating

@PeterBecich: that's because F5 is being executed in an external program; TOAD doesn't interpret the results which are plain text. F9, on the other hand, is being executed by TOAD directly so it can format the results in a nice grid layout. – Dorsey

Answer 2

Wrap the multiple statements in a BEGIN END block to make them one statement and add a slash after the END; clause.

BEGIN
  insert into books
  (id, title, author)
  values
  (books_seq.nextval, 'The Bite in the Apple', 'Chrisann Brennan');

  insert into books
  (id, title, author)
  values
  (books_seq.nextval, 'The Restaurant at the End of the Universe', 'Douglas Adams');
END;
/

That way, it is just ctrl-a then ctrl-enter and it goes.

Breckenridge

Answer 3

Highlight everything you want to run and run as a script. You can do that by clicking the icon on the menu bar that looks like a text file with a lightning bolt on it. That is the same as hitting F5. So if F5 doesn't work you probably have an error in your script.

Do you have semicolons after each statement?

Hoop

Answer 4

begin

insert into fiscal_year values(2001,'01-jan-2001','31-dec-2001');
insert into fiscal_year values(2002,'01-jan-2002','31-dec-2002');
insert into fiscal_year values(2003,'01-jan-2003','31-dec-2003');
insert into fiscal_year values(2004,'01-jan-2004','31-dec-2004');

end;

Use like this and then commit.

Forequarter

Answer 5

I prefer the Execute via SQL*Plus option. It's in the little down-arrow menu under the "Execute as script" toolbar button.

Dorsey

Answer 6

Open multiple instances of Toad and execute.

Bacon

Answer 7

You can either go for f5 it will execute all the scrips on the tab.

Or

You can create a sql file and put all the insert statements in it and than give the file path in sql plus and execute.

Garnettgarnette

Answer 8

1. Just finsih all of your queries with ;
2. Select all queries you need (inserts, selects, ...).
3. Push or F5 or F9 both Works.

Not necessary to execute as script

Cookgeneral

Answer 9

If you have Multiple Insert Statements then Toad has a simple Way to execute all the Insert Statements.

Right Click On the Highlighted Insert Statements --> Select Execute Menu --> Execute Script

This will automatically start running the Insert Statements

Catlett

Answer 10

1. Select all statement make sure you have / at the end of the script.
2. press F5
3. check in output script you will be able to see all records in grid
4. right click on output grid->save file(.txt,.csv) as per requirement.

Misread