Re: SQL Works but PL/SQL hangs

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 03 May 2003 06:43:30 -0700
Message-ID: <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_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. Received on Sat May 03 2003 - 08:43:30 CDT