Re: TRUNCATE from a stored procedure?

From: Robert W. Swisshelm <swisshelm_at_lilly.com>
Date: 1997/06/13
Message-ID: <33A1C2A3.190D@lilly.com>#1/1

Panther wrote:
>
> I have users who need to reload several large tables on a weekly basis.
> Currently, they truncate
> each
> table and then run SQL* LOAD to load the new data. Apparently, doing a
> delete in a loop
>
> takes too
> long. I'm trying to find a way to take the schema owner's password away
> from them,
> without slowing
> down
> their reloads too much.
>
> I know that I can't directly put a TRUNCATE command in a stored procedure,
> since it's a DDL
> command,
> but several people have told me that there is an indirect way of calling
> TRUNCATE
> from a stored
> procedure. However none of them can remember the details of how it is
> done.
> If anyone can tell me
> how this might be accomplished, I'd appreciate it.
>
> If it makes a difference, the databases are versions 7.2 and 7.3.
>
> Please post the answer to the newsgroup, or Email me at: alan.kilivry_at_msx.ae
> .ge.com
>
> Thanks!

Here is a stored procedure that does that. Create it under the table owner's account, and grant execute on the procedure to the developer.

PROMPT Creating GSW_TRUNC_TABLE Procedure CREATE OR REPLACE
PROCEDURE trunc_table (table_var IN VARCHAR2)

--  allow those with execute on this procedure to truncate any
--  tables
--
AS
 cursor_var     INTEGER;        -- holds cursor id
 return_val     INTEGER;        -- holds call return value
 str            VARCHAR2(150);  -- string to hold DDL stmt

BEGIN
   str := 'TRUNCATE TABLE '||table_var||' drop storage';    cursor_var := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cursor_var,str,DBMS_SQL.V7);    return_val := DBMS_SQL.EXECUTE(cursor_var);    DBMS_SQL.CLOSE_CURSOR(cursor_var);
exception

   when others then

       raise_application_error(-20002,sqlerrm); END;
/

---

Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com Received on Fri Jun 13 1997 - 00:00:00 CDT