Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!news-feed01.roc.ny.frontiernet.net!nntp.frontiernet.net!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: amirrazakhan@hotmail.com (Amir)
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools,comp.databases.oracle.misc
Subject: Re: SQL Works but PL/SQL hangs
Date: 4 May 2003 23:45:49 -0700
Organization: http://groups.google.com/
Lines: 40
Message-ID: <8c1ec68c.0305042245.6fb7405c@posting.google.com>
References: <8c1ec68c.0305030010.525ac4af@posting.google.com> <6IPsa.2698$MJ5.230@fed1read03>
NNTP-Posting-Host: 203.128.8.70
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1052117150 16506 127.0.0.1 (5 May 2003 06:45:50 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 5 May 2003 06:45:50 GMT
Xref: core-easynews comp.databases.oracle.server:185290 comp.databases.oracle.tools:58780 comp.databases.oracle.misc:97304
X-Received-Date: Sun, 04 May 2003 23:45:19 MST (news.easynews.com)

"Ana C. Dent" <anacedent@hotmail.com> wrote in message news:<6IPsa.2698$MJ5.230@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@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@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