Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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_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
Mark_Co
Asked:
Mark_Co
  • 6
  • 3
  • 2
  • +1
4 Solutions
 
Steve WalesSenior Database AdministratorCommented:
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
 
Mark_CoAuthor Commented:
It's a practice exercise so I want to do it the long way first
0
 
Steve WalesSenior Database AdministratorCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Mark_CoAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
awking00Commented:
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
 
Mark_CoAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
Mark_CoAuthor Commented:
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
 
awking00Commented:
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
 
Mark_CoAuthor Commented:
Thanks so much! I'd be so lost without the great help from experts like you guys. :)
0
 
slightwv (䄆 Netminder) Commented:
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
 
Mark_CoAuthor Commented:
Nice, Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now