Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!cyclone1.gnilink.net!ngpeer.news.aol.com!newsfeed1!bredband!uio.no!193.75.75.20.MISMATCH!news.eunet.no!fu-berlin.de!uni-berlin.de!207.140.171.60!not-for-mail From: Chuck 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 13:12:57 GMT Lines: 59 Message-ID: References: <8c1ec68c.0305030010.525ac4af@posting.google.com> <6IPsa.2698$MJ5.230@fed1read03> <8c1ec68c.0305042245.6fb7405c@posting.google.com> NNTP-Posting-Host: 207.140.171.60 X-Trace: fu-berlin.de 1052140377 16435270 207.140.171.60 (16 [85580]) User-Agent: Xnews/5.04.25 Xref: core-easynews comp.databases.oracle.server:185304 comp.databases.oracle.tools:58786 comp.databases.oracle.misc:97314 X-Received-Date: Mon, 05 May 2003 06:12:29 MST (news.easynews.com) amirrazakhan@hotmail.com (Amir) wrote in news:8c1ec68c.0305042245.6fb7405c@posting.google.com: > "Ana C. Dent" 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 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.