On Fri, 16 Feb 2001, Smith, Ron L. wrote:
> I have a developer that is trying to truncate a table from within a
> procedure. If he does a delete it works ok. If he tries to issue the
> truncate command he gets errors. He is running the procedure as the schema
> owner. Is there a problem with issuing a truncate command from within a
> procedure?
>
> Ron Smith
> Database Administration
> [EMAIL PROTECTED]
>
Ron, what errors does he get?
Jared
PS. Here's a procedure for truncating
-------
-- 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:
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).
