Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Procedure to Create Tables after they are droppped

Posted on 2013-01-04
13
Medium Priority
?
376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 23

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 23

Accepted Solution

by:
Steve Wales earned 1000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 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 32

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
 

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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1000 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 32

Assisted Solution

by:awking00
awking00 earned 600 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 77

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

704 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