Re: Removing data form a table

From: <Jared.Still_at_radisys.com>
Date: Fri, 08 Feb 2002 14:15:37 -0800
Message-ID: <F001.0040AE41.20020208141421@fatcity.com>

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" <lprais_at_ts.checkpoint.com> Sent by: root_at_fatcity.com
02/08/02 01:25 PM
Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        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: lprais_at_ts.checkpoint.com

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: ListGuru_at_fatcity.com (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: Jared.Still_at_radisys.com

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: ListGuru_at_fatcity.com (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).

Received on Fri Feb 08 2002 - 16:15:37 CST