hi,
I am really not an expert in this (more like a beginner), but doesn't
the truncate automatically lock the table in an exclusive mode? Why do
you think that locking the table explicitly would help my problem?
(the loop of 1..100 was simply added to make it more likely for the
truncate to succeed)
thanks
Erika
Matthias Rogel <rogel_at_web.de> wrote in message news:<at2dq3$v8c4s$1_at_ID-86071.news.dfncis.de>...
> 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 - 16:46:22 CST
