Procedure to Create Tables after they are droppped
I'm trying to make a package to drop tables, create tables, and then build/populate tables with new data. Later I will schedule this process to happen nightly with DBMS_SCHEDULER.
Can someone help me with my "CREATE_NEW_ARCHIVED_TABLE
The Spec:
The Body:
Can someone help me with my "CREATE_NEW_ARCHIVED_TABLE
The Spec:
CREATE OR REPLACE PACKAGE DROP_CREATE_TABLES_NIGHTLY IS
/*
Original Author: Me
Created Date: 3-Jan-2013
Purpose: For storing procedures to drop, create, and archive new tables /
To drop initial tables and recreate them nightly
*/
PROCEDURE W(STR VARCHAR2); --WRAPPER FOR DBMS_OUTPUT
PROCEDURE DROP_TABLES
(
P_SCHEMA VARCHAR2
,P_TABLENAME VARCHAR2
); --DROPS PRE-EXISTING TABLES
PROCEDURE CREATE_NEW_ARCHIVED_TABLES; --RECREATES TABLES THEMSELVES
PROCEDURE BUILD_ARCHIVED_TABLES; --POPULATE FRESH DATA INTO TABLES
END DROP_CREATE_TABLES_NIGHTLY;The Body:
CREATE OR REPLACE PACKAGE BODY DROP_CREATE_TABLES_NIGHTLY IS
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
PROCEDURE W(STR VARCHAR2) IS
BEGIN
/* setting variable to simpler to use STR variable for outputting */
DBMS_OUTPUT.PUT_LINE(STR);
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* procedure purpose: For dropping previously archived tables so that new ones can be created */
PROCEDURE DROP_TABLES
(
P_SCHEMA VARCHAR2
,P_TABLENAME VARCHAR2
) IS
/* VARIABLES */
L_CMD VARCHAR2(4000);
L_NO_TABLES_TO_DROP EXCEPTION;
PRAGMA EXCEPTION_INIT(L_NO_TABLES_TO_DROP,-00942);
/* When I tested this procedure by inserting a table that didn't exist,
the 00942 error popped up which I then placed in the above line */
BEGIN
L_CMD := 'DROP TABLE ' || P_SCHEMA || '.' || P_TABLENAME;
EXECUTE IMMEDIATE L_CMD;
DBMS_OUTPUT.PUT_LINE(L_CMD);
EXCEPTION
WHEN L_NO_TABLES_TO_DROP THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE CREATE_NEW_ARCHIVED_TABLES IS
BEGIN
NULL;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE BUILD_ARCHIVED_TABLES IS
BEGIN
NULL;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
/*
package begin will be null for compiling purposes...
would use for cleaning up but unnecessary to touch otherwise
*/
NULL;
END DROP_CREATE_TABLES_NIGHTLY;ASKER
It's a practice exercise so I want to do it the long way first
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Are you saying I should do this:
PROCEDURE CREATE_NEW_ARCHIVED_TABLES IS
BEGIN
create table tab2 as select * from tab1 where 0=1;
insert into tab2 select * from tab1;
END;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you describe what it is you want to occur nightly in plain language? I'm not sure whether you want to create archive tables from the existing ones, then empty (or drop, re-create) the existing tables and load them with new data or whether you want to make the existing tables (and data) the archived tables and create and populate new ones.
ASKER
Thanks for the responses. So basically this learning assignment was given to me from a co-worker who suggested I try it this way. They helped me with the drop procedure but didn't describe how many rows the table would have or how many columns. I think it was just open-ended for me to do in whatever way seemed best.
I want to the package to run nightly and drop the pre-existing data in the table, then re-populate the table with fresh data from, let's just say midnight of each night.
Here is the corrected proc:
I want to the package to run nightly and drop the pre-existing data in the table, then re-populate the table with fresh data from, let's just say midnight of each night.
Here is the corrected proc:
PROCEDURE CREATE_NEW_ARCHIVED_TABLES IS
BEGIN
EXECUTE IMMEDIATE CREATE TABLE TAB2 AS
SELECT *
FROM TAB1
WHERE 0 = 1;
EXECUTE IMMEDIATE INSERT
INTO TAB2
SELECT *
FROM TAB1;
END;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok. Thanks :).
I tried this then:
but the compiler hates the "EXECUTE IMMEDIATE CREATE TABLE TAB2 AS" line
I tried this then:
PROCEDURE CREATE_NEW_ARCHIVED_TABLES IS
BEGIN
EXECUTE IMMEDIATE CREATE TABLE TAB2 AS
SELECT *
FROM TAB1
WHERE 0 = 1;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE BUILD_ARCHIVED_TABLES IS
BEGIN
EXECUTE IMMEDIATE INSERT
INTO TAB2
SELECT *
FROM TAB1;
END;but the compiler hates the "EXECUTE IMMEDIATE CREATE TABLE TAB2 AS" line
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much! I'd be so lost without the great help from experts like you guys. :)
Just to add:
execute immediate is for DDL.
The insert statement is DML and it is not necessary to use it here.
You should only user execute immediate when absolutely necessary. It will mask dependencies.
>>given to me from a co-worker who suggested I try it this way.
You were given bad advice.
>>drop the pre-existing data in the table, then re-populate the table with fresh data from, let's just say midnight of each night.
Might I suggest a Materialized View that refreshes every night?
execute immediate is for DDL.
The insert statement is DML and it is not necessary to use it here.
You should only user execute immediate when absolutely necessary. It will mask dependencies.
>>given to me from a co-worker who suggested I try it this way.
You were given bad advice.
>>drop the pre-existing data in the table, then re-populate the table with fresh data from, let's just say midnight of each night.
Might I suggest a Materialized View that refreshes every night?
ASKER
Nice, Thanks!
Or is there some other requirement ?