Re: Q about TRUCATE command within stored procedure

You can issue TRUNCATE TABLE statement from PL/SQL procedure using supplied package DBMS_SQL. You will need to call the following functions: open_cursor, parse, execute, close_cursor.
See Oracle documentation:

Oracle Server Application Developer's Guide / Using Dynamic SQL

IIFThen wrote:

> Within a stored procedure, is there a FAST way to do the following:
>
> (1) delete rows
> (2) insert rows
> (3) update rows
>
> Is it possible to use the TRUNCATE command in a stored proc? The DELETE
> command is too slow for bulk deletes. I tried the following:
>
> replacing DELETE FROM "tablename" with TRUNCATE TABLE "tablename" within my
> SP.
>
> Get error.
>
> Is it because TRUNCATE is a DDL command? Is there another alternative to
> delete bulk data within SP?
>
> I don't know a faster replacement for INSERT and UPDATE for bulk data within
> an SP.
>
> IIFThen
Received on Wed Jul 21 1999 - 17:29:48 CDT