Re: Perl::DBI problems after charset change (MORE INFO -- longish

On Thu, Oct 03, 2002 at 06:38:28AM -0800, Jesse, Rich wrote:
> Already did -- and deftly checking the list archives I see that MS Lookout
> has once again thwarted me by throwing away a reply by Tim Bunce himself.

That'll be this one...

Tim [also who skims oracle-l sometimes...]

On Tue, Oct 01, 2002 at 04:17:51PM -0500, Jesse, Rich wrote:
> Hi,
>
> A Solaris system that I have little control over is running Perl 5.005_03,
> and a DBI_TRACE level 2 shows DBI v1.13 and DBD::Oracle v1.03. The Oracle
> client on the Solaris server is 8.0.5.0.0. The Oracle DB we're connecting
> to is 8.1.7.4.0 on HP/UX.
>
> After changing the 8.1.7 DB's characterset from US7ASCII to WE8ISO8859P1,
> I've been receiving constant "ORA-1017, invalid username/password" errors on
> the DB's audit trail. Amazingly, no users complained. After a
> support-level network trace, it appears that simple connect statements like
> this one:
>
> $dbh = DBI->connect("dbi:Oracle:MYSID","myuser","mypass");
>
> ....fail with the ORA-1017, but then automatically retry the connection and
> succeed without reporting the error. All other Oracle Client tools on this
> Solaris machine, like SQL*Plus, work as normal. The DBI/DBD::Oracle error
> occurs over several Perl scripts for various applications.
>
> The trace also showed something I haven't seen before -- the password on the
> second try (the successful one) was sent unencrypted.
>
> Is this a documented problem in the somewhat dated versions of DBI and/or
> DBD::Oracle? Is there a documented fix? I can't ask to upgrade any of this
> without evidence that the upgrade will fix the problem.

DBD::Oracle has no connect retry logic in it. Looks like Oracle's client library is doing it. Do a DBI trace level 9 to see the OCI calls being used.

> BTW, I've attempted to match the NLS_LANG on the client by setting it to
> AMERICAN_AMERICA.WE8ISO8859P1. But while SQL*Plus works fine with this,
> DBI/DBD::Oracle doesn't:
>
> DBI->connect failed: ORA-12705: invalid or unknown NLS parameter value
> specified (DBD: login failed) at ./cursor_sharing_yes.pl line 17

DBD::Oracle has no NLS parameter code in the connect logic. Looks like Oracle's client library is doing it (from the env var).

It's possible that DBD::Oracle is missing some OCI calls that it should make, and if anyone can tell me what they are I'll happily add them!

[Ding! A lightbulb over head moment...] Solaris now has a very cool tool called apptrace which can trace calls from any application into any shared library it uses. You could use it to trace what OCI calls SQL*Plus is using to connect. Let me know what you find. (Anyone else on Solaris (>=8 I think) is welcome to try this and send me the relevant info.)

Tim.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Bunce
  INET: Tim.Bunce_at_pobox.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).