DBIx::SQLEngine::Driver::Oracle - Support DBD::Oracle and DBD::ODBC/Oracle

DBIx-SQLEngine documentation Contained in the DBIx-SQLEngine distribution.

Index

Code Index:

NAME

Top

DBIx::SQLEngine::Driver::Oracle - Support DBD::Oracle and DBD::ODBC/Oracle

SYNOPSIS

Top

DBI Wrapper: Adds methods to a DBI database handle.

  my $sqldb = DBIx::SQLEngine->new( 'dbi:oracle:test' );

Portability Subclasses: Uses driver's idioms or emulation.

  $hash_ary = $sqldb->fetch_select( 
    table => 'students' 
    limit => 5, offset => 10
  );

DESCRIPTION

Top

This package provides a subclass of DBIx::SQLEngine which compensates for Oracle's idiosyncrasies.

About Driver Subclasses

You do not need to use this package directly; when you connect to a database, the SQLEngine object is automatically re-blessed in to the appropriate subclass.

FETCHING DATA (SQL DQL)

Top

Methods Used By Complex Queries

sql_limit()

Adds support for SQL select limit clause.

Implemented as a subselect with ROWNUM.

EDITING DATA (SQL DML)

Top

Insert to Add Data

do_insert_with_sequence()
  $sqldb->do_insert_with_sequence( $sequence_name, %sql_clauses ) : $row_count

Implemented using _seq_do_insert_preinc and seq_increment.

seq_increment

  $sqldb->seq_increment( $table, $field ) : $new_value

Increments the sequence, and returns the newly allocated value.

DEFINING STRUCTURES (SQL DDL)

Top

Detect Tables and Columns

sql_detect_table()
  $sqldb->sql_detect_table ( $tablename )  : %sql_select_clauses

Implemented using Oracle's "select * from $tablename limit 1".

Column Type Methods

The following methods are used by sql_create_table to specify column information in a DBMS-specific fashion.

dbms_create_column_types()
  $sqldb->dbms_create_column_types () : %column_type_codes

Implemented using Oracle's blob and number types.

Portability: Note that this capability is currently limited, and additional steps need to be taken to manually define sequences in Oracle.

dbms_create_column_text_long_type()
  $sqldb->dbms_create_column_text_long_type () : $col_type_str

Implemented using Oracle's clob type.

ADVANCED CAPABILITIES

Top

Call, Create and Drop Stored Procedures

Note: this feature has been added recently, and not yet tested in real-world conditions.

fetch_storedproc()
  $sqldb->fetch_storedproc( $proc_name, @arguments ) : $rows

Not yet supported.

See "Binding Cursors" in DBD::Oracle for more information.

do_storedproc()
  $sqldb->do_storedproc( $proc_name, @arguments ) : $row_count

Calls do_sql with "execute procedure", the procedure name, and the arguments using placeholders.

create_storedproc()
  $sqldb->create_storedproc( $proc_name, $definition )

Calls do_sql with "create or replace procedure", the procedure name, and the definition.

drop_storedproc()
  $sqldb->drop_storedproc( $proc_name )

Calls do_sql with "drop procedure" and the procedure name.

INTERNAL CONNECTION METHODS (DBI DBH)

Top

Checking For Connection

sql_detect_any()
  $sqldb->sql_detect_any : %sql_select_clauses

Implemented using Oracle's "select 1 from dual".

Statement Error Handling

recoverable_query_exceptions()
  $sqldb->recoverable_query_exceptions() : @common_error_messages

Provides a list of error messages which represent common communication failures or other incidental errors.

SEE ALSO

Top

See DBIx::SQLEngine for the overall interface and developer documentation.

See DBIx::SQLEngine::Docs::ReadMe for general information about this distribution, including installation and license information.

DBIx-SQLEngine documentation Contained in the DBIx-SQLEngine distribution. 
package DBIx::SQLEngine::Driver::Oracle;

use strict;
use Carp;

########################################################################

########################################################################


sub sql_limit {
  my $self = shift;
  my ( $limit, $offset, $sql, @params ) = @_;

  # remove tablealiases and group-functions from outer query properties
  my ($properties) = ($sql =~ /^\s*SELECT\s(.*?)\sFROM\s/i);
  $properties =~ s/[^\s]+\s+as\s+//ig;
  $properties =~ s/DISTINCT//ig;
  $properties =~ s/\w+\.//g;
  
  $offset ||= 0;
  my $position = ( $offset + $limit );
  
  $sql = "SELECT $properties FROM ( SELECT $properties, ROWNUM AS sqle_position FROM ( $sql ) WHERE ROWNUM <= $position ) WHERE sqle_position > $offset";

  return ($sql, @params);
}

########################################################################

########################################################################


# $rows = $self->do_insert_with_sequence( $sequence, %clauses );
sub do_insert_with_sequence { 
  (shift)->_seq_do_insert_preinc( @_ )
}

# $current_id = $sqldb->seq_increment( $table, $field );
sub seq_increment {
  my ($self, $table, $field) = @_;
  $self->fetch_one_value(
    sql => "SELECT $field.NEXTVAL FROM DUAL')"
  );
}

########################################################################

########################################################################


sub sql_detect_table {
  my ($self, $tablename) = @_;
  return (
    table => $tablename,
    criteria => '1 = 0',
    limit => 1,
  )
}

########################################################################


sub dbms_create_column_types {
  # sequences have to be defined extra manually with Oracle :-|
  'sequential' => 'number not null', 
  'binary' => 'blob',
}

sub dbms_create_column_text_long_type {
  'clob'
}

########################################################################

########################################################################


sub fetch_storedproc  { 
  confess("Oracle fetch_storedproc: Not yet implemented")
}
sub do_storedproc     { 
  (shift)->do_sql(join("\n",
		      "begin" . 
		      (shift) . "(" . join(', ', ('?') x scalar(@_) ) . ")",
		      "end"), @_)
}
sub create_storedproc { 
  (shift)->do_sql(join("\n", 
		    "create or replace procedure $_[0] is begin", @_, "end" ) ) 
}
sub drop_storedproc   { 
  (shift)->do_sql( "drop procedure $_[0]" ) 
}

########################################################################

########################################################################


sub sql_detect_any {
  return ( sql => 'select 1 from dual' )
}

########################################################################


sub recoverable_query_exceptions {
  'ORA-03111',	# ORA-03111 break received on communication channel
  'ORA-03113',	# ORA-03113 end-of-file on communication channel
  'ORA-03114',	# ORA-03114 not connected to ORACLE
}

########################################################################

########################################################################


########################################################################

1;