Re: truncate randomly failing

hi erika,

interesting procedure.

why don't you simply use

procedure truncate_table (p_tname in varchar2) as
begin

    execute immediate 'lock table '||p_tname || ' IN EXCLUSIVE MODE';     execute immediate 'truncate table '||p_tname; end;

???

Erika wrote:
> I am having some problems with truncating tables with a procedure, and
> I just can't figure out why.
> I am calling the following procedure to truncate the tables:
>
> procedure truncate_table (p_tname in varchar2)
> as
> begin
> for i in 1 ..100
> loop
> begin
> execute immediate 'truncate table '||p_tname;
> exit;
> exception
> when others then if (i=100) then RAISE; end if;
> end;
> end loop;
> end;
>
>
> It fails randomly (at least it seems random), for different users and
> different tables. The error message is: ORA-00942: table or view
> does not exist.
>
> It can't be a permission problem because the same table/user
> combination works most of the time, but fails sometimes.
> I wasn't able to observe any pattern of when does it happen and why,
> except that it seems to happen during heavy-use periods.
>
> Thank you for your help
>
> Erika
Received on Mon Dec 09 2002 - 09:45:07 CST