Re: How to prevent deletes when DELETE ANY TABLE granted?

John Verbil wrote:
>
> Todd Verstraten wrote:
> >
>
> "snip"
>
> 1. You cannot to TRUNCATEs in PL/SQL, only SQL*Plus (at least on 7.2.3)
>
>

-- 

This is a way to do truncate from PL/SQL, I can't remember where I got
this from but it has proved to be very useful:


PROCEDURE truncate_table (table_name IN VARCHAR2) AS
   cid INTEGER;
BEGIN
   /* Open new cursor and return cursor ID. */
   cid := dbms_sql.open_cursor;
   /* Parse and immediately execute dynamic SQL statement built by
      concatenating table name to TRUNCATE TABLE command. */
   dbms_sql.parse(cid, 'TRUNCATE TABLE ' || table_name, dbms_sql.v7);
   /* Close cursor. */
   dbms_sql.close_cursor(cid);
EXCEPTION
   /* If an exception is raised, close cursor before exiting. */
   WHEN OTHERS THEN
      dbms_sql.close_cursor(cid);
      RAISE;  -- reraise the exception
END truncate_table;


================================================================
Stephen R. Egesdahl             email: stephen.egesdahl_at_nike.com
Senior Oracle DBA/DA            phone: (503)671-NIKE
Nike, Inc.                      fax  : (503)671-6300
One Bowerman Drive           mailstop: NRC-1
Beaverton, OR 97005
================================================================
The opinions expressed are my own and do not necessarily reflect
those of Nike, Inc.
================================================================