Oraperl - Perl access to Oracle databases for old oraperl scripts


NAME

Oraperl - Perl access to Oracle databases for old oraperl scripts


SUPPORTED PLATFORMS


SYNOPSIS

  eval 'use Oraperl; 1' || die $@ if $] >= 5;  # ADD THIS LINE TO OLD SCRIPTS
  $lda = &ora_login($system_id, $name, $password)
  $csr = &ora_open($lda, $stmt [, $cache])
  &ora_bind($csr, $var, ...)
  &ora_fetch($csr [, $trunc])
  &ora_close($csr)
  &ora_logoff($lda)
  &ora_do($lda, $stmt)
  &ora_titles($csr)
  &ora_lengths($csr)
  &ora_types($csr)
  &ora_commit($lda)
  &ora_rollback($lda)
  &ora_autocommit($lda, $on_off)
  &ora_version()
  $ora_cache
  $ora_long
  $ora_trunc
  $ora_errno
  $ora_errstr
  $ora_verno
  $ora_debug


DESCRIPTION

Oraperl is an extension to Perl which allows access to Oracle databases.

The functions which make up this extension are described in the following sections. All functions return a false or undefined (in the Perl sense) value to indicate failure. You do not need to understand the references to OCI in these descriptions. They are here to help those who wish to extend the routines or to port them to new machines.

The text in this document is largely unchanged from the original Perl4 oraperl manual written by Kevin Stock <kstock@auspex.fr>. Any comments specific to the DBD::Oracle Oraperl emulation are prefixed by DBD:. See the DBD::Oracle and DBI manuals for more information.

DBD: In order to make the oraperl function definitions available in perl5 you need to arrange to 'use' the Oraperl.pm module in each file or package which uses them. You can do this by simply adding use Oraperl; in each file or package. If you need to make the scripts work with both the perl4 oraperl and perl5 you should add add the following text instead:

  eval 'use Oraperl; 1' || die $@ if $] >= 5;

Principal Functions

The main functions for database access are &ora_login(), &ora_open(), &ora_bind(), &ora_fetch(), &ora_close(), &ora_do() and &ora_logoff().

Ancillary Functions

Additional functions available are: &ora_titles(), &ora_lengths(), &ora_types(), &ora_autocommit(), &ora_commit(), &ora_rollback() and &ora_version().

The first three are of most use within a program which allows statements to be entered interactively. See, for example, the sample program sql which is supplied with Oraperl and may have been installed at your site.


VARIABLES

Six special variables are provided, $ora_cache, $ora_long, $ora_trunc, $ora_errno, $ora_errstr and $ora_verno.

Customisation Variables

These variables are used to dictate the behaviour of Oraperl under certain conditions.

Status Variables

These variables report information about error conditions or about Oraperl itself. They may only be read; a fatal error occurs if a program attempts to change them.


SUBSTITUTION VARIABLES

Oraperl allows an SQL statement to contain substitution variables. These consist of a colon followed by a number. For example, a program which added records to a telephone list might use the following call to &ora_open():

  $csr = &ora_open($csr, "insert into telno values(:1, :2)");

The two names :1 and :2 are called substitution variables. The function &ora_bind() is used to assign values to these variables. For example, the following statements would add two new people to the list:

  &ora_bind($csr, "Annette", "472-8836");
  &ora_bind($csr, "Brian", "937-1823");

Note that the substitution variables must be assigned consecutively beginning from 1 for each SQL statement, as &ora_bind() assigns its parameters in this order. Named substitution variables (for example, :NAME, :TELNO) are not permitted.

DBD: Substitution variables are now bound as type 1 (VARCHAR2) and not type 5 (STRING) by default. This can alter the behaviour of SQL code which compares a char field with a substitution variable. See the String Comparison section in the Datatypes chapter of the Oracle OCI manual for more details.

You can work around this by using DBD::Oracle's ability to specify the Oracle type to be used on a per field basis:

  $char_attrib = { ora_type => 5 }; # 5 = STRING (ala oraperl2.4)
  $csr = ora_open($dbh, "select foo from bar where x=:1 and y=:2");
  $csr->bind_param(1, $value_x, $char_attrib);
  $csr->bind_param(2, $value_y, $char_attrib);
  ora_bind($csr);  # bind with no parameters since we've done bind_param()'s


DEBUGGING

DBD: The Oraperl $ora_debug variable is not supported. However detailed debugging can be enabled at any time by executing

  $h->debug(2);

where $h is either a $lda or a $csr. If debugging is enabled on an $lda then it is automatically passed on to any cursors returned by &ora_open().


EXAMPLE

  format STDOUT_TOP =
  Name Phone
  ==== =====
  .
  format STDOUT =
  @<<<<<<<<<< @>>>>>>>>>>
  $name, $phone
  .
  die "You should use oraperl, not perl\n" unless defined &ora_login;
  $ora_debug = shift if $ARGV[0] =~ /^\-#/;
  $lda = &ora_login('t', 'kstock', 'kstock')
            || die $ora_errstr;
  $csr = &ora_open($lda, 'select * from telno order by name')
            || die $ora_errstr;
  $nfields = &ora_fetch($csr);
  print "Query will return $nfields fields\n\n";
  while (($name, $phone) = &ora_fetch($csr)) { write; }
  warn $ora_errstr if $ora_errno;
  die "fetch error: $ora_errstr" if $ora_errno;
  do ora_close($csr) || die "can't close cursor";
  do ora_logoff($lda) || die "can't log off Oracle";


NOTES

In keeping with the philosophy of Perl, there is no pre-defined limit to the number of simultaneous logins or SQL statements which may be active, nor to the number of data fields which may be returned by a query. The only limits are those imposed by the amount of memory available, or by Oracle.


WARNINGS

The Oraperl emulation software shares no code with the original oraperl. It is built on top of the new Perl5 DBI and DBD::Oracle modules. These modules are still evolving. (One of the goals of the Oraperl emulation software is to allow useful work to be done with the DBI and DBD::Oracle modules whilst insulating users from the ongoing changes in their interfaces.)

It is quite possible, indeed probable, that some differences in behaviour will exist. These are probably confined to error handling.

All differences in behaviour which are not documented here should be reported to Tim.Bunce@ig.co.uk and CC'd to dbi-users@fugue.com.


SEE ALSO

Oracle Documentation
SQL Language Reference Manual. Programmer's Guide to the Oracle Call Interfaces.

Books
Programming Perl by Larry Wall and Randal Schwartz. Learning Perl by Randal Schwartz.

Manual Pages
perl(1)


AUTHORS

Perl by Larry Wall <lwall@netlabs.com>.

ORACLE by Oracle Corporation, California.

Original Oraperl 2.4 code and documentation by Kevin Stock <kstock@auspex.fr>.

DBI and Oraperl emulation using DBD::Oracle by <Tim.Bunce@ig.co.uk>

 Oraperl - Perl access to Oracle databases for old oraperl scripts