Re: Deleting all rows from a table

From: Scott Urman <surman_at_oracle.com>
Date: 1996/06/17
Message-ID: <4q42t8$bep_at_inet-nntp-gw-1.us.oracle.com>#1/1

In article <4prtk1$j4o_at_big.info.att.com>, bharat_at_allegra.tempo.att.com (Bharat Kumar) writes:
|>
|> I'm writing a PL/SQL procedure that requires the use of a couple of temporary
|> tables. Currently, I've created those temp. tables outside the procedure,
|> and inside the procedure, after I'm done with the main work, I delete all
|> the rows in those temp tables (required for subsequent invocations of the
|> procedure). I would like to use something similar to a "truncate" command,
|> since the "delete from temp_table" command takes a long time. However, I
|> can't put the truncate command directly into the procedure. Any suggestions?
|>
|> Here's the psuedocode for the procedure:
|>
|> procedure blah
|> insert into temp_table (subquery...)
|> ... select/project/join queries on the temp_table
|> delete from temp_table;
|> end blah;
|>
|>

You can use the DBMS_SQL package to execute the truncate command. For example: CREATE OR REPLACE PROCEDURE Blah AS
  ...

  v_Cursor INTEGER;
  v_Command VARCHAR2(200) := 'TRUNCATE TABLE temp_table';
  v_ReturnCode INTEGER;

BEGIN
  ...
  v_Cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_Cursor, v_Command, DBMS_SQL.V7);   v_ReturnCode := DBMS_SQL.EXECUTE(v_Cursor); END Blah;

For more information, check out the Application Developer's Guide or Chapter 10 of _Oracle PL/SQL Programming_.

|> Please post replies to this newsgroup.
|>
|> Thanks,
|> -Bharat
|>
|> -------------------------------------------------------------------------------
|> Disclamer: My opinions are mine alone, and no one else can have them.

-- 
------------------------------------------------------------------------
Scott Urman            Oracle Corporation           surman_at_us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
 Oracle Corporation"
------------------------------------------------------------------------

Received on Mon Jun 17 1996 - 00:00:00 CEST