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