Re: ORA-6512

Patrik wrote:
>
> 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

I doubt that you have encountered a bug.

The error manual states that a 00942 may be returned if "a view is used where a table is required." I note that F0001.SupTr_Gr is based on a view consisting of a join of 3 tables. Due to time constraints I have not tried to determine if F0001.SupTr_Gr is updateable, but there is a decent chance that it is not.

In this case, Oracle is seeing that your code would work if F0001.SupTr_Gr were a table, but it can't find any table with that name, and returns the error.

Quick test: try doing an update on F0001.SupTr_Gr in sql*plus. Use the columns you use in your procedure, and, if you succeed, rollback.

If this update works, then it really is a permission problem (but the problem is with F0001.SupTr_Gr).

HTH

-- 
Phil Singer                |    psinger1ATchartermiDOTnet
Oracle DBA
Remove the Obvious to reply