Re: SQL Works but PL/SQL hangs

From: Chuck <chuckh_at_softhome.net>
Date: 5 May 2003 13:12:57 GMT
Message-ID: <Xns93725DBE72543chuckhsofthomenet@130.133.1.4>

amirrazakhan_at_hotmail.com (Amir) wrote in 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 everyone is barking up the wrong tree here. The issue they are talking about applies only to stored objects (stored procedures, packages, even views). To access any object via a stored object, the owner of the stored object must have permissions for the object to be accessed granted directly to him and not through a role. Without the permissions you would get an "object does not exist" type of error. This does not affect anonymous pl/sql like you have in your example.

To diagnose your problem I would log on 2 sessions. Run your pl/sql from the first and exmamine it's wait events from the second to see what it's hanging on. A session's current wait event can be found in v$session_event. All wait events for a session can be found in v$session_wait. Received on Mon May 05 2003 - 08:12:57 CDT