Solved

Procedure to drop tables

Posted on 2013-01-03
8
610 Views
Last Modified: 2013-01-03
I'm trying to make a package to drop tables, create tables, build tables with new data and schedule this process to happen nightly.

Can someone help me with my "drop tables" procedure and give me an idea how i'd want that to look?

The Proc
PROCEDURE DROP_ARCHIVED_TABLES IS
              BEGIN
              
                            NULL;
              END;

Open in new window



The PKG 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_ARCHIVED_TABLES IS
              BEGIN
              
                            NULL;
              END;

              -------------------------------------------------------------------------------------------------------------------------------------------------------------------
              -------------------------------------------------------------------------------------------------------------------------------------------------------------------

              PROCEDURE CREATE_NEW_ARCHIVED_TABLES IS
              BEGIN
              
              EXCEPTION
                            
                            
                            WHEN OTHERS THEN
                                          /* will output an error message and indicate where errors exist */
                                          W('EXCEPTION: ' || SQLCODE || ' IN ARCHIVE_ALL_TABLES. ERROR MESSAGE:  ' || SQLERRM);
              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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 200 total points
ID: 38741271
To drop a table in a package, you need to use EXECUTE IMMEDIATE, so your procedure would look something like this:

PROCEDURE DROP_ARCHIVED_TABLES IS
              BEGIN
              
                            EXECUTE IMMEDIATE 'drop table tab1';
                            EXECUTE IMMEDIATE 'drop table tab2';
                            EXECUTE IMMEDIATE 'drop table tab3';
                            EXECUTE IMMEDIATE 'drop table tab4';
              END;

Open in new window


If you are going to recreate the same table later within the process, why not just truncate the tables?
0
 

Author Comment

by:Mark_Co
ID: 38741300
I suppose I could truncate in the same proc instead. Would you suggest that instead of drop? What benefit is there to trunc vs drop in this case?
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 150 total points
ID: 38741307
if you truncate tables, you will get rid of the data and then you can insert new data instead of dropping and re-creating the tables. It all depends what are you trying to accomplish
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 150 total points
ID: 38741313
To me, unless you want to remove table definitions, truncating would be a much better and faster solution
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.

 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 50 total points
ID: 38741336
in addition to what experts have mentioned above..

it all depends on your requirement too..

dropping of the table will also drop its indexes and constraints,triggers,grants,synonyms referential integrities( if any..)
0
 
LVL 31

Accepted Solution

by:
awking00 earned 100 total points
ID: 38741338
How are your archived tables identified? For example, is there a specific naming convention that identifies them? Assuming something like tablenames ending in "ARCH", you could create a procedure such as -
drop_archive_tbls is
v_sql varchar2(255);
begin
for t in (select table_name from dba_tables where table_name like '%ARCH') loop
v_sql := 'drop table '||t.table_name||' purge';
execute immediate v_sql;
end loop;
end drop_archive_tbls;
Note - you may want to add a cascade constraints clause to your drop statement.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 200 total points
ID: 38741557
Additionally, a truncate would be faster than a drop and create.  If you keep the storage rather than release it, inserts could be faster as you would be doing less space allocation.

It all depends on the structure of the tables.  Referential integrity constraints would get in the way of truncates.  Drop and recreate you have to be aware of indexes, privileges, triggers, etc.  If anything is added that references the tables, you have to be sure to update your packages to reflect that.
0
 

Author Closing Comment

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

743 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

15 Experts available now in Live!

Get 1:1 Help Now