Re: SQL Works but PL/SQL hangs

chuckh_at_softhome.net said...
> 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>...
> > 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.
>

It's true they can communicate with each other ... but the syntax in one particular Net8 file (tnsnames.ora) has changed between Oracle7 and Oracle8.

Under Oracle7 your entry says "sid=..." and under Oracle8 it probably says "service_name=...". For backward compatibility, Net8 works with the "sid=..." (at least in 8i; don't know about 9i).

You might want to verify your tnsnames.ora. But I don't know why that would cause your app to "hang." You should just get the "cannot resolve service_name" error.

Another thing to check is that if your SELECT returns more than one row, you can't select it into a scalar variable. You'll need a WHERE clause. You should also consider putting an EXCEPTION section in your PL/SQL. I understand your sample code was just that ... and not meant to be the real code.

-- 
/Karsten
DBA > retired > DBA