I'm not in the habit of one-up-manship, but I just have to post this procedure. I can't brag about it anyway, as I didn't write it. Notice that it deals with FK constraints. 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 On Wed, 2004-02-18 at 17:23, Jacques Kilchoer wrote: > Another advantage is that, since the TRUNCATE_TABLE is created with authid > current_user, it will allow USERB to truncate table USERC.TABLE_NAME even if > access is granted through a role, or through a role from another role and so > on, something that can be tedious to check in a stored procedure. > Though I notice that the previous version I included had an unnecessary > rollback. How embarassing! The better version: > > create or replace procedure do_truncate (table_owner_in varchar2, > table_name_in varchar2) > is > begin > execute immediate 'truncate table "' || table_owner_in || '"."' > || table_name_in || '"' ; > end do_truncate ; > / > create or replace procedure truncate_table > (table_owner_in varchar2, table_name_in varchar2) > authid current_user > is > begin > execute immediate 'delete from "' || table_owner_in || '"."' || > table_name_in > || '" where rownum < 1' ; > do_truncate (table_owner_in, table_name_in) ; > end truncate_table ; > / > > > -----Original Message----- > > From: oracle-l-bounce@xxxxxxxxxxxxx > > [ mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of John Flack > > Sent: mardi, 17. fvrier 2004 08:10 > > To: oracle-l@xxxxxxxxxxxxx > > Subject: RE: Privilege to be abl to truncate another user's table > > > > > > I like your implementation - find out if the user has delete > > privilege = > > by trying to do a delete. Mine does a SELECT on > > ALL_TAB_PRIVS for this = > > and so is less straightforward. > > > > -----Original Message----- > > From: Jacques Kilchoer [ mailto:Jacques.Kilchoer@xxxxxxxxx] > > Sent: Friday, February 13, 2004 9:16 PM > > To: oracle-l@xxxxxxxxxxxxx > > Subject: RE: Privilege to be abl to truncate another user's table > > > > > > I hate to steal someone else's thunder, but I wrote something > > a while = > > ago that does exactly the same thing, so I will take the liberty of = > > posting it here. > > Create two procedures owned by USERA. USERA has DROP ANY TABLE = > > privilege. > > grant execute on TRUNCATE_TABLE to USERB ; > > > > USERB has DELETE privilege on USERC.TABLENAME ; > > > > then USERB can say > > execute usera.truncate_table ('USERC', 'TABLENAME') > > > > create or replace procedure do_truncate (table_owner_in varchar2, = > > table_name_in varchar2) > > is > > begin > > execute immediate 'truncate table "' || table_owner_in || '"."' > > || table_name_in || '"' ; > > end do_truncate ; > > / > > create or replace procedure truncate_table > > (table_owner_in varchar2, table_name_in varchar2) > > authid current_user > > is > > begin > > execute immediate 'delete from "' || table_owner_in || '"."' || = > > table_name_in > > || '" where rownum < 2' ; > > rollback ; > > do_truncate (table_owner_in, table_name_in) ; > > end truncate_table ; > > / > > > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------