Solved

Procedure to Create Tables after they are droppped

Posted on 2013-01-04
13
367 Views
Last Modified: 2013-01-04
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_TABLES" procedure and give me an idea how i'd want that to look?


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;

Open in new window


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;

Open in new window

0
Comment
Question by:Mark_Co
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38744921
If all you're doing is trying to repopulate tables nightly, wouldn't it be easier to simply truncate them, rather than dropping and recreating them ?

Or is there some other requirement ?
0
 

Author Comment

by:Mark_Co
ID: 38744923
It's a practice exercise so I want to do it the long way first
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 38744960
Without knowing exactly what the tables are supposed to look like it's kind of hard to give you assistance here.

If your archive table is a copy of an existing table, you could do this to create an empty copy of the table:

create table tab2 as select * from tab1 where 0=1;

That will get you an empty table called tab1 with the exact same structure as tab1.

Then, in your populate procedure you could do

insert into tab2 select * from tab1;


Alternatively, you could just have create table statements for your fixed table definitions.

Doing a drop / create also destroys permissions, so if you have any other users trying to read from the archived tables you're creating, you'd need to re-grant permissions after the create.
0
 

Author Comment

by:Mark_Co
ID: 38745000
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;

Open in new window

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 38745104
I agree with sjwales:  truncate.  It is less overhead since the tables will retain their allocated space.  Even though this is a learning exercise, why learn a 'bad' way to do something?

Create the tables once with the correct tablespaces, sizing, blocksize, constraints, indexes, etc... and leave them.

>>Are you saying I should do this:

Just like your last question:  You cannot do DDL inside a procedure.  You need execute immediate to create a table.
0
 
LVL 31

Expert Comment

by:awking00
ID: 38745117
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Mark_Co
ID: 38745143
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:

 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;

Open in new window

0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 38745151
From a purely learning perspective:

"create table tab2 as select * from tab1" will create a copy of a table and populate it all in one step.

The steps I laid out were to match you package header:  create table and populate (2 steps).  The create with "where 0=1" creates an empty copy of the table and the insert populates.

You couldn't just execute the create in a procedure since DDL can't be used natively in a procedure, it would have to be done as execute immediate (like the drop table in the code you supplied).

However, as slightwv said, learning is one thing, learning bad practices is another.   It's probably not your ideal way to learn to do things.
0
 

Author Comment

by:Mark_Co
ID: 38745163
ok. Thanks :).

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;

Open in new window


but the compiler hates the "EXECUTE IMMEDIATE CREATE TABLE TAB2 AS" line
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 150 total points
ID: 38745224
exexcute immediate requires quotes -
execute immediate 'create table tab2 as select * from tab1 where 1=0';
execute immediate 'insert into tab2 as select * from tab1';
0
 

Author Closing Comment

by:Mark_Co
ID: 38745271
Thanks so much! I'd be so lost without the great help from experts like you guys. :)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38745400
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?
0
 

Author Comment

by:Mark_Co
ID: 38745405
Nice, Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now