We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
9 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Procedure to drop tables
Posted on 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
The PKG Body
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;
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;
[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
Active 3 days ago
To drop a table in a package, you need to use EXECUTE IMMEDIATE, so your procedure would look something like this:
If you are going to recreate the same table later within the process, why not just truncate the tables?
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;
If you are going to recreate the same table later within the process, why not just truncate the tables?
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?
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
To me, unless you want to remove table definitions, truncating would be a much better and faster solution
Active today
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,grant
it all depends on your requirement too..
dropping of the table will also drop its indexes and constraints,triggers,grant
Active 2 days ago
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.
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.
Active 3 days ago
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.
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.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Working with Network Access Control Lists in Oracle 11g (part 1)
Part 2: http://www.e-e.com/A_9074.html
So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle:
A cursor is used to process individual rows returned by database system for a query.
In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work. It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.