Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: using truncate in oracle7 procedures

Re: using truncate in oracle7 procedures

From: Tfidd <tfidd_at_aol.com>
Date: 1996/12/31
Message-ID: <19961231182700.NAA16351@ladder01.news.aol.com>#1/1

You have to make sure you have the proper rights as the other people pointed out. Here is a function I use. This function handles tables with synonyms. No warranties, etc... Use at your own risk.

FUNCTION TruncTab
  (TableIn VARCHAR2,
   ConfirmIn BOOLEAN := TRUE)
RETURN BOOLEAN IS
  CursorId INTEGER;
  ReturnValue INTEGER;
  TableOut all_synonyms.table_name%TYPE;   TableOwner all_synonyms.table_owner%TYPE; BEGIN
  Stack.Push('User_Util.TruncTab');
  IF ConfirmIn THEN
    CursorId := dbms_sql.open_cursor;
    dbms_sql.parse(CursorId, 'TRUNCATE TABLE '||TableIn,

                   dbms_sql.v7);

    ReturnValue := dbms_sql.execute(CursorId);     dbms_sql.close_cursor(CursorId);
  ELSE
    debug.out('Truncate table function is off.',1);   END IF;
  Stack.Pop;
  RETURN TRUE;
EXCEPTION
  WHEN aic_err.no_table THEN
  BEGIN
    SELECT table_owner, table_name INTO TableOwner, TableOut     FROM all_synonyms
    WHERE synonym_name = UPPER(TableIn)     AND owner = user;
    CursorId := dbms_sql.open_cursor;
    dbms_sql.parse(CursorId, 'TRUNCATE TABLE '||
                   TableOwner||'.'||TableOut,
                   dbms_sql.v7);

    ReturnValue := dbms_sql.execute(CursorId);     dbms_sql.close_cursor(CursorId);
    Stack.Pop;
    RETURN TRUE;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_sql.close_cursor(CursorId);
-- debug.out('The '||TableIn||' table or view does not exist.',1);

    Stack.Push('The '||TableIn||' table or view does not exist.');     RETURN FALSE;
  WHEN aic_err.no_privilage THEN
    dbms_sql.close_cursor(CursorId);
-- debug.out('You do not have rights to truncate table '||

Received on Tue Dec 31 1996 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US