Re: SQL Works but PL/SQL hangs
From: Chuck <chuckh_at_softhome.net>
Date: Mon, 5 May 2003 11:14:53 -0400
Message-ID: <1iu5ad2hko7xh$.coizh83pfhlz$.dlg_at_40tude.net>
>>> 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.
Date: Mon, 5 May 2003 11:14:53 -0400
Message-ID: <1iu5ad2hko7xh$.coizh83pfhlz$.dlg_at_40tude.net>
[Quoted] On 5 May 2003 07:42:47 -0700, Amir wrote:
> 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
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. Received on Mon May 05 2003 - 17:14:53 CEST