Lance,
You can to this by creating a procedure that will truncate data in a table
after disabling FK constraints. This must be done as you cannot truncate
a table that has enabled FK constraints. It makes sense that you may
want to do this when their are no inserts or updates being done to the
table.
First the wrapper for the procedure, then the procedure itself.
Here's the code with an example of a wrapper to provide a
single table truncate procedure.
Jared
-- jkstill 10/28/97
-- added 'towner' to specify object owner
-- this allows other users the ability to execute
-- the 'truncate' if so desired
-- eg.
-- create or replace procedure ced_detail_truncate
-- as
-- begin
-- ced.trunc_tab('CED_DETAIL','CED');
-- end;
-- grant execute on ced_detail_truncate to ced_dev
--
create or replace procedure trunc_tab
(tname varchar2, towner user_constraints.r_owner%TYPE default user,reuz
boolean default false) as
/*-------------------------------------------------------------------------\
| Name: Trunc_tab
| Description: Given a table name will truncate the table. The table is
| assumed to be in the users schema. As a possible
| enhancement add a table which has user table mapping,
| and check the table before any processing if the current
user
| has rights to truncate the said (tname) table.
| Parameters: tname --- Varchar2, In ; table to be truncated
| towner --- varchar2, In ; owner of table - default to
USER function
| reuz --- Boolean, In ; Option to reause space
| Date Written: 12-Feb-95
| Written By: Raj Pande
|---------------------------------------------------------------------------
| Mod History:
| 12-Feb-95 Date Written
| 12-Feb-96 Given a table, Check dependencies, disable Fk's
truncate
| Table and then enable Fk constraints.
|
\*-------------------------------------------------------------------------*/
lv_chld_table user_constraints.table_name%TYPE;
lv_chld_ownr user_constraints.r_owner%TYPE;
lv_chld_cons user_constraints.constraint_name%TYPE;
lv_parnt_cons user_constraints.r_constraint_name%TYPE;
--lv_ownr user_constraints.r_owner%TYPE := user;
lv_ownr user_constraints.r_owner%TYPE;
src_cursor integer;
stmt_string varchar2(800) ;
temp_str varchar2(200);
rc integer;
lv_cons_count integer;
type str_tab is table of varchar2(800)
index by binary_integer;
enb_cons_tab str_tab;
dsbl_cons_tab str_tab;
CURSOR C_CHILD_TABS is
select chld.owner ownr, chld.table_name tabl, chld.constraint_name
ccon,
chld.r_constraint_name crcon
from user_constraints parnt, all_constraints chld
where chld.constraint_type = 'R'
and chld.r_constraint_name = parnt.constraint_name
and chld.r_owner = parnt.owner
and chld.status = 'ENABLED'
and parnt.table_name = upper(trunc_tab.tname);
CURSOR C_CHILD_COLS is
SELECT column_name from all_cons_columns
WHERE owner = lv_chld_ownr
and table_name = lv_chld_table
and constraint_name = lv_chld_cons;
/*--------------------------------------------------------------------\
| Subroutine: DO_DDL_DSQL
| Description: Subroutine to actually execute the dynamic sql(ddl)
\--------------------------------------------------------------------*/
PROCEDURE DO_DDL_DSQL is
BEGIN
dbms_output.put_line('DDL Execute ' || stmt_string);
dbms_sql.parse(src_cursor, stmt_string, dbms_sql.native);
rc := dbms_sql.execute(src_cursor);
dbms_output.put_line('Return code (DDL) is ' || rc);
END;
/*--------------------------------------------------------------------\
| Subroutine: DO_DML_DSQL
| Description: Subroutine to actually execute the dynamic sql(ddl)
\--------------------------------------------------------------------*/
FUNCTION DO_DML_DSQL return Number is
BEGIN
rc := 0;
dbms_output.put_line(' DML Execute ' || stmt_string);
dbms_sql.parse(src_cursor, stmt_string, dbms_sql.native);
rc := dbms_sql.execute(src_cursor);
rc := dbms_sql.fetch_rows(src_cursor);
dbms_output.put_line('Return code (DML) is ' || rc);
return rc;
END;
/*------------------------------------------------------------------------\
| Subroutine: BLD_DSBL_CON.
| Description: A subroutine to build the disabled foreign key definitions
\------------------------------------------------------------------------*/
PROCEDURE BLD_DSBL_CON is
BEGIN
dsbl_cons_tab(lv_cons_count) := 'ALTER TABLE ' || lv_chld_table ||
' disable constraint ' || lv_chld_cons;
END BLD_DSBL_CON;
/*------------------------------------------------------------------------\
| Subroutine: BLD_ENB_CON.
| Description: A subroutine to build the create foreign key definitions
| that must be created after the table is truncated.
\------------------------------------------------------------------------*/
PROCEDURE BLD_ENB_CON is
BEGIN
enb_cons_tab(lv_cons_count) := 'ALTER TABLE ' || lv_chld_table ||
' enable constraint ' || lv_chld_cons;
END BLD_ENB_CON;
/*----------------------------------------------------------------------\
| Subroutine: DSBL_CON
| Description: The subroutine to disable the existing foreign key
constraints
| for the table.
\-----------------------------------------------------------------------*/
PROCEDURE DSBL_CON is
BEGIN
DO_DDL_DSQL; ------ Execute the Disable constraint.
END DSBL_CON; -- End of procedure dsbl_con
/*-- Start of Main Procedure ------------------------------ */
BEGIN
dbms_output.enable(1000000);
lv_cons_count := 0;
lv_ownr := towner;
src_cursor := dbms_sql.open_cursor; -- Open the cursor
FOR CHLD_TABS in C_CHILD_TABS LOOP -- Get the tables that refer this
tab
lv_chld_table := chld_tabs.tabl;
lv_chld_cons := chld_tabs.ccon;
lv_parnt_cons := chld_tabs.crcon;
lv_chld_ownr := chld_tabs.ownr;
temp_str := ' ';
FOR child_cols in C_CHILD_COLS LOOP -- Check Ref columns, It'll
help
-- If the child field has
index
-- On the fields - Which one
??
temp_str := temp_str || child_cols.column_name ||
' is not null or ';
END LOOP;
temp_str := substr(temp_str, 1, length(temp_str) - 3);
stmt_string := 'Select ''x'' from ' || chld_tabs.ownr || '.' ||
chld_tabs.tabl || ' where ' || temp_str ||
' and rownum = 1';
if ( do_dml_dsql > 0 )
then
raise_application_error(-20200, 'Child Table ' ||
chld_tabs.ownr || '.' ||
chld_tabs.tabl || ' has Not Null values as FK ' ||
' Can Not truncate Table ' ||
upper(trunc_tab.tname));
END IF;
lv_cons_count := lv_cons_count + 1;
bld_enb_con; --------- Build the foreign key constraints
string
bld_dsbl_con; --------- Build the Disable constraints string
-- Note that we Don't do the actual disable now as the next Child
-- FK may have valid foreign keys recs to the table being
-- truncated.
END LOOP;
FOR fk_count in 1..lv_cons_count LOOP -- Now do the disable
constraints
stmt_string := dsbl_cons_tab(fk_count);
do_ddl_dsql;
END LOOP;
IF trunc_tab.reuz -- Build the Truncate the table
THEN
stmt_string := 'truncate table ' || lv_ownr || '.' ||
upper(tname) || ' REUSE STORAGE ';
ELSE
stmt_string := 'truncate table ' || lv_ownr || '.' ||
upper(tname);
END IF;
DO_DDL_DSQL; ----- Execute the truncate Table statement
dbms_output.put_line('Count is ' || lv_cons_count);
-- Hopefully everything went fine so create/enable the onstraints
FOR fk_count in 1..lv_cons_count LOOP
stmt_string := enb_cons_tab(fk_count);
DO_DDL_DSQL; --- Build the Foreign Key constraints
END LOOP;
if dbms_sql.is_open(src_cursor)
then
dbms_sql.close_cursor(src_cursor);
end if;
exception
when others then
if dbms_sql.is_open(src_cursor)
then
dbms_sql.close_cursor(src_cursor);
end if;
raise_application_error(-20100, sqlerrm);
-- raise;
end;
/
show errors
"Lance Prais" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/08/02 01:25 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Removing data form a table
I want to create a procedure the drops all the data from a table.
I was trying to drop table then create this proved to be extremely
complicated in Oracle. I think the above route is much better.
Does anyone know how to do this?
Lance
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lance Prais
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
