CODEWITHSUNDEEP
sqloracle-databasestored-proceduresoracle10g
Craig C
Craig C

Reputation: 165

How to use commands like DROP TABLE etc. within a stored procedure

I have written a script which is intended to be scheduled to run overnight to copy yesterday's tables, drop yesterday's tables, create new tables, then compare changes. This is working fine as a scheduled task, but I am really struggling to find a way of allowing the users to execute it on the fly for testing purposes.

What I really want to do is to pack it all into a stored procedure, as I have an eForm tool which will easily allow the user (very non-technical) to be able to execute the procedure with a click.

Here is an example of the start of the script. Can anyone help me shoehorn this into a stored procedure as it clearly won't accept commands like DROP TABLE as standard.

Thanks.

DROP TABLE SQL2005TEST.ABSENCEFULLDATADIFF_YESTERDAY;
DROP TABLE SQL2005TEST.ABSENCELATESTSTART_YESTERDAY;
DROP TABLE SQL2005TEST.ABSENCELATESTEND_YESTERDAY;

CREATE TABLE SQL2005TEST.ABSENCEFULLDATADIFF_YESTERDAY
 AS SELECT * FROM SQL2005TEST.ABSENCEFULLDATADIFF;

CREATE TABLE SQL2005TEST.ABSENCELATESTSTART_YESTERDAY
 AS SELECT * FROM SQL2005TEST.ABSENCELATESTSTART;

CREATE TABLE SQL2005TEST.ABSENCELATESTEND_YESTERDAY
 AS SELECT * FROM SQL2005TEST.ABSENCELATESTEND;

Upvotes: 3

Views: 22464

Answers (1)

Anjan Biswas
Anjan Biswas

Reputation: 7912

CREATE OR REPLACE PROCEDURE proc_name AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE SQL2005TEST.ABSENCEFULLDATADIFF_YESTERDAY';
.....
EXECUTE IMMEDIATE 'CREATE TABLE SQL2005TEST.ABSENCELATESTEND_YESTERDAY
                       AS SELECT * FROM SQL2005TEST.ABSENCELATESTEND';
....
....
EXCEPTION
  ....
  ....
END;

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block, within a PL/SQL block, or Stored Procedure, or Package. This is more specifically used if you need to run DDL statements like DROP, CREATE TABLE etc. You cannot execute DDL commands from PL/SQL as DML statements, so the only way is dynamic SQL. More info here and here.

Upvotes: 9

Related Questions