Yes, the results need to stay beyond the end of the session (and
visible to other users as well). The results should only be truncetad
when one of the users (any of them) decides to generate new results
(with different data). We have some internal locks in the software to
make sure that no 2 users are generating results for the same table at
the same time,so that is not an issue.
Erika
"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<2H1K9.124636$pN3.9254_at_sccrnsc03>...
> Global temporary tables are not really temporary. The results they store
> are. Are you saying you need to have the results extend beyond the user's
> session?(for someone else)
> Jim
> "Erika" <erika_at_multimodalinc.com> wrote in message
> news:766a32cf.0212120702.7a044b0d_at_posting.google.com...
> > Thank you all for the messages.
> >
> > Some answers to your questions:
> >
> > Matthias: the loop is definitely not the problem, because the problem
> > was there first and then we added the loop to see if it fixes the
> > problem, it didn't fix it but it definitely didn't cause it. So
> > really, forget about the loop, the problem was happening when there
> > was no loop in the procedure and continues happening with the loop.
> > And no, I never got the ORA-00054 error.
> >
> > Jim and Vladimir: the tables can't be temporary, they need to survive
> > the end of the session, and only should be truncated if someone
> > (possibly a different user) runs the same algorithm and decided to
> > override the results of a particular run. There was a reason why
> > things were designed the way they were, so that is really not the
> > question here. The question is that the way the truncate is
> > implemented it shouldn't fail, but it does, and most likely it is
> > somehow related to synonyms.
> > I am copying here what the DBA thinks is happening:
> >
> > "ACT_OWN owns the tables. And ACT_OWN owns the truncate_table
> > procedure.
> > ACT_OWN has granted the "execute" authority to the various users who
> > need
> > it. When the truncate_table procedure is executed by user A, it is run
> > with
> > the privileges of the owner - ACT_OWN. Therefore, we should never get
> > the
> > ORA-942 error. But we do. I believe we have run into an intermittent
> > bug.
> > Public synonyms have been created for every table. The public synonym
> > means
> > that when user A refers to the name "mytable" it is translated to
> > "act_own.mytable" and then the permissions are checked on the
> > table. Synonym translation is valid for DML (select, insert, update,
> > delete), but not for DDL (create, alter, truncate, etc). Since the
> > truncate
> > command is being issued by ACT_OWN against and ACT_OWN table, synonyms
> > should not be involved, but I think the bug is that sometimes Oracle
> > tries
> > to resolve the table name via synonyms and fails - thus ORA-942 (table
> > not
> > found).
> > My recommended workaround is to modify the truncate table procedure to
> > explicitly state the owner in the truncate command, eliminating any
> > attempt
> > at synonym resolution:
> > execute immediate 'truncate table act_own.'||p_tname; "
> >
> > We are testing this right now (might take as a while because we are
> > not able to replicate this at will, so we just have to see if we can
> > go without the error for a while). I just would like to know if
> > anybody else ran into the same problem before. Or if yo have heard
> > about an Oracle bug like this. I don't know the Oracle version but I
> > will ask and post it later.
> >
> > Thanks again
> >
> > Erika
Received on Thu Dec 12 2002 - 13:05:12 CST
