How To Run Multiple SQL Queries?

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 t

Solution 1:

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.

Another way is this solution.

Solution 2:

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

Solution 3:

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.