Re: drop any table vs. delete any table

On Fri, 27 Feb 1998 10:30:05 -0500, "Al" <al_at_work> wrote:

>If you don't like granting such privileges, you can encapsulate the truncate
>statements into a stored procedure, and simply grant EXECUTE
>
>CREATE OR REPLACE PROCEDURE sp__trunc_tabs AS
>cursor_1 integer;
>ignore integer;
>BEGIN
>-- open a cursor
> cursor_1 := dbms_sql.open_cursor;
>-- repeat the following two lines for each table that must be truncated
>-- changing 'tb__junk' to the correct table name
> dbms_sql.parse(cursor_1,'truncate table tb__junk',dbms_sql.v7);
> ignore := dbms_sql.execute(cursor_1);
>-- close the cursor
> dbms_sql.close_cursor(cursor_1);
>END;
This is a good idea. In practice however, it is somewhat more complicated. The stored procedure first needs to find all FK that reference the table to be truncated, and disable.

The table can then be truncated, and the FK's reenabled.

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;) Regence Blue Cross Blue Shield of Oregon jkstill_at_bcbso.com
jkstill_at_teleport.com Received on Sun Mar 08 1998 - 00:00:00 CST