• Introduction

• Frequently Asked Questions

• Conference Slides

• Recommended Books

• Mailing Lists

[ The Perl Journal ]

[ Perl ]

DBD::Oracle

General Information

Driver Version

DBD::Oracle version 1.03.

Feature Summary

  Transactions                           Yes
  Locking                                Yes, implicit and explicit
  Table joins                            Yes, inner and outer
  LONG/LOB data types                    Yes, upto 4GB
  Statement handle attributes available  After prepare()
  Placeholders                           Yes, "?" and ":1" styles (native)
  Stored procedures                      Yes
  Bind output values                     Yes
  Table name letter case                 Insensitive, stored as uppercase
  Field name letter case                 Insensitive, stored as uppercase
  Quoting of otherwise invalid names     Yes, via double quotes
  Case insensitive "LIKE" operator       No
  Server table ROW ID pseudocolumn       Yes, "ROWID"
  Positioned update/delete               No
  Concurrent use of multiple handles     Unrestricted

Author and Contact Details

The driver author is Tim Bunce. He can be contacted via the dbi-users mailing list.

Supported Database Versions and Options

The DBD::Oracle module supports both Oracle 7 and Oracle 8.

Building for Oracle 8 defaults to use the new Oracle 8 OCI interface, which enables use of some Oracle 8 features including LOBs and ``INSERT ... RETURNING ...''.

An emulation module for the old Perl4 oraperl software is supplied with DBD::Oracle, making it very easy to upgrade oraperl scripts to Perl5.

For further information about Oracle, refer to:

  http://www.oracle.com
  http://technet.oracle.com

Differences from the DBI Specification

DBD::Oracle has no known significant differences in behavior from the current DBI specification.

Connect Syntax

The DBI->connect() Data Source Name, or DSN, can be one of the following:

  dbi:Oracle:tnsname
  dbi:Oracle:sidname
  dbi:Oracle:host=hostname;sid=sid

Some other less common formats also work if supported by the Oracle client version being used.

There are no significant driver specific attributes for the DBI->connect() method.

DBD::Oracle supports an unlimited number of concurrent database connections to one or more databases.

Data Types

Numeric Data Handling

Oracle only has one flexible underlying numeric type, NUMBER. But Oracle does support several ANSI standard and IBM data type names as aliases, including:

  INTEGER      = NUMBER(38)
  INT          = NUMBER(38)
  SMALLINT     = NUMBER(38)
  DECIMAL(p,s) = NUMBER(p,s)
  NUMERIC(p,s) = NUMBER(p,s)
  FLOAT        = NUMBER
  FLOAT(b)     = NUMBER(p)   where b is the binary precision, 1 to 126
  REAL         = NUMBER(18)

The NUMBER datatype stores positive and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes), with 38 digits of precision.

You can specify a fixed-point number using the following form: NUMBER(p,s) where s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

You can specify an integer using NUMBER(p). This is a fixed-point number with precision p and scale 0. This is equivalent to NUMBER(p,0).

You can specify a floating-point number using NUMBER. This is a floating-point number with decimal precision 38. A scale value is not applicable for floating-point numbers.

DBD::Oracle always returns all numbers as strings. Thus the driver puts no restriction on size of PRECISION or SCALE.

String Data Handling

Oracle supports the following string data types:

  VARCHAR2(size)
  NVARCHAR2(size)
  CHAR
  CHAR(size)
  NCHAR
  NCHAR(size)
  RAW(size)

The RAW type is presented as hexadecimal characters. The contents are treated as non-character binary data and thus are never ``translated'' by character set conversions or gateway interfaces.

CHAR types and the RAW type have a limit of 2000 bytes. For VARCHAR types the limit is 2000 bytes in Oracle 7 and 4000 in Oracle 8.

The NVARCHAR2 and NCHAR variants hold string values of a defined national character set (Oracle 8 only). For those types the maximum number of characters stored may be lower when using multibyte character sets.

The CHAR and NCHAR types are fixed length and blank padded.

Oracle automatically converts character data between the character set of the database defined when the database was created and the character set of the client, defined by the NLS_LANG parameter for the CHAR and VARCHAR2 types or the NLS_NCHAR parameter for the NCHAR and NVARCHAR2 types.

CONVERT(string, dest_char_set, source_char_set) can be used to convert strings between character sets. Oracle 8 supports 180 storage character sets. UTF-8 is supported. See the National Language Support section of the Oracle Reference manual for more details on character set issues.

Strings can be concatenated using either the CONCAT(s1,s2,...) SQL function or the || operator.

Date Data Handling

Oracle supports one flexible date/time data type: DATE. A DATE can have any value from January 1, 4712 BC to December 31, 4712 AD with a one second resolution.

Oracle supports a very wide range of date formats and can use one of several calendars (Arabic Hijrah, English Hijrah, Gregorian, Japanese Imperial, Persian, ROC Official (Republic of China) and Thai Buddha). We'll only consider the Gregorian calendar here.

The default output format for the DATE type is defined by the NLS_DATE_FORMAT configuration parameter, but it's typically DD-MON-YY, e.g., 20-FEB-99 in most western installations. The default input format for the DATE type is the same as the output format. Only that one format is recognised.

If you specify a DATE value without a time component, the default time is 00:00:00 (midnight). If you specify a DATE value without a date, the default date is the first day of the current month. If a date format that has a two digit year, such as the YY in DD-MON-YY (a common default) then the date returned is always in the current century. The RR format can be used instead to provide a 50 year pivot.

The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY. For information on these parameters, see Oracle8 Referencee.

You can change the default date format for your session with the ``ALTER SESSION'' command. For example:

  ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY'

The TO_DATE() function can be used to parse a character string containg a date in a known format. For example:

  UPDATE table SET date_field = TO_DATE('1999-02-21', 'YYYY-MM-DD')

The TO_CHAR() function can be used to format a date. For example:

  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL

The current datetime is returned by the SYSDATE() function.

You can add numbers to DATE values. The number is interpreted as numbers of days; for example, SYSDATE + 1 is this time tomorrow, and SYSDATE - (3/1440) is three minutes ago. You can subtract two dates to find the difference, in days, between them.

Oracle provides a wide range of date functions including ROUND(), TRUNC(), NEXT_DAY(), ADD_MONTHS(), LAST_DAY() (of the month), and MONTHS_BETWEEN().

The following SQL expression can be used to convert an integer ``seconds since 1-jan-1970'' value to the corresponding database date time:

  to_date(trunc(:unixtime/86400, 0) + 2440588, 'J') -- date part
  +(mod(:unixtime,86400)/86400)                     -- time part

To do the reverse you can use:

  (date_time_field - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400

Oracle does no automatic time zone adjustments. However it does provide a NEW_TIME() function that calculates time zone adjustments for a range of time zones. NEW_TIME(d, z1, z2) returns the date and time in time zone z2 when the date and time in time zone z1 are represented by d.

LONG/BLOB Data Handling

Oracle supports these LONG/BLOB data types:

  LONG      - Character data of variable length
  LONG RAW  - Raw binary data of variable length
  CLOB      - A large object containing single-byte characters
  NCLOB     - A large object containing national character set data
  BLOB      - Binary large object
  BFILE     - Locator for external large binary file

The LONG types can hold upto 2 gigabytes. The other types (LOB and FILE) can hold upto 4 gigabytes. The LOB and FILE types are only available when using Oracle 8 OCI.

The LONG RAW, and RAW, types are passed to and from the database as strings consisting of pairs of hex digits.

The LongReadLen and LongTruncOk attributes work as defined. However, the LongReadLen attribute seems to be limited to 65535 bytes on most platforms when using Oracle 7. Building DBD::Oracle with Oracle 8 OCI raises that limit to 4 gigabytes.

The maximum length of bind_param() parameter value that can be used to insert LONG data seems to be limited to 65535 bytes on most platforms when using Oracle 7. Building DBD::Oracle with Oracle 8 OCI raises that limit to 4 gigabytes.

The TYPE attribute value SQL_LONGVARCHAR indicates an Oracle LONG type. The value SQL_LONGVARBINARY indicates an Oracle LONG RAW type. These values are not always required but their use is strongly recommended.

No other special handling is required for LONG/BLOB data types. They can be treated just like any other field when fetching or inserting etc.

Other Data Handling issues

The DBD::Oracle driver supports the type_info() method.

Oracle supports automatic conversions between data types wherever it's reasonable.

Transactions, Isolation and Locking

DBD::Oracle supports transactions. The default transaction isolation level is 'Read Commited'.

Oracle supports READ COMMITED and SERIALIZABLE isolation levels. The level be changed once per-transaction by executing a SET TRANSACTION ISOLATION LEVEL x statement (where x is the name of the isolation level required).

Oracle also supports transaction-level read consistency. This can be enabled by issuing a SET TRANSACTION statement with the READ ONLY option.

In Oracle, the default behavior is that a lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers.

Rows returned by a SELECT statement can be locked to prevent them from being changed by another transaction by appending FOR UPDATE to the select statement. Optionally, you can specify a column list in parentheses after the FOR UPDATE clause.

The LOCK TABLE table_name IN lock_mode statement can be used to apply an explicit lock on an entire table. A range of row and table locks are supported.

SQL Dialect

Case Sensitivity of LIKE Operator

The LIKE operator is case sensitive.

Table Join Syntax

Oracle supports inner joins with the usual syntax:

  SELECT * FROM a, b WHERE a.field = b.field

To write a query that performs an outer join of tables A and B and returns all rows from A, the Oracle outer join operator (+) must be applied to all column names of B that appear in the join condition. For example:

  SELECT customer_name, order_date 
  FROM customers, orders 
  WHERE customers.cust_id = orders.cust_id (+);

For all rows in the customers table that have no matching rows in the orders table, Oracle returns NULL for any select list expressions containing columns from the orders table.

Table and Column Names

The names of Oracle identifiers, such as tables and columns, cannot exceed 30 characters in length.

The first character must be a letter, but the rest can be any combination of letters, numerals, dollar signs ($), pound signs (#), and underscores (_).

However, if an Oracle identifier is enclosed by double quotation marks ("), it can contain any combination of legal characters including spaces but excluding quotation marks.

Oracle converts all identifiers to upper-case unless enclosed in double quotation marks. National characters can also be used when identifiers are quoted.

Row ID

The Oracle ``row id'' pseudocolumn is called ROWID. Oracle ROWIDs are alphanumeric case sensitive strings. They can be treated as ordinary strings and used to rapidly (re)select rows.

Automatic Key or Sequence Generation

Oracle supports ``sequence generators''. Any number of named sequence generators can be created in a database using the CREATE SEQUENCE seq_name SQL command. Each has pseudocolumns called NEXTVAL and CURRVAL. The typical usage is:

  INSERT INTO table (k, v) VALUES (seq_name.nextval, ?)

To get the value just inserted you can use

  SELECT seq_name.currval FROM DUAL

Oracle does not support automatic key generation such as ``auto increment'' or ``system generated'' keys. However they can be emulated using triggers and sequence generators. For example:

  CREATE TRIGGER trigger_name
    BEFORE INSERT ON table_name FOR EACH ROW
    DECLARE newid integer;
  BEGIN
    IF (:NEW.key_field_name IS NULL)
    THEN
      SELECT sequence_name.NextVal INTO newid FROM DUAL;
      :NEW.key_field_name := newid;
    END IF;
  END;

Oracle8i (8.1.0 and above) supports Universal Unique ID number generation, per the IETF Internet-Draft, using the new SYS_GUID() function. GUIDs are more useful than sequence generators in a distributed database since no two hosts will generate the same GUID.

Automatic Row Numbering and Row Count Limiting

The ROWNUM pseudocolumn can be used to sequentially number selected rows (starting at 1). Sadly, however, Oracle's ROWNUM has some frustrating limitations. Refer to the Oracle SQL documentation.

Positioned updates and deletes

Oracle does not support positioned updates or deletes.

Parameter Binding

Parameter binding is directly suported by Oracle. Both the ? and :1 style of placeholders are supported. The :name style is also supported, but is not portable.

The bind_param() method TYPE attribute can be used to indicate the type a parameter should be bound as. These SQL types are bound as VARCHAR2: SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT, SQL_REAL, SQL_DOUBLE, and SQL_VARCHAR. Oracle will automatically convert from VARCHAR2 to the required type.*

    * Working with strings and letting Oracle handle the conversions
      actually has many benefits. Oracle's packed decimal numeric
      format is compact, fast and has far larger scale and precision
      than Perl's own numeric values.

The SQL_CHAR type is bound as a CHAR thus enabling fixed-width blank padded comparison semantics.

The SQL_BINARY and SQL_VARBINARY types are bound as RAW. SQL_LONGVARBINARY is bound as LONG RAW and SQL_LONGVARCHAR as LONG.

Unsupported values of the TYPE attribute generate a warning.

Refer to the DBD::Oracle documentation for details of how to bind LOBs and CURSORs.

Stored Procedures

Oracle stored procedures are implemented in the Oracle PL/SQL language*.

    * A procedural extension to SQL that supports variables, control flow,
      packages, exceptions, etc. With Oracle8i stored procedures can also
      be implemented in Java.

The DBD::Oracle module can be used to execute a block of PL/SQL code by starting it with a BEGIN and ending it with an END;. PL/SQL blocks are used to call stored procedures. Here's a simple example that calls a stored procedure called ``foo'' and passes it two parameters:

  $sth = $dbh->prepare("BEGIN foo(:1, :2) END;");
  $sth->execute("Baz", 24);

Here's a more complex example that shows a stored procedure being called with two parameters and returning the return value of the procedure. The second parameter of the procedure is defined as IN OUT so we bind that using bind_param_inout() to enable it to update the Perl variable:

  $sth = $dbh->prepare("BEGIN :result = func_name(:id, :changeme) END;");
  $sth->bind_param(":id", "FooBar");
  my ($result, $changeme) = (41, 42);
  $sth->bind_param_inout(":result",  \$result,  100);
  $sth->bind_param_inout(":changeme", \$changeme, 100);
  $sth->execute();
  print "func_name returned '$result' and updated changeme to '$changeme'\n";

Table Metadata

DBD::Oracle supports the table_info() method.

The ALL_TABLES view contains detailed information about all tables in the database, one row per table.

The ALL_TAB_COLUMNS view contains detailed information about all columns of all the tables in the database, one row per table.

The ALL_INDEXES view contains detailed information about all indexes in the database, including primary keys, one row per index.

The ALL_IND_COLUMNS view contains information about the columns that make up each index.

(Note that for all these views, fields containing statistics derived from the actual data in the corresponding table are updated only when the SQL ANALYSE command is executed for that table.)

Driver-specific Attributes and Methods

DBD::Oracle has no significant driver-specific database or statement handle attributes.

The following private methods are supported:

plsql_errstr

  $plsql_errstr = $dbh->func('plsql_errstr');

Returns error text from the USER_ERRORS table.

dbms_output_enable

  $dbh->func('dbms_output_enable');

Enables the DBMS_OUTPUT package. The DBMS_OUTPUT package is typically used to receive trace and informational messages from stored procedures.

dbms_output_get

  $msg  = $dbh->func('dbms_output_get');
  @msgs = $dbh->func('dbms_output_get');

Gets a single line or all available lines using DBMS_OUTPUT.GET_LINE.

dbms_output_put

  $msg  = $dbh->func('dbms_output_put', @msgs);

Puts messages using DBMS_OUTPUT.PUT_LINE.