Re: truncate a table

From: Jared Still <jkstill_at_cybcon.com>
Date: Wed, 02 May 2001 17:25:54 -0700
Message-ID: <F001.002F7E69.20010502171520@fatcity.com>

On Wednesday 02 May 2001 12:50, Behar, Rivaldi wrote:

> Hi,
> I want to grant to Scott to Truncate my Emp
> table (not to truncate all table).
> How can I do that ?
>
> Rivaldi

2 ways.

1. grant DROP ANY TABLE to the user. This is not recommended.
2. create a stored procedure to truncate the table, write a wrapper for the specific table, grant execute on the wrapper.

Below are 2 pieces of code. The first is the simple script to set this up and test it. Below that is the trunc_tab procedure code, which you will actually need to create first.

Jared

• test code for truncate wrapper

create table test_names ( name varchar2(30));

insert into test_names values( 'jared'); insert into test_names values( 'scott');

commit;

create procedure truncate_test_names
as
begin

        trunc_tab('TEST_NAMES','JKSTILL'); end;
/

grant execute on truncate_test_names to scott;

• trtab.prc ---------------
  • 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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jared Still
  INET: jkstill_at_cybcon.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 Wed May 02 2001 - 19:25:54 CDT