Oracle DBE Driver

DOCUMENTATION

• NAME
• SYNOPSIS
• EXAMPLE
• DESCRIPTION
• Connect Parameters
• PL/SQL
• LOB - LARGE OBJECTS
• Reading from BLOBs, CLOBs and NCLOBs
• Reading from LONG and LONG RAW
• Writing to large objects by reading from IO handles
• Writing to large objects by reading from a class
• LIMITATIONS
• KNOWN ERRORS
• AUTHORS
• COPYRIGHT

NAME

SYNOPSIS

  use DBE;
  
  $dsn = "Driver=Oracle;SID=xe;User=hr;Auth=hr";
  $con = DBE->connect( $dsn );

EXAMPLE

  use DBE;
  
  # connect with parameters
  $con = DBE->connect(
      'driver' => 'Oracle',
      'host' => 'host.oracle.tld',
      'service' => 'xe',
      'user' => 'db_user',
      'password' => 'db_pass',
      'charset' => 'al32utf8', # utf-8
      'iso_date' => 1,
      'reconnect' => 999, # 999 reconnect tries
  );
  
  # create a table
  $con->do(
      qq/
      CREATE TABLE CFG_CONFIG (
          CFG_NAME VARCHAR(64) NOT NULL PRIMARY KEY,
          CFG_VALUE VARCHAR(64)
      )
      /
  );
  
  # insert an entry
  $con->do(
      "INSERT INTO CFG_CONFIG (CFG_NAME,CFG_VALUE) VALUES(?,?)",
      'foo', 'bar'
  );
  
  # select the inserted entry
  $res = $con->query(
      "SELECT CFG_VALUE FROM CFG_CONFIG WHERE CFG_NAME = 'foo'"
  );
  
  ($foo) = $res->fetch_row();
  print "\$foo = '$foo'\n";
  
  # output:
  # $foo = 'bar'

DESCRIPTION

Connect Parameters

  use DBE;
  
  DBE->connect( 'Driver=Oracle;User=hr;Auth=hr' );

• PROVIDER | DRIVER [Oracle]
  Driver must be "Oracle".

• HOST [string]
  Name of host to connect to.

• PORT [int]
  Port number. Use together with the HOST parameter.

• SID [string]
  Session identifier.

• SERVICE | SERVER [string]
  Name of the service. Can be set instead of the SID parameter.

• DB | DATABASE [string]
  Name of the database to connect to. Use SID or SERVICE instead.

• USER | UID [string]
  Oracle user name to connect as.

• AUTH | PWD | PASSWORD [string]
  Password to be used.

• CHARSET | CHARACTERSET [string]
  A connection character set. Needs Oracle 9 or higher. Connection character sets can not be changed later again. Default character set is read from the NLS_LANG environment variable.

• ISO_DATE | ISODATE [1|0|Yes|No|True|False]
  When set to TRUE columns with type of DATE and TIMESTAMP will be fetched in ISO format. Default is FALSE.

• AUTOCOMMIT [1|0|Yes|No|True|False]
  Enable or disable auto commit. Default is disabled.

• LOB_MAX_SIZE | LOBMAXSIZE [int]
  The maximum size of data that will be fetched directly from large objects. Larger objects are truncated or returned as IO handle, depending on LOB_TRUNCATE. Default value is 1024 bytes.

• LOB_TRUNCATE | LOBTRUNCATE [1|0|Yes|No|True|False]
  On TRUE value large objects are truncated at LOB_MAX_SIZE. On FALSE value objects larger then LOB_MAX_SIZE are returned as DBE Large Object class. Default is FALSE.
  LONG and LONG RAW fields must be read through a callback function, refer to Reading from LONG and LONG RAW.

• LOB_BUFFER_SIZE | LOBBUFFERSIZE [int]
  Sets the buffer size in bytes when reading from or writing to LOBs. Default value is 4096.

• PREFETCH_MEMORY | PREFETCHMEMORY [int]
  Sets the memory level for top level rows to be prefetched in queries. Rows up to the specified top level row count are fetched if it occupies no more than the specified memory usage limit. The default value is 0, which means that memory size is not included in computing the number of rows to prefetch.

• PREFETCH_ROWS | PREFETCHROWS [int]
  Sets the number of top level rows to be prefetched in queries. The default value is 1 row.

PL/SQL

  # create a table
  $con->do(
      q/CREATE TABLE temp (col1 NUMBER, col2 NUMBER, col3 VARCHAR2(16))/
  );
  
  # execute a PL/SQL block
  $con->do(
      q~
      DECLARE
          x NUMBER := 100;
      BEGIN
          FOR i IN 1..10 LOOP
              IF MOD(i,2) = 0 THEN -- i is even
                  INSERT INTO temp VALUES (i, x, 'i is even');
              ELSE
                  INSERT INTO temp VALUES (i, x, 'i is odd');
              END IF;
              x := x + 100;
          END LOOP;
          COMMIT;
      END;
      ~
  );
  
  # show the result
  $res = $con->query( q/SELECT * FROM temp ORDER BY col1/ );
  print $res->dump;
  
  # drop the table
  $con->do( q/DROP TABLE temp/ );

LOB - LARGE OBJECTS

Reading from BLOBs, CLOBs and NCLOBs

  $res = $con->query( "SELECT lob FROM table" );
  
  while( ($lob) = $res->fetchrow_array )
  {
      if( ref( $lob ) )
      {
          # The size of the LOB exceeds LOB_MAX_SIZE, and LOB_TRUNCATE
          # was set to False. The LOB can be accessed via the DBE::LOB class.
          print "lob is ", $lob->size, " characters/bytes long\n";
          while( $got = $lob->read( $buf, 1024 ) )
          {
              # CLOBs return (length / 4) bytes mininum
              print "lob read $got bytes\n";
          }
          $lob->close; # close the LOB handle
      }
      else
      {
          # The LOB data can be accessed directly.
          print "lob is ", length( $lob ), " characters/bytes long\n";
      }
  }

Reading from LONG and LONG RAW

  # the example defines a callback function that writes the content of
  # each long field into a file with filename <field>.content
  
  # define a hash to hold the file handles
  our %LongFileHandles;
  
  # the callback function has the following parameters
  #
  # $id      : contains a userdefined identifier
  # $field   : contains the name of the column or field
  # $piece   : one of the following:
  #   0 - one piece (callback is be called once)
  #   1 - first piece (callback is called first time)
  #   2 - next piece
  #   3 - last piece (callback is called last time)
  # $buffer  : contains the data
  
  sub read_long_callback {
      my ($id, $field, $piece, $buffer) = @_;
      my ($fh);
      print "read long $field, piece $piece, size ", length $buffer, "\n";
      if( $piece == 0 )
      {
          # one piece
          open $fh, "> $field.content";
          print $fh $buffer;
          close $fh;
      }
      elsif( $piece == 1 )
      {
          # first piece
          open $fh, "> $field.content";
          print $fh $buffer;
          $LongFileHandles{$field} = $fh;
      }
      else
      {
          # next or last piece
          $fh = $LongFileHandles{$field};
          print $fh $buffer;
          if( $piece == 3 )
          {
              # last piece
              close $fh;
              delete $LongFileHandles{$field};
          }
      }
  }
  
  # on the second parameter you can define an identifier or an object
  $con->set_long_callback( \&read_long_callback, 'myid' );
  # set callback to a class method:
  # $con->set_long_callback( 'read_long_callback', $my_class );
  
  $res = $con->query( "SELECT LONG_FIELD FROM TABLE" );
  
  while( $res->fetch_row( \%row ) )
  {
      if( $row{'LONG_FIELD'} )
      {
          # $row{'LONG_FIELD'} contains the identifier that was set
          # to set_long_callback()
          # the content was written to file "LONG_FIELD.content"
      }
      else
      {
          # field is NULL, callback was not called
      }
  }

Writing to large objects by reading from IO handles

  $stmt = $con->prepare( "INSERT INTO table (lob) VALUES(?)" );
  # open a file
  open $fh, '< lob.file';
  # bind a Perl IO handle to the lob parameter
  # use type 'b' for blobs and type 's' for clobs
  $stmt->bind_param( 1, $fh, 'b' );
  # execute the statement and write the contents of 'lob.file' to Oracle
  $stmt->execute();
  # close the file
  close $fh;
  # commit the statement
  $con->commit();

Writing to large objects by reading from a class

  package MyInput;
  
  sub new {
      my $pkg = shift;
      my $this = {
          'counter' => 0,
          'max' => shift || 1,
      };
      bless $this, $pkg;
  }
  
  # the class must implement a READ method like it a tied HANDLE does
  
  sub READ {
      my ($this, undef, $length) = @_;
      # read up to 'max' times
      $this->{'counter'} ++ == $this->{'max'} and return -1; # EOF
      # we need to write directly into the second parameter ($_[1])
      $_[1] = 'TEST';
      # was LOB_BUFFER_SIZE set to less then 4 bytes?
      $length < 4 and $_[1] = substr( $_[1], 0, $length );
      # return the length of bytes read
      return length( $_[1] );
  }
  
  package main;
  
  # make the class
  $in = MyInput->new( 3 );
  # bind the class
  $stmt->bind_param( ':lob', $in, 'b' );
  # execute the statement and read from our class
  $stmt->execute();
  # destroy the class
  undef $in;
  # commit the statement
  $con->commit();

LIMITATIONS

KNOWN ERRORS

  $stmt->bind_param(':blob', 'TEST', 's');          # error
  $stmt->bind_param(':blob', 'TEST', 'b');          # ok
  $stmt->bind_param(':blob', '54455354', 's');      # ok (hex values)

AUTHORS

COPYRIGHT