2.2 Installing Perl DBI Now that you've installed Perl itself, you need to set things up so your Perl programs can communicate with your Oracle database. The best way to do this is via the magic of the Perl DBI module and its Oracle-specific database driver, DBD::Oracle . These modules let us gain access to our target database through the Oracle Call Interface provided by Oracle Corporation. The architecture for this arrangement, which takes full advantage of the object-oriented features available within Perl 5, is shown in Figure 2-2. This figure also demonstrates how the same Perl DBI interface can be used, with other drivers, to connect to other databases, and how all of these drivers are hidden from your Perl scripts by the DBI package. [1] [1] You can even access different database types within the same Perl script using DBI. Doing so can be especially useful when you want to transfer information from one database type to another without having to use Oracle's SQL*Loader product. This section focuses on the installation of Perl DBI. For more information about Perl DBI's capabilities, see Appendix B, and the book and online references listed in Chapter 1. For complete online information, go to http://dbi.perl.org. We'll show how to install the DBI modules for both Unix and Win32. Figure 2-2. The Perl DBI architecture  Before we get to the DBI and DBD::Oracle modules, however, we need to take a step back to discuss the methodology we'll be using for installing Perl modules onto Unix systems, both here and in the rest of the book. 2.2.1 Methods for Installing Perl Modules There are two basic approaches to installing Perl modules (for example, Perl DBI, DBD::Oracle , and the many other modules we'll be discussing in later chapters) on Unix systems. The first is what some people call the traditional method . The second is the CPAN method. We recommend the traditional method, as we describe in the next section, but because the CPAN method is quite popular, we'll describe that one here as well. 2.2.1.1 The traditional method Briefly, the traditional method consists of the following steps: -
Download a module's tarball from cpan.org. -
Unpack it. -
Build it. -
Test it. -
Install it. This process often requires specifying the following steps on the command line (once the module tarball has been downloaded from the CPAN site, http://www.cpan.org). $ gzip SomeModule-1.00.tar.gz # Unzip archive $ tar xvf SomeModule-1.00.tar # Unpack archive $ cd SomeModule-1.00 # Enter archive $ perl Makefile.PL # Configure the build $ make # Build, or compile, the module $ make test # Test the module's compilation $ make install # Install the tested module There are a lot of keystrokes here. However, there is a way to cut down on this effort, and that's to use the CPAN module described in the next section. 2.2.1.2 The CPAN method The CPAN module (a separate entity from CPAN itself) provides a streamlined way to install Perl modules. You can learn the details of this built-in module by running the following commands: [2] [2] The perldoc (Perl documentation) program itself is installed automatically with Perl, as part of the general Perl development environment. $ perldoc perlmodinstall $ perldoc CPAN If you have a valid Internet connection open , you will have two ways of using CPAN . You can use either an interactive shell or a direct command-line instruction. We'll load up two modules in the following sections using these methods.  | The CPAN module comes prebundled with Perl. When you run its shell (described in the next section) for the first time, it will ask you a one-time series of short configuration questions. Once you've completed these, you're ready to start installing online! | | 2.2.1.3 The interactive CPAN shell First, we'll try the interactive CPAN shell, and install Number::Format , a helpful Perl module for manipulating number and string displays, particularly financial data: -
We enter the shell via the following command: $ perl -MCPAN -e "shell" cpan shell CPAN exploration and modules installation (v1.59_51) ReadLine support enabled cpan> -
We then install Number::Format with a straightforward instruction at the cpan> prompt. This sets off a train of events in which CPAN goes off to the nearest cpan.org mirrors, First, it interrogates the Comprehensive Perl Archive Network, as to whether our target module really exists. After validating this fact and a few other related pieces of information, it automatically downloads the latest existing tarball from the mirror and installs it for you. It does all this via some Perl magic of which Mithrandir himself would be proud: cpan> install Number::Format CPAN: Net::FTP loaded ok Fetching with Net::FTP: ftp://ftp.demon.co.uk/pub/CPAN/authors/01mailrc.txt.gz Going to read /home/andyd/.cpan/sources/authors/01mailrc.txt.gz CPAN: Compress::Zlib loaded ok Fetching with Net::FTP: ftp://ftp.demon.co.uk/pub/CPAN/modules/02packages.details.txt.gz Going to read /home/andyd/.cpan/sources/modules/02packages.details.txt.gz Fetching with Net::FTP: ftp://ftp.demon.co.uk/pub/CPAN/modules/03modlist.data.gz Going to read /home/andyd/.cpan/sources/modules/03modlist.data.gz Running install for module Number::Format Running make for W/WR/WRW/Number-Format-1.44.tar.gz Fetching with Net::FTP: ftp://ftp.demon.co.uk/pub/CPAN/authors/id/W/WR/WRW/Number- Format-1.44.tar.gz CPAN: MD5 loaded ok Fetching with Net::FTP: ftp://ftp.demon.co.uk/pub/CPAN/authors/id/W/WR/WRW/CHECKSUMS Checksum for /home/andyd/.cpan/sources/authors/id/W/WR/WRW/Number- Format-1.44.tar.gz ok Scanning cache /home/andyd/.cpan/build for sizes CPAN.pm: Going to build W/WR/WRW/Number-Format-1.44.tar.gz Writing Makefile for NumberFormat /usr/bin/make -- OK Running make test No tests defined for NumberFormat extension. /usr/bin/make test -- OK Running make install Writing /usr/lib/perl5/site_perl/5.6.1/i686- linux/auto/NumberFormat/.packlist Appending installation info to /usr/lib/perl5/5.6.1/i686- linux/perllocal.pod /usr/bin/make install -- OK cpan> Although the preceding is a lot of output, it has taken our fingers relatively few keystrokes to get Number::Format installed. In addition to the interrogation shown here, notice the three build and installation steps we've highlighted ” make , make test , and make install ” all done automatically. -
Once we've finished installing new packages, we simply quit out of the shell: cpan> quit Lockfile removed. $ Number::Format (and any other requested modules) is now installed, as if you had done it by hand. 2.2.1.4 CPAN from the command line Using CPAN directly from the command line is even easier than using the interactive CPAN shell. We'll use it to get another useful Perl module, the Convert::EBCDIC bundle, which deals with IBM mainframe EBCDIC data and its conversion to and from ASCII formats. Follow these steps: -
This time, we'll install our target module directly, with a single command at the operating system prompt: $ perl -MCPAN -e "install 'Convert::EBCDIC' " -
This runs through a processing operation that's similar to the shell method shown earlier: Going to read /home/andyd/.cpan/sources/authors/01mailrc.txt.gz CPAN: Compress::Zlib loaded ok Going to read /home/andyd/.cpan/sources/modules/02packages.details.txt.gz Going to read /home/andyd/.cpan/sources/modules/03modlist.data.gz Running install for module Convert::EBCDIC Running make for C/CX/CXL/Convert-EBCDIC-0.06.tar.gz CPAN: Net::FTP loaded ok Fetching with Net::FTP: ftp://ftp.demon.co.uk/pub/CPAN/authors/id/C/CX/CXL/Convert- EBCDIC-0.06.tar.gz CPAN: MD5 loaded ok Fetching with Net::FTP: ftp://ftp.demon.co.uk/pub/CPAN/authors/id/C/CX/CXL/CHECKSUMS Checksum for /home/andyd/.cpan/sources/authors/id/C/CX/CXL/Convert- EBCDIC-0.06.tar.gz ok Scanning cache /home/andyd/.cpan/build for sizes CPAN.pm: Going to build C/CX/CXL/Convert-EBCDIC-0.06.tar.gz Writing Makefile for ConvertEBCDIC /usr/bin/make -- OK Running make test No tests defined for ConvertEBCDIC extension. /usr/bin/make test -- OK Running make install Writing /usr/lib/perl5/site_perl/5.6.1/i686- linux/auto/ConvertEBCDIC/.packlist Appending installation info to /usr/lib/perl5/5.6.1/i686- linux/perllocal.pod /usr/bin/make install -- OK $ Convert::EBCDIC should now be fully installed. (We discuss both Number::Format and Convert::EBCDIC in Appendix D.) 2.2.1.5 The traditional method As you can see, the CPAN installation method is very convenient . However, like many Perl developers, we've chosen to use the older paradigm because it gives us better control and is more reliable. Many of the modules we're going to discuss in this book have compilation routes that deviate from the norm. CPAN is a fire-and-forget missile. You press the button, and away it goes, but it always expects our target to be in view. If the target is elsewhere, it's intelligent enough to try to adapt, but it may still fail to do exactly what you wish, and consequently miss the mark. On the other hand, because of the greater number of steps required by the traditional method, we actually achieve better granularity when we use that method, and we find it particularly helpful when explaining Perl to others. We can hop between steps and offer you more advice on debugging, alternative location installations, and other compilation tips (particularly in places where the Perl module is a glue layer camouflaging the more difficult API of a passenger C library, which itself needs compilation). [3] [3] You can think of the difference between the CPAN and traditional methods as analogous to the old conflict between what's called, in military circles, Sigint (Signals Intelligence) and Humint (Human Intelligence). Sigint, or CPAN , is easier to deploy and gather information from, whereas the traditional cloak -and-dagger Humint, pays for its greater management overhead with a greater depth of penetration. Thus, from here on, we're going to sidestep the CPAN module and stick mainly to la methode traditionelle as we discuss Perl installation. However, if you ever get jealous of Win32 people using PPM (the Perl Package Manager, which we describe in the later section, Section 2.2.3), nothing stops from you using CPAN ; we promise to turn a blind eye, especially as we use PPM quite often. 2.2.2 Installing Perl DBI on Unix In this section, we'll explain how to install Perl DBI using the following tarballs: - DBI-1.20.tar.gz
- DBD-Oracle-1.12.tar.gz
The central locations for these are: - http://www.perl.com/CPAN/modules/by-module/DBI
- http://www.perl.com/CPAN/modules/by-module/DBD
You can obtain the relevant interface and driver packages by clicking on and saving the files from the appropriate links. Save them to a Perl module repository. 2.2.2.1 Installing Perl DBI Once you have the relevant downloads, you can begin the Perl DBI installation. Before starting, always scan through the relevant README files; the following instructions do change gradually over time. If any problems occur with your installation, you'll find that the solution is most likely buried deep within either the DBI or the DBD::Oracle README files. Follow these steps: -
As a sanity check, make sure that the Perl version you installed earlier is set up correctly. Do this as the same user with which you installed Perl: $ type perl perl is hashed (/usr/bin/perl) $ perl -v This is perl, v5.6.1 built for i686-linux......... -
If this looks good, carry on with the Perl DBI installation by unpacking the tarball and checking the documentation: $ gzip -d DBI-1.20.tar.gz $ tar xvf DBI-1.20.tar $ cd DBI-1.20 $ vi README -
If you have no special requirements (as detailed in the README file), follow the standard Perl installation instructions. [4] If any step fails, you will need to sort out what's causing the problem before you can move on: [4] If you installed Perl earlier in a nonstandard directory as a non- root user, all subsequent module installations will automatically feed themselves into the correct library locations, and no further intervention will be required on your part. $ perl Makefile.PL This step may produce an informational note similar to the following: *** Note: The optional PlRPC-modules (RPC::PlServer etc) are not installed. If you want to use the DBD::Proxy driver and DBI::ProxyServer modules, then you'll need to install the RPC::PlServer, RPC::PlClient, Storable and Net::Daemon modules. The CPAN Bundle::DBI may help you. You can install them any time after installing the DBI . You do *not* need these modules for typical DBI usage . The DBI::ProxyServer and DBD::Proxy combination is an alternative approach to the one we describe in this section. It lets you avoid using DBD::Oracle on remote Oracle clients . We'll describe this approach, as well as the details of the dbiproxy daemon program, later in this chapter; the dbiproxy daemon is also displayed in Figure 2-4. To get dbiproxy to work, you'll need to install four more packages before installing DBI (remember that these are optional and unnecessary for typical DBI usage). You can always come back to these packages at a later time; for now, let's move on. -
Having configured Perl DBI, let's build, test, and install it. $ make $ make test $ make install Perl DBI should now be installed. But we need to keep the bubbly on ice, for a little while longer. The next stage is to pair DBI up with its partner, the DBD::Oracle driver. 2.2.2.2 Installing DBD::Oracle Follow these steps to install the DBD::Oracle module: -
At this point, make sure you have a test Oracle database running, with the appropriate TNS listener up. Also, if you're the root user, make sure you have the usual Oracle environment variables set up: ORACLE_HOME and ORACLE_SID (you may choose to use TWO_TASK , instead of ORACLE_SID , depending on your setup). You particularly need ORACLE_HOME to locate the OCI code libraries. Note that DBD::Oracle is similar in concept to the Type II fat JDBC drivers for use with the java.sql database connectivity package in Java. It needs at least Oracle client libraries available, in order to compile successfully: [5] [5] That is why DBI::ProxyServer and DBD::Proxy may be of interest for remote clients. They let you sidestep the requirement for Oracle client libraries at the remote end (see Figure 2-4). $ ORACLE_HOME=/u01/app/oracle/product/8.1.5 $ export ORACLE_HOME $ ORACLE_SID=orcl $ export ORACLE_SID -
To make sure the DBD::Oracle driver is working correctly (before its full installation in the make test step described later), you'll also need to set up the following special ORACLE_USERID environment variable. (Simply change the scott/tiger@orcl string on your own installation to a valid connection string on your own test database.) $ ORACLE_USERID= scott/tiger@orcl $ export ORACLE_USERID -
As a final environmental gotcha, you may also need to have your LD_LIBRARY_PATH environmental value pointing to all of the right little places on various Unix flavors. This will help ensure that DBD::Oracle will pick up the correct Oracle libraries: $ LD_LIBRARY_PATH=$LD_LIBRARY_PATH: $ORACLE_HOME/lib $ export LD_LIBRARY_PATH -
We're now ready to unpack DBD::Oracle : $ gzip -d DBD-Oracle-1.12.tar.gz $ tar xvf DBD-Oracle-1.12.tar $ cd DBD-Oracle-1.12 $ vi README -
As the Captain himself might have once said, "Transporter room, this is Kirk. Configure, build, and install": $ perl Makefile.PL $ make $ make test $ make install If you do encounter any errors with this installation (particularly on the make test step), you must sort them out before running the final make install step ” even if the errors appear to be nonfatal. That way, you'll have greater confidence when you're running production DBI scripts later on. Following this error hit list should remove most of the gremlins: -
Ensure that ORACLE_USERID is set correctly, as described earlier. -
Check that LD_LIBRARY_PATH can access the libraries residing in $ORACLE_HOME/lib . -
Make sure your Oracle database is up and running with adequate memory available in the shared pool, particularly if you know that the machine's memory is tight. -
Check that the correct Oracle listener is responding correctly. You can do this by connecting to it via a SQL*Plus session, from the same machine on which you're installing DBD::Oracle . -
Scan through the README files again with a fine-toothed comb. If you can't solve your problem (which should be a very rare case), you may be able to get help from the DBI users mailing lists or from the helpful information in the DBI FAQs. We explain how to access these at the end of Chapter 1. Once you've successfully completed the make install step, you'll find plenty of up-to-date Perl DBI documentation automatically loaded onto your system. You can ensure that the documentation has been loaded by running the following pair of commands: $ perldoc DBD::Oracle $ perldoc DBI 2.2.3 Installing Perl DBI on Win32 In contrast to the Unix installation, installing Perl DBI using ActiveState 's version of Perl is very straightforward. We recommend that for Win32 you use the PPM (Perl Package Manager) module, which installs automatically alongside ActivePerl with its utility ppm program (which runs it). PPM simplifies the tasks of locating, installing, upgrading, and removing software packages on Win32. It determines whether the most recent version of a software package is installed, and can install or upgrade that package from a local or remote host. PPM is very similar to the interactive CPAN shell module described earlier. Although PPM is usually run via its ppm interactive shell program, it can also be used directly on the command line. PPM uses PPD (Perl Package Description) files containing an extended form of the Open Software Description specification, [6] for information about software packages. These description files are written in XML. For more information on PPM on Win32, run the very helpful perldoc command; we'll say more about perldoc in Appendix A: [6] http://www.w3.org/TR/NOTE-OSD.html DOS> perldoc PPM We've chosen to use PPM ActivePerl packages, instead of hand- crafting Perl module installations, for the following reasons: -
Most Win32 users don't have a development environment in which to compile and test source code installations. -
Many Perl modules on Unix require the manual compilation of libraries such as zlib for compression, expat for XML parsing, or gd for dynamic image creation. All ActivePerl modules have precompiled these libraries for you into DLLs, where necessary, before you download them. These will save you a significant amount of work over compiling DLLs of your own. -
Just as compiling from source is the standard method for installing Perl on Unix, the de facto standard method on Win32 is to use PPM. 2.2.3.1 Running PPM For a comprehensive guide to installing Perl modules on Win32, check out the following site: - http://aspn.activestate.com/ASPN/Products/ActivePerl/faq/ActivePerl-faq2.html
Unless you're accessing the Internet through proxies, as we'll discuss shortly, the following steps should take only a few minutes: -
As with CPAN , make sure your PC is connected to the Internet before you run ActiveState's PPM. (Note that PPM itself was automatically installed when you loaded ActivePerl earlier.) If you're connected through a proxy, you'll need to set the Win32 environment variable HTTP_proxy to the name of your proxy server. You may also need to set the variables HTTP_proxy_user and HTTP_proxy_pass , if your server requires a username and password. If you need additional information, check out this web site: http://aspn.activestate.com//ASPN/Products/ActivePerl/faq/ActivePerl-faq2.html#ppm_and_proxies -
Now start up an MS-DOS window and type the PPM command shown here at the command-line prompt: C:\> ppm This will bring up the PPM program prompt. -
Install the ActivePerl DBI package as follows : PPM> install DBI -
When this completes, type: PPM> install DBD-Oracle8 This should load the latest Oracle DBD::Oracle package. -
Alternatively, if Oracle8 (or later) is unavailable on your system, you may wish to load a slightly earlier DBD::Oracle package instead: PPM> install DBD-Oracle -
Your Perl DBI installation process should now be complete. You can quit and prepare for the heady excitement of the "Hello World" example coming up. 2.2.3.2 Getting the latest PPD files For the very latest Perl DBI and DBD::Oracle packages on Win32, you can go beyond ActiveState and turn to a knight in shining armor , Ilya Sterin. Ilya regularly provides the latest binary compilations at the http://xmlproj.com/PPM web page. Check this site first! If the PPD files you're looking for are there, you can run the following PPM commands to obtain the very latest DBI and DBD::Oracle downloads: DOS> ppm PPM> remove DBD::Oracle PPM> remove DBI PPM> set repository XMLPROJ http://xmlproj.com/PPM/ PPM> install DBI-1_20 PPM> install DBD-Oracle-1_12 PPM> quit |
