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

Question

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;

Solution

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.

Comments

[Craig C]

Thanks a lot. I actually tried this earlier and couldn't get it to work. However, with renewed confidence following your answer I just tried again and this time it's working. Don't know what went wrong ealier. Probably half-heartedness due to not knowing what I was doing.

[AnBisw]

Nothing too funky here just a bunch of Dynamic SQLs! :)