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

x
?
Solved

Procedure to drop tables

Posted on 2013-01-03
8
Medium Priority
?
618 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
[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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 800 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 600 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 600 total points
ID: 38741313
To me, unless you want to remove table definitions, truncating would be a much better and faster solution
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 200 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 32

Accepted Solution

by:
awking00 earned 400 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 35

Assisted Solution

by:johnsone
johnsone earned 800 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

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.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

715 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