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

x
?
Solved

Procedure to drop tables

Posted on 2013-01-03
8
Medium Priority
?
619 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

783 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