Re: ORA-6512

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<bt3u2a$334kt$1_at_ID-82536.news.uni-berlin.de>...
> > Hi I have a problem with a update. I recive this error
> >
> > ORA-00942: table or view does not exist
> > ORA-06512: at "F0001.UPDATE_MASUPTR", line 21
> > ORA-06512: at line 1
> >
> > when i execute this procedure:
> >
> > begin
> >
> > declare
> > Cursor SupTrUpDate
> > is
> > Select *
> > from F0001.SupTr_Gr;
> >
> >
> > SupTr_Rec SupTrUpDate%ROWTYPE;
> >
> > BEGIN
> >
> > open SupTrUpDate;
> >
> > loop
> > fetch SupTrUpDate into SupTr_Rec;
> > exit when SupTrUpDate%NOTFOUND;
> >
> > UPDATE F0001.MaSupTr
> > set MaSupTr.PayedDt = SupTr_Rec.VoDt
> > where (MaSupTr.JNo = SupTr_Rec.JNo
> > and MaSupTr.EntNo = SupTr_Rec.EntNo)
> > or (MaSupTr.AgJNo = SupTr_Rec.JNo
> > and MaSupTr.AgEntNo = SupTr_Rec.EnTNo);
> >
> > commit;
> >
> > end loop;
> >
> > close SupTrUpDate;
> >
> > END;
> >
> > end;
> >
> >
> > F0001.SupTr_Gr is a view based on two other views lika this:
> >
> >
> > F0001.SupTr_Gr:
> >
> > Select "JNO","ENTNO","VODT"
> > from SupTr_Inv
> > union
> > Select "JNO","ENTNO","VODT"
> > from SupTr_Pay
> >
> > F0001.SupTr_Inv:
> >
> > Select ST1.JNo, ST1.EntNo,ST1.VoDt
> > from MaSupTr MT, SupTr ST1, SupTr ST2
> > where MT.Jno = ST1.JNo
> > and MT.EntNo = ST1.EntNo
> > and MT.AgJno = ST2.JNo
> > and MT.AgEntNo = ST2.EntNo
> > and ST1.VoDt != ST1.ValDt
> > and MT.PayedDt = ST1.ValDt
> > and MT.PayedDt != ST1.VoDt
> > and ST1.VoDt >= ST2.VoDt
> >
> > F0001.SupTr_Pay:
> >
> > Select ST2.JNo, ST2.EntNo,ST2.VoDt
> > from MaSupTr MT, SupTr ST2, SupTr ST1
> > where MT.AgJno = ST2.JNo
> > and MT.AgEntNo = ST2.EntNo
> > and MT.AgJno = ST1.JNo
> > and MT.AgEntNo = ST1.EntNo
> > and ST2.VoDt != ST2.ValDt
> > and MT.PayedDt = ST2.ValDt
> > and MT.PayedDt != ST2.VoDt
> > and ST2.VoDt >= ST1.VoDt
> >
> >
> > User F0001 creates the procedure and executes it. F0001 has rights to
> > execute any proc, update any table etc..
> >
> >
> > Line 21 is "UPDATE F0001.MaSupTr" table F0001.MaSupTr exists for sure.
> >
> > what is wrong???
>
>
> Patrick,
>
> Most probably, you are not directly granted the nessessary privileges on
> F0001.MaSupTr. That is, you have the privileges through a role.
>
> Try
> set role none
> in your sql prompt and see if the table still 'exists'. If not, that is
> the problem. Then, you should ask F0001 to
> grant select, update on MaSupTr
>
> hth,
>
> Rene

Hi Rene,
thanks for your effort!

I've tried your solution but it didn't work. User F0001 now has no role and have the objects rights "Update" and "Select" on table F0001.MaSupTr. I use Worksheet log on as F0001 and run a select against MaSupTr it works but running the proc doesn't.

The version of Oracle is 8.1.7 on t64 unix, can this be somekind of a bug??

/Patrik Received on Sat Jan 03 2004 - 09:38:44 CST