Re: TRUNCATE from a stored procedure?

From: Pierre <maverick_at_videotron.ca>
Date: 1997/06/01
Message-ID: <5ms509$hqq$2@wagner.spc.videotron.ca>#1/1

Here's a procedure I use that calls the DBMS_SQL package. With .PARSE and .EXECUTE you can submit a DDL statement.

Example:

CREATE OR REPLACE PROCEDURE KILL_ALL
IS

   CURSOR sess IS
   SELECT sid,serial#
     FROM v$session
    WHERE username IS NOT NULL
      AND username <> 'SYS';
   execute_feedback INTEGER;
   sess_tb_kill INTEGER;
BEGIN
   FOR sess_rec IN sess
   LOOP

      sess_tb_kill := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(sess_tb_kill,'ALTER SYSTEM KILL SESSION 
'||''''||sess_rec.s
id||','||sess_rec.serial#||'''',DBMS_SQL.native);
      execute_feedback := DBMS_SQL.EXECUTE(sess_tb_kill);
      DBMS_SQL.CLOSE_CURSOR(sess_tb_kill);

   END LOOP;
END;
/

Pierre Roussin
Oracle DBA Consultant
ADMORA
Montreal, Quebec. Received on Sun Jun 01 1997 - 00:00:00 CDT