Oraperl - Perl access to Oracle databases for old oraperl scripts


Oraperl - Perl access to Oracle databases for old oraperl scripts


  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_do($lda, $stmt)
  &ora_autocommit($lda, $on_off)


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

The original oraperl was a Perl 4 binary with Oracle OCI compiled into it. The Perl 5 Oraperl module described here is distributed with the DBD::Oracle manpage (a database driver what operates within the DBI manpage) and adds an extra layer over the DBI manpage method calls. The Oraperl module should only be used to allow existing Perl 4 oraperl scripts to run with minimal changes; any new development should use the DBI manpage directly.

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.


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.


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


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


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().


  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";


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.


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 to dbi-users@perl.org.


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

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

Manual Pages


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

DBI and Oraperl emulation using DBD::Oracle by Tim Bunce.


As of DBD::Oracle release 1.17 in February 2006 The Pythian Group, Inc.
(L<http://www.pythian.com>;) are taking the lead in maintaining DBD::Oracle with
my assistance and gratitude.


Copyright (c) 1994-2006 Tim Bunce. Ireland.

The DBD::Oracle module is free open source software; you can redistribute it and/or modify it under the same terms as Perl 5.

 Oraperl - Perl access to Oracle databases for old oraperl scripts