Re: SQL Works but PL/SQL hangs

amirrazakhan_at_hotmail.com (Amir) wrote in message news:<8c1ec68c.0305042245.6fb7405c_at_posting.google.com>...
> "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<6IPsa.2698$MJ5.230_at_fed1read03>...
> > Amir wrote:
> > > When I issue a statement to fetch some data from the remote database
> > > on the SQL prompt it works fine, as given below
> > >
> > > SQL> select trans_no from XYZ_at_remotedb;
> > >
> > > But when I embbed the same query in PL/SQL block it hangs and does not
> > > complete
> > >
> > > declare
> > > x varchar2(50);
> > > begin
> > > select trans_no into x from XYZ_at_remotedb;
> > > end;
> > >
> > >
> > > What could be the reason?
> > >
> > > ~~Amir
> >
> > I suspect that it is a "permissions" problem.
> >
> > Access to an object which are obtained via a ROLE
> > will work as desired using SQL*PLUS.
> > However access to an object when using PL/SQL
> > can ONLY be accomplished when the invoker has
> > been explicitly GRANTed access to the object.
> >
> > SQL*Net complicates the situation and if there are
> > version differences between the local & remote sessions
> > this too can impact the results.
>
> Thanx for quick and precise reply. But I am still not clear what sort
> of permissions do I require to run it thru PL/SQL.
> It would be great help if you could please post me the soloution of
> SQL*Net version difference problem. I mean is there any SQL*Net
> version compatibility parameter that need to be set ?
>
> ~~Amir

I think this problem is due to SQL*Net Compatibility. The database link connects the Oracle 8.1.7 to Oracle 7.3.3. Hence the Net8 communicate with the SQL*Net ver2. Can u please help me out in this compatibility problem? Thanks.

~~Amir Received on Mon May 05 2003 - 09:42:47 CDT