DBD::Oracle -- an Oracle 7 interface for Perl 5. Copyright (c) 1994,1995,1996 Tim Bunce You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file. PLEASE READ THE ENTIRE README FILE CAREFULLY ! * WARNING: THIS IS MOSTLY ALPHA SOFTWARE. Your mileage may vary. * * THE ORAPERL EMULATION IS NO LONGER ALPHA. IT IS NOW FULLY RELEASED. * * The only currently supported interface is via Oraperl.pm, the * * oraperl emulation layer. This is fairly complete and stable. * * Execute "perldoc Oraperl" after installing for full information. * *BEFORE* BUILDING, TESTING AND INSTALLING this you will need to: Build, test and install Perl 5 (at least 5.002beta2). It is very important to TEST it and INSTALL it! Build, test and install the DBI module (at least DBI 0.71). It is very important to TEST it and INSTALL it! Remember to *read* the DBI README file! Install enough Oracle software to enable DBD::Oracle to build. That includes Pro*C. BUILDING: perl Makefile.PL make If you have problems see the 'IF YOU HAVE PROBLEMS' section below. If it's builds without error you can then run the tests. For the main test to work it must be able to connect to an Oracle database. Don't worry about most warnings, specifically "end-of-loop code not reached", "ANSI C forbids braced-groups within expressions", "cast increases required alignment of target type" and "passing arg 2 of `oerhms' with different width due to prototype". You will need to set either the TWO_TASK or ORACLE_SID environment variables to the correct values for your database. Consult Oracle documentation for more details. Test your setting by connecting to the database using an Oracle tool such as sqlplus. Once you can do that then you can test DBD::Oracle knowing that it should work. The test will connect to the database using the value of the ORACLE_USER environment variable so you should set that to the correct value before starting the test. Please read README.login. make test make test TEST_VERBOSE=1 (if any of the t/* tests fail) make install (if the tests look okay) IF YOU HAVE PROBLEMS: Do not hand edit the generated Makefile unless you are completely sure you understand the implications! Always try to make changes via the Makefile.PL command line and/or editing the Makefile.PL. You should not need to make any changes. If you do *please* let me know so that I can try to make it automatic in a later release. If you just can't login then read README.login and edit test.pl to suit. If you can't get it to build on a minimally configured client system then read README.client, it might help but basically I can't help much. Others on the dbi-users mailing list probably can. Please don't post problems to comp.lang.perl.misc or perl5-porters. This software is supported via the dbi-users mailing list. For more information and to keep informed about progress you can join the a mailing list via http://www.fugue.com/dbi (if you are unable to use the web you can subscribe by sending a message to dbi-request@fugue.com, it may take a few days to be processed). Please post details of any problems (or changes you needed to make) to dbi-users@fugue.com and CC them to me at Tim.Bunce@ig.co.uk. ** IT IS IMPORTANT TO INCLUDE THE FOLLOWING INFORMATION: 1. A complete log of a all steps of the build, e.g.: perl Makefile.PL (do a make realclean first) make make test make test TEST_VERBOSE=1 (if any tests fail) 2. Full details of which version of Oracle and Perl you're using. 3. The output of perl -V 4. If you get errors like "undefined symbol", "symbol not found" or "Text relocation remains" then include the output of "perl Makefile.PL -s XXX" where XXX is the name of one of the symbols. Please don't send the entire output of this command, just any obviously 'interesting' parts (if there are any). 5. If you get a core dump when using Oraperl try adding this *before* your 'use Oraperl' line: BEGIN { $Oraperl::safe = 1 } unless your version of Oraperl.pm already has $safe=1 in it (it does by default now). 6. If you still get a core dump rebuild DBD::Oracle with debugging enabled by executing: perl Makefile.PL -g (note the -g option) then rerun the code to get a new core dump file, finally use a debugger (gdb, sdb, dbx, adb etc) to get a stack trace from it. NOTE: I may not be able to help you much without a stack trace! It is worth fetching and building the GNU GDB debugger (4.15) if you don't have a good debugger on your system. If desparate try: make perl; ./perl script; echo '$c' | adb ./perl core 7. If the stack trace mentions XS_DynaLoader_dl_load_file then rerun make test after setting the environment variable PERL_DL_DEBUG to 2. It is important to check that you are using the latest version before posting. If you're not then I'm *very* likely to simply say "upgrade to the latest". You would do yourself a favour by upgrading beforehand. Please remember that I'm _very_ busy. Try to help yourself first, then try to help me help you by following these guidelines. Regards, Tim. =============================================================================== Examples and other info: DBI 'home page': http://www.hermetica.com/technologia/DBI Master archive site for Perl DB information: ftp://ftp.demon.co.uk/pub/perl/db/ Mailing list archive: /DBI/perldb-interest/ Perl 4 Oraperl (v2.4) /perl4/oraperl/ Searchable index of the dbi-users mailing list: http://www.coe.missouri.edu/~faq/lists/dbiusers/ ftp://ftp.bf.rmit.edu.au/pub/Oracle/sources/... Jeff Stander's stuff stands out for Oraperl: Directories of interest might be /pub/Oracle/sources /pub/Oracle/sources/jstander /pub/Oracle/sources/jstander/distrib /pub/Oracle/sources/jstander/tsmlib /pub/Oracle/sources/jstander/wdbex /pub/Oracle/sources/web/scripts /pub/Oracle/sources/dba /pub/Oracle/sources/dba/imp2sql7 /pub/Oracle/sources/Lonnroth /pub/Oracle/sources/harrison Send stuff for the archive in [.{cpio|tar|zip}][.{gz|Z|zip}].uu format if by mail to me (orafaq@bf.rmit.edu.au) And drop the .uu if using ftp, putting file(s) in ftp://ftp.bf.rmit.edu.au/incoming/Oracle http://www.bf.rmit.edu.au/~orafaq/perlish.html ftp://ftp.bf.rmit.edu.au/pub/perl/db ftp://ftp.bf.rmit.edu.au/pub/Oracle ftp://ftp.bf.rmit.edu.au/pub/Oracle/sources ftp://ftp.bf.rmit.edu.au/pub/Oracle/OS/MS/NT/ntoraperl.zip =============================================================================== Example for reading LONG fields via blob_read: my $sql = "SELECT long_field FROM table_name WHERE id = :1"; my $csr = ora_open($lda, $sql) or die $ora_errstr; ora_bind($csr, $id) or die $ora_errstr; # ora_fetch() needs to be called in an array context my (@data) = ora_fetch($csr); my ($frag, $blob, $length, $ll, $offset); $blob = ''; $length = 4096; # use benchmarks to get best value for you $offset = 0; while (1) { $frag = $csr->blob_read(0, $offset, $length); last unless defined $frag; $ll = length $frag; last unless $ll; $blob .= $frag; $offset += $ll; } print $blob; With thanks to james.taylor@srs.gov and desilva@ind70.industry.net. =============================================================================== Some users of DBD::Oracle on non-sun platforms: (this is VERY out of date now, there are MANY users on many platforms) HP-UX 9: Terry Greenlaw Les Troyer Norbert Kiesel Greg Anderson Johann Griessler Dave Venus Unisys U6000/300: Alan Burlison SCO 3: Greg Gerber Sequent DYNIX/ptx 2: Alligator Descartes Jonathan Lemon Motorola M88100 (SVR3.2): Christian Murphy DEC UNIX 3.2: David J. DeWolfe DEC Alpha, OSF/1 v2: Royal Hansen Jonathan Olson Greg Gerber Davide Migliavacca SGI Iris Indigo R4000 Irix 5.3: Andrew G Wood AIX 3.2 Davide Migliavacca Joerg Senekowitsch AIX 4.1 Ken P. Nikolai NT: Jim Fox =============================================================================== Platform or Oracle Version specific notes: Note that although some of these refer to specific systems and versions the same or similar problems may exist on other systems or versions. ------------------------------------------------------------------------------- Can't find libclntsh.so at run time: Dave Moellenhoff : libclntsh.so is the shared library composed of all the other Oracle libs you used to have to statically link. Oracle didn't start providing it until 7.2 and later. libclntsh.so should be in $ORACLE_HOME/lib. If it's missing, try running $ORACLE_HOME/rdbms/lib/genclntsh.sh and it should create it. Also: Never copy libclntsh.so to a different machine or Oracle version. If DBD::Oracle was built on a machine with a different path to libclntsh.so then you'll need to set set an environment variable, typically LD_LIBRARY_PATH, to include the directory containing libclntsh.so. ------------------------------------------------------------------------------- Error while trying to retrieve text for error ...: From Lou Henefeld : We discovered that we needed some files from the $ORACLE_HOME/ocommon/nls/admin/data directory: lx00001.nlb, lx10001.nlb, lx1boot.nlb, lx20001.nlb If your national language is different from ours (American English), you will probably need different nls data files. ------------------------------------------------------------------------------- Oracle 7.3 on Solaris 2.5 (maybe others): libc internal error: _rmutex_unlock: rmutex not held. Try each of these in turn: - Try removing -lthread from the list of libraries to link with (edit Makefile). - Try passing explicit db sid name to ora_login or connect. - Try removing clntsh from the list of libraries to link to (edit Makefile). If any of these work please let me know (include full version info). ------------------------------------------------------------------------------- Bad free() warnings: These are generally caused by problems in Oracle's own library code. You can use this code to hide them: $SIG{__WARN__} = sub { warn $_[0] unless $_[0] =~ /^Bad free/ } But please let me know if you have this problem (include full version info). Rather than simply hiding it it would be much better to help me fix it! Follow the instructions in step 5 above (for generating a version of DBD::Oracle with debugging enabled) and then use the following code to generate a core dump at the point the bad free happens. $SIG{__WARN__} = sub { $_[0] =~ /^Bad free/ ? dump : warn $_[0] } Follow the instructions in step 5 above for getting a stack trace from the core file and then send it to me along with the other information requested in the 'If you have problems' section. ------------------------------------------------------------------------------- 8-bit text is returned as '?' characters. Make sure the following environment vaiables are set: NLS_LANG, ORA_NLS, ORA_NLS32 Thanks to Robin Langdon for this information. Example: $ENV{NLS_LANG} = "american_america.we8iso8859p1"; $ENV{ORA_NLS} = "/home/oracle/ocommon/nls/admin/data"; $ENV{ORA_NLS32} = "/home/oracle/ocommon/nls/admin/data"; ------------------------------------------------------------------------------- HP-UX 9.x: Terry Greenlaw I traced a problem with "ld: Invalid loader fixup needed" to the file libocic.a. On HP-UX it contains position-dependant code and cannot be used to generate dynamic load libraries. The only shared library that Oracle ships under HP-UX is liboracle.sl which replaces libxa.a, libsql.a, libora.a, libcvg.a, and libnlsrtl.a. The OCI stuff still appears to only link statically under HU-UX 9.x [10.x is okay]. You'll need to build DBD::Oracle statically linked into the perl binary. See the static linking notes below. HP-UX 10 and Oracle 7.2.x do work together when creating dynamic libraries. The problem was older Oracle libraries were built without the +z flag to cc, and were therefore position-dependent libraries that can't be linked dynamically. Newer Oracle releases don't have this problem and it may be possible to even use the newer Oracle libraries under HP-UX 9. Oracle 7.3 will ONLY work under HP-UX 10, however. ------------------------------------------------------------------------------- For platforms which require static linking. If you get an error like: /usr/lib/dld.sl: Bad magic number for shared library: /usr/local/lib/perl5/hpux/auto/DBD/Oracle/Oracle.a You'll need to build DBD::Oracle statically linked and then link it into a perl binary: perl Makefile.PL LINKTYPE=static make make perl (makes a perl binary in current directory) make test FULLPERL=./perl (run tests using the new perl binary) make install You will probably need to have already built and installed a static version of the DBI in order that it be automatically included when you do the 'make perl' above. ------------------------------------------------------------------------------- Error: Syntax error in oratypes.h Try uncommenting the '#define signed' line in Oracle.h ------------------------------------------------------------------------------- Oracle 7.1 and 7.2: Connection takes a long time and may coredump Oracle bug number: 227321 related to changing the environment before connecting to oracle. To work around this bug, do not set any environment variables in your oraperl script before you call ora_login, and when you do call ora_login, the first argument must be the empty string. This means that you have to be sure that your environment variables ORACLE_SID and ORACLE_HOME are set properly before you execute any oraperl script. It is probably also possible to pass the SID to ora_login as part of the username (for example, ora_login("", "SCOTT/TIGER@PROD", "")), although I have not tested this. This workaround is based on information from Kevin Stock. ------------------------------------------------------------------------------- Oracle 7.1.4/5: ORA-01002: fetch out of sequence Edited from Message-ID: <3qaui9$ae@usenet.rpi.edu> and <3qb5dp$2nc@usenet.rpi.edu> in comp.databases.oracle From: finkej@ts.its.rpi.edu (Jon Finke) Every test run results in an "ORA-01002: fetch out of sequence" error. The manual claims that this is the result of calling "ofetch" before oparse/oexec, or running out of things to fetch. Yet, I am calling oopen, oparse, oexec, (odesc, odefin) all without error. As a control case, I also built the cdemo2 program, and it appears to be calling the same routines, yet it is successful. Note: In the "broken" program, I am currently calling odsc in a loop for each column in the query, and then calling odefin, while in the "working" program, I call odescr/odefin, odesc/odefin for each column. In both cases, the calls to odefin seem reasonable. Both programs are attempting the query "select user,uid from dual". This is a known problem in version 7.1.4, and it will be fixed in 7.1.6. The bug report is #239128. -- Jon Finke finkej@rpi.edu Senior Network Systems Engineer http://www.rpi.edu/~finkej Information Technology Services 518 276 8185 (voice) | 518 276 2809 (fax) Rensselaer Polytechnic Institute 110 8th Street, Troy NY, 12180 ------------------------------------------------------------------------------- Error: ORA-00604: error occurred at recursive SQL level (DBD: login failed) This can happen if TWO_TASK is defined but you connect using ORACLE_SID. ------------------------------------------------------------------------------- Error: ld: Undefined symbols _environ _dlopen _dlclose ... Environment: SunOS 4.1.3, Oracle 7.1.6 Steve Livingston If you get link errors like: ld: Undefined symbols _environ _dlopen _dlclose ... and the link command line includes '-L/usr/5lib -lc' then comment out the 'CLIBS= $(OTHERLIBS) -L/usr/5lib -lc' line in the Makefile. ------------------------------------------------------------------------------- Environment: SVR4, stephen.zander@mckesson.com Error: can't load ./blib/arch/auto/DBD/Oracle/Oracle.so for module DBD::Oracle: DynamicLinker:/usr/local/bin/perl:relocation error:symbol not found:setitimer Fix: Try adding the '-lc' to $ORACLE_HOME/rdbms/lib/sysliblist (just make sure it's not on a new line). ------------------------------------------------------------------------------- Error: Undefined symbols __cg92_used at link time. Environment: Solaris, GCC Fix: If you're compiling Oracle applications with gcc on Solaris you need to link with a file called $ORACLE_HOME/lib/__fstd.o. If you compile with the SparcWorks compiler you need to add the command line option on -xcg92 to resolve these symbol problems cleanly. Alligator Descartes ------------------------------------------------------------------------------- Environment: SunOS 4.1.3, Oracle 7.1.3 John Carlson Problem: oraperl and DBD::Oracle fail to link. Some messing around with the library order makes the link succeed. Now I get a "Bad free()" when ora_logoff is called. Solution: In my case, this was caused by a faulty oracle install. The install grabbed the wrong version of mergelib (The X11R6 one) instead of the one in $ORACLE_HOME/bin. Try a more limited path and reinstall Oracle again. ------------------------------------------------------------------------------- Environment: DEC Alpha, OSF, Jonathan Olson Problem: ERROR EVENT: DBI::dr=HASH(0x14011fee8)->DBI::default_handler: 0 ORA-00000: normal, successful completion (DBD: login failed) Solution: Add '#define A_OSF' to Oracle.h above '#include ' and complain to Oracle about bugs in their header files on 64 bit systems. =============================================================================== End.