Link to home
Start Free TrialLog in
Avatar of Mark_Co
Mark_CoFlag for United States of America

asked on

Procedure to drop tables

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

SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark_Co

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark_Co

ASKER

Thanks