How to run multiple SQL queries?

Question

I have a create table query, an update query ,and then drop table query. I need to run these three queries in one shot. What is the best way to do this?

Example
1st Query: Create table A.
2nd Query: Update value in table A
3rd Query: Drop table A.

Instead of running these three queries one by one, I want to run them in on go either by PLSQL or some other way. Please help.

Write the queries in a file (script) and then run the script: http://www.techonthenet.com/oracle/questions/script.php — ypercubeᵀᴹ, Jun 02 '11 at 09:28
I did it in TOAD IDE. WE can run multiple queries at a time by pressing F5 I think(Done long back) — Max, Jun 02 '11 at 09:28
Also see this: http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch6.htm — ypercubeᵀᴹ, Jun 02 '11 at 09:30
Sometimes you don't need the (temporary) staging table at all e.g. can use a CTE instead. But isn't the net result of running all three (create A, update A, drop A) exactly nothing? — onedaywhen, Jun 02 '11 at 11:01
Creating and dropping tables in a single Unit Of Work is a code smell. It might be necessary in some other databases, but it is not necessary in Oracle: there are always better ways, depending on precisely what it is you need to do. — APC, Jun 05 '11 at 08:51

score 10 · Answer 1 · edited Aug 31 '22 at 16:00

10

Put the 3 queries following each other separated by a semi-colon:

SELECT * 
FROM table_to_be_selected; 
DROP TABLE the table_to_be_dropped; 
TRUNCATE TABLE table_to_be_truncated;

You can concatenate these queries in a string and execute that string.

edited Aug 31 '22 at 16:00

adius

13,685
7
45
46

answered Jun 02 '11 at 09:34

DaMainBoss

2,035
1
19
26

whirlwin · Answer 2 · 2011-06-02T10:10:42.107

7

Why not create a PROCEDURE?

E.g.

CREATE OR REPLACE PROCEDURE foo
IS
BEGIN
  -- The create sentence goes here. For example:
  -- EXECUTE IMMEDIATE
  -- 'CREATE TABLE bar (...)';

  -- The update sentence goes here
  -- EXECUTE IMMEDIATE
  -- 'UPDATE bar SET ...';

  -- The drop/delete sentence goes here.
  -- EXECUTE IMMEDIATE
  -- 'DROP TABLE bar;'
END;

You can test it with:

SET serveroutput ON;
exec foo;

The PROCEDURE is stored on the database, so you can use it later on.

Hope this helps.

edited Jun 02 '11 at 10:10

answered Jun 02 '11 at 09:33

whirlwin

16,044
17
67
98

2

don't forget about execute immediate for DDL statements here – Kirill Leontev Jun 02 '11 at 09:51
Thanks for pointing that out. I learned something new today! :) – whirlwin Jun 02 '11 at 10:12

score 5 · Answer 3 · answered Jun 02 '11 at 09:32

Simply put three queries one after the other in a .sql file, with semi-colons after each statement, then execute it as a script (either on a SQL*Plus prompt using @scriptname.sql or in TOAD/SQL Developer [or equivalent] using its script execution function).

How to run multiple SQL queries?

3 Answers3

Linked