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: joel-garry@home.com (Joel Garry)
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools,comp.databases.oracle.misc
Subject: Re: SQL Works but PL/SQL hangs
Date: 5 May 2003 12:10:04 -0700
Organization: http://groups.google.com/
Lines: 57
Message-ID: <91884734.0305051110.7ce998b0@posting.google.com>
References: <8c1ec68c.0305030010.525ac4af@posting.google.com> <6IPsa.2698$MJ5.230@fed1read03> <8c1ec68c.0305042245.6fb7405c@posting.google.com> <8c1ec68c.0305050642.2f83b41a@posting.google.com>
NNTP-Posting-Host: 67.116.125.178
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1052161805 19528 127.0.0.1 (5 May 2003 19:10:05 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 5 May 2003 19:10:05 GMT
Xref: core-easynews comp.databases.oracle.server:185337 comp.databases.oracle.tools:58796 comp.databases.oracle.misc:97336
X-Received-Date: Mon, 05 May 2003 12:09:33 MST (news.easynews.com)

amirrazakhan@hotmail.com (Amir) wrote in message news:<8c1ec68c.0305050642.2f83b41a@posting.google.com>...
> amirrazakhan@hotmail.com (Amir) wrote in message news:<8c1ec68c.0305042245.6fb7405c@posting.google.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
> 
> 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.
> 

If you have 8 on Sun and 7 on HP, search metalink for OOB.  That would
be a tcp incompatability between the implementations as to whether out
of band interrupts are allowed.

jg
--
@home.com is bogus.
You'd think they'd have tcp figured out by now.