Re: PL/SQL to truncate a table

In article <3a3b579d.1474103_at_news.mindspring.com>,   willjamu_at_mindspring.com wrote:
> I need to clear out a staging table weekly prior to an application
> coming up. I know how to issue DML. Is truncate table considered DDL..
>
> Logic:
>
> Insert into work select * from xxxx;
>
> if rc =0
> then
> do
> truncate table
> end
> else
> do
> Say 'Insert did not work'
> end
>
> exit
>

I wrote a function to do this, and use it quite a lot: Here is the code:

/**FILE****************************************************************/
/* File Id:                     truncate.sql.                         */
/* Author:                      Stan Milam.                           */
/* Date Written:                23-Feb-2000.                          */
/* Description:                                                       */
/*     A called routine to truncate the contents of a table.          */
/*                                                                    */
/****************************************************************FILE**/

create or replace function
truncate_table( p_table in varchar2 ) return boolean

/**FUNCTION************************************************************/
/* Name:                                                              */
/*     truncate_table().                                              */
/*                                                                    */
/* Description:                                                       */
/*     Function to truncate the contents of a table.  The table       */
/*     is specified as an argument.  This magic is accomplished       */
/*     through the wonders of dynamic PL/SQL.                         */
/*                                                                    */
/* Arguments:                                                         */
/*     P_TABLE varchar2 - The name of the table to truncate.          */
/*                                                                    */
/* Return Value:                                                      */
/*     A Boolean value of either true or false.  Of course, false     */
/*     is returned when the table cannot be truncated.                */
/*                                                                    */
/************************************************************FUNCTION**/

is

    l_cursor number;
    l_return boolean;
    l_wrkbuf varchar2(2000);
    l_tmpbuf varchar2(2000) default 'truncate table ';

    l_return := true;

    /******************************************************************/
    /* Open a cursor.                                                 */
    /******************************************************************/

    l_cursor := dbms_sql.open_cursor;

    /******************************************************************/
    /* Concatenate strings to build an executable DDL statement.      */
    /******************************************************************/

    l_wrkbuf := l_tmpbuf || p_table;

    /******************************************************************/
    /* Parse the statement, which for a DDL statement will also       */
    /* execute it.                                                    */
    /******************************************************************/

    dbms_sql.parse(l_cursor, l_wrkbuf, DBMS_SQL.V7);

    /******************************************************************/
    /* Finally, close the cursor and return to caller.                */
    /******************************************************************/

    dbms_sql.close_cursor(l_cursor);

    return l_return;

exception

    when others then

        dbms_sql.close_cursor(l_cursor);
        return false;

end truncate_table;
/

Sent via Deja.com
http://www.deja.com/ Received on Mon Dec 18 2000 - 17:15:20 CET