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. ** 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" for info. *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.69). It is very important to test it and install it! Remember to *read* the DBI README file! BUILDING perl Makefile.PL make make test make test TEST_VERBOSE=1 (if any of the t/* tests fail) make install (if the tests look okay) 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 never 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 HAVE PROBLEMS: 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. Please include: 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 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). 5. 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 6. If the stack trace mentions XS_DynaLoader_dl_load_file then rerun make test after setting the environment variable PERL_DL_DEBUG to 2. Regards, Tim. =============================================================================== Examples and other info: 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 ftp://ftp.demon.co.uk/pub/perl/db/perl4/oraperl/ =============================================================================== 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 Specific Platforms: (this is VERY out of date now, there are MANY users on many platforms) Solaris 1: Myself. John D Groenveld Christian Murphy Solaris 2: Myself. David Moellenhoff James Taylor Jay Krish John D Groenveld Joe Hildebrand John Liptak Philip Kizer Alan Johnson Jim Fox HP-UX 9: Terry Greenlaw Les Troyer Norbert Kiesel Greg Anderson Johann Griessler Dave Venus Unisys U6000/300: Alan Burlison SCO 3: Joel Rosi-Schwartz Greg Gerber Sequent DYNIX/ptx 2: Alligator Descartes Jonathan Lemon NT: Jim Fox 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 =============================================================================== Platform or Oracle Version specific notes: ------------------------------------------------------------------------------- Oracle 7.3 on Solaris 2.5 (maybe others): libc internal error: _rmutex_unlock: rmutex not held. - Try passing explicit db sid name to ora_login or connect. - Try removing clntsh from the list of libraries to link to (edit Makefile). - Try adding -lthread to 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 cause by problems in oracle 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) . ------------------------------------------------------------------------------- HP-UX: 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. You'll need to build DBD::Oracle statically linked into the perl binary. See the static linking notes below. ------------------------------------------------------------------------------- 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. ------------------------------------------------------------------------------- Oracle 7.1.3: Syntax error in oratypes.h Try uncommenting the '#define signed' line in Oracle.h ------------------------------------------------------------------------------- Oracle 7.1.3/4: 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) I am in the process of converting a locally developed that sits on top of the OCI routines from version 6 to version 7. Unfortunately, I have run into a snag that I have been unable to get around. Every test run results in getting 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 ------------------------------------------------------------------------------- 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. ------------------------------------------------------------------------------- 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). ------------------------------------------------------------------------------- Environment: Solaris, GCC Error: Undefined symbols __cg92_used at link time. 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. Problem: Unable to connect Solution: Try using something like this: "scott/tiger@t:HOST_IP:INSTANCE" and not just "scott/tiger". =============================================================================== End.