$Id: README,v 1.16 2002/11/22 19:47:52 jhorwitz Exp $ Oracle Perl Procedure Library Copyright (c) 2001, 2002 Jeff Horwitz (jeff@smashing.org). All rights reserved. This package is free software; you can redistribute it and/or modify it under the same terms as Perl itself. extproc_perl enables Oracle stored procedures and functions to be written in Perl. It uses Oracle 8's external procedure interface to create Perl interpreters on the fly to execute Perl subroutines. These interpreters are persistent throughout the life of the database session. A patch for DBD::Oracle is supplied that enables the use of DBI to make queries back to the calling database without establishing a new connection. Please send all bug reports, questions and suggestions to jeff@smashing.org. extproc_perl now has a home on the web: http://www.smashing.org/extproc_perl REQUIREMENTS ------------ o Perl 5.005_62 or later (built and tested with 5.6.1) o shared libperl o Oracle 8 server with OCI development libraries and headers (built and tested with 8.1.7 -- may work with earlier versions) o DBI and DBD::Oracle 1.08 or later. Needed for the "ep" command. BUILDING & INSTALLATION ----------------------- This is quick and dirty, and is only meant to give a general overview of the installation process. See the INSTALL file for more detailed information. 1) Set the ORACLE_HOME environment variable to the proper path 2) perl Makefile.PL 3) Enter the list of Perl modules you'd like to support 4) make -- This will build extproc_perl and the ExtProc Perl module. 5) make install -- This will install the ExtProc Perl module. 6) Copy the extproc_perl library (name is platform-dependent, but it's probably extproc_perl.so) to a location that an Oracle process can access. 7) Create a bootstrap file containing common Perl data and subroutines. For more details on steps 8 - 10, see the extproc_sample.sql file. 8) Have your DBA create an Oracle function that references the code in the library. see extproc_sample.sql for sample code. 9) Create a table in the database to store your code. 10) Have your DBA start a listener for extproc connections. 11) Use the "ep" command to import code into the database. PERSISTENCE ----------- Any Perl interpreters created by extproc_perl are persistent for the life of the Oracle external procedure process. According to Oracle documentation, this process should remain alive until the client process disconnects from the database. This means that you will have persistence throughout any particular session, but neither the data nor the interpreter will persist beyond that session. CODE FETCHING STRATEGY ---------------------- extproc_perl will read the bootstrap file if it exists each time the Perl interpreter is started (after the first perl subroutine call and after each call to _flush). If a call to an unknown Perl subroutine is made, extproc_perl will query the database for stored perl code. If it finds code, it parses it and revalidates that subroutine. If it now exists, the subroutine is run. Otherwise, the query fails with an "invalid subroutine" error message. This strategy ensures that bootstrap code will always be pre-loaded and that the database is only queried when necessary, eliminating any overhead associated with the query. BUGS & CAVEATS -------------- * Since modules are statically compiled into the binary, you must recompile in order to use any new modules installed since the last compile. This only applies to modules with shared object code (usually compiled from C). * Syntax checking using "perl -cw" will fail if you use the Extproc module, as it requires the extproc_perl library to be loaded. Set the LD_PRELOAD environment variable to the path of the library to check syntax in this case. * You cannot use DDL or DML statements from an extproc_perl script. This means you cannot do things like create or drop a table or insert or update rows in a table, effectively making the database read-only. * Code in bootstrap files is only available to databases local to that machine. If you are running parallel server, you will either have to replicate the bootstrap file across your servers or store your code in the database.