Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!newshosting.com!news-xfer1.atl.newshosting.com!fu-berlin.de!uni-berlin.de!207.140.171.60!not-for-mail
From: Chuck <chuckh@softhome.net>
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools,comp.databases.oracle.misc
Subject: Re: SQL Works but PL/SQL hangs
Date: Mon, 5 May 2003 11:14:53 -0400
Lines: 55
Message-ID: <1iu5ad2hko7xh$.coizh83pfhlz$.dlg@40tude.net>
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: 207.140.171.60
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Trace: fu-berlin.de 1052147694 14222906 207.140.171.60 (16 [85580])
User-Agent: 40tude_Dialog/2.0.5.1
Xref: core-easynews comp.databases.oracle.server:185315 comp.databases.oracle.tools:58792 comp.databases.oracle.misc:97318
X-Received-Date: Mon, 05 May 2003 08:14:28 MST (news.easynews.com)

On 5 May 2003 07:42:47 -0700, Amir wrote:

> 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.
> 
> ~~Amir

I don't think it's a compatibility problem. Net8 and Sqlnet 2.3 (Oracle
7.3.3) should be completely compatible. If you really want to identify why
the session's hanging, use a 2nd session to look at what it's waiting on
while it's hanging.