DBD::O...ooting(3pm)

📦 version 1.83, in debian bookworm

💖 DBD::Oracle::Troubleshooting 💖

okay bestie, so this is DBD::Oracle::Troubleshooting — literally the "tips and hints to troubleshoot DBD::Oracle" page. If DBD::Oracle is being a little diva and won't connect, this is your girl. 🫶

✨ VERSION ✨

version 1.83 — yeah she's current, we stan.

🌈 CONNECTING TO ORACLE 🌈

So if you're reading this, we're assuming the glow-up already happened: you successfully installed DBD::Oracle, and now you're having some problems connecting to Oracle. Classic. Let's fix it. 💅

First things first, you gotta tell DBD::Oracle where the binaries live — specifically the Oracle client it was compiled against. This is the whole situation when you run into:

DBI connect('','system',...) failed: ERROR OCIEnvNlsCreate.

on Linux, or on Windows when you get:

OCI.DLL not found

The Linux fix is to make sure your ORACLE_HOME environment variable (or LD_LIBRARY_PATH if you're on InstantClient) points to the right directory. Like so:

export ORACLE_HOME=/app/oracle/product/xx.x.x

On Windows, the fix is to add that value to your PATH:

PATH=c:\app\oracle\product\xx.x.x;%PATH%

Okay so you got past that stage, iconic. But now you're getting:

ORA-12154: TNS:could not resolve the connect identifier specified

Most likely cause? DBD::ORACLE can't find your .ORA files — that's TNSNAMES.ORA, LISTENER.ORA, SQLNET.ORA. The fix is to set the TNS_ADMIN environment variable to the directory where those files are chillin'.

And if you get to this stage and you have either one of these:

ORA-12560: TNS:protocol adapter error
ORA-12162: TNS:net service name is incorrectly specified

that usually means DBD::Oracle can find the listener, but it can't connect to the DB because the listener can't find the DB you asked for. It's giving "I know a guy but he's not picking up." 😭

🎀 Oracle utilities 🎀

Still stuck connecting? The Oracle adapters utility might come through for you. Run these two commands:

$ORACLE_HOME/bin/adapters
$ORACLE_HOME/bin/adapters $ORACLE_HOME/bin/sqlplus

and check the output. The "Protocol Adapters" should include at least "IPC Protocol Adapter" and "TCP/IP Protocol Adapter".

If it generates any errors that look relevant, please talk to your Oracle technical support — and not the dbi-users mailing list, bestie, they don't deserve that. 🙈

🦋 Connecting using a bequeather 🦋

If you're using a bequeather to connect to a server on the same host as the client, you might have to add:

bequeath_detach = yes

to your sqlnet.ora file — otherwise you won't be able to safely use fork/system functions in Perl. no cap.

See the discussion at <http://www.nntp.perl.org/group/perl.dbi.dev/2012/02/msg6837.html> and <http://www.nntp.perl.org/group/perl.dbi.users/2009/06/msg34023.html> for more gory details. 🔥

💜 USING THE LONG TYPES 💜

Some examples related to the use of LONG types are available in the examples/ directory of the distribution. Go look, they're right there.

🌸 Can't find libclntsh.so 🌸

libclntsh.so is the shared library composed of all the other Oracle libs you used to have to statically link. She should be in $ORACLE_HOME/lib. If she's missing, try running $ORACLE_HOME/lib/genclntsh.sh and it should create it. ✨

Never copy libclntsh.so to a different machine or Oracle version. Not ever. Not even once.

If DBD::Oracle was built on a machine with a different path to libclntsh.so, then you'll need to set an environment variable — typically LD_LIBRARY_PATH — to include the directory containing libclntsh.so.

LD_LIBRARY_PATH is typically ignored if the script is running set-uid (which is common in some httpd/CGI configurations). In this case, either rebuild with LD_RUN_PATH set to include the path to libclntsh, or create a symbolic link so that libclntsh is available via the same path as it was when the module was built. (On Solaris the command ldd -s Oracle.so can be used to see how the linker is searching for it.)

👑 Miscellaneous 👑

💀 Crash with an open connection and Module::Runtime in mod_perl2 💀

See RT 72989 (https://rt.cpan.org/Ticket/Display.html?id=72989)

Apache2 MPM Prefork with mod_perl2 will crash if Module::Runtime is loaded, and an Oracle connection is opened through PerlRequire (before forking).

It looks like this was fixed in 0.012 of Module::Runtime. slay.

🍓 bind_param_inout swapping return values 🍓

See RT 71819 (https://rt.cpan.org/Ticket/Display.html?id=71819)

It seems that in some older versions of Oracle Instant Client (certainly 10.2.0.4.0) when output parameters are bound with lengths greater than 3584 the output parameters can be returned in the wrong placeholders. not the vibe. 🙈

It is reported fixed in Instant Client 11.2.0.2.0. 💅

⭐ AUTHORS ⭐

  • Tim Bunce <timb@cpan.org>
  • John Scoles <byterock@cpan.org>
  • Yanick Champoux <yanick@cpan.org>
  • Martin J. Evans <mjevans@cpan.org>

the icons, honestly. 👑

This software is copyright (c) 2022, 2021, 2019, 2014, 2013, 2012, 2011, 2010 by Tim Bunce.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself. periodt. 💖

and that's the whole page, bestie!! this was pulled from perl v5.36.0, dated 2023-02-26 — go forth and connect, you've got this 🦋💅✨

📦 where this page comes from