1 package DBIx::Class::Storage::DBI::Oracle::Generic;
8 DBIx::Class::Storage::DBI::Oracle::Generic - Oracle Support for DBIx::Class
12 # In your result (table) classes
13 use base 'DBIx::Class::Core';
14 __PACKAGE__->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } });
15 __PACKAGE__->set_primary_key('id');
16 __PACKAGE__->sequence('mysequence');
20 This class implements base Oracle support. The subclass
21 L<DBIx::Class::Storage::DBI::Oracle::WhereJoins> is for C<(+)> joins in Oracle
28 use base qw/DBIx::Class::Storage::DBI/;
31 sub deployment_statements {
33 my ($schema, $type, $version, $dir, $sqltargs, @rest) = @_;
36 my $quote_char = $self->schema->storage->{'_sql_maker_opts'}->{'quote_char'};
37 $sqltargs->{quote_table_names} = 0 unless $quote_char;
38 $sqltargs->{quote_field_names} = 0 unless $quote_char;
40 my $oracle_version = eval { $self->_get_dbh->get_info(18) };
42 $sqltargs->{producer_args}{oracle_version} = $oracle_version;
44 $self->next::method($schema, $type, $version, $dir, $sqltargs, @rest);
47 sub _dbh_last_insert_id {
48 my ($self, $dbh, $source, @columns) = @_;
50 foreach my $col (@columns) {
51 my $seq = ($source->column_info($col)->{sequence} ||= $self->get_autoinc_seq($source,$col));
52 my $id = $self->_sequence_fetch( 'currval', $seq );
58 sub _dbh_get_autoinc_seq {
59 my ($self, $dbh, $source, $col) = @_;
61 # check if quoting is on
62 my $quote_char = $self->schema->storage->{'_sql_maker_opts'}->{'quote_char'};
64 # look up the correct sequence automatically
66 SELECT trigger_body FROM ALL_TRIGGERS t
67 WHERE t.table_name = ?
68 AND t.triggering_event = 'INSERT'
69 AND t.status = 'ENABLED'
72 # trigger_body is a LONG
73 local $dbh->{LongReadLen} = 64 * 1024 if ($dbh->{LongReadLen} < 64 * 1024);
78 if ( ref $source->name ne 'SCALAR' ) {
79 $source_name = $source->name;
82 $source_name = ${$source->name};
84 $source_name = uc($source_name) unless $quote_char;
86 # check for fully-qualified name (eg. SCHEMA.TABLENAME)
87 if ( my ( $schema, $table ) = $source_name =~ /(\w+)\.(\w+)/ ) {
89 SELECT trigger_body FROM ALL_TRIGGERS t
90 WHERE t.owner = ? AND t.table_name = ?
91 AND t.triggering_event = 'INSERT'
92 AND t.status = 'ENABLED'
94 $sth = $dbh->prepare($sql);
95 my $table_name = $self -> sql_maker -> _quote($table);
96 #my $schema_name = $self -> sql_maker -> _quote($schema);
97 my $schema_name = uc($schema);
99 $sth->execute( $schema_name, $table_name );
102 $sth = $dbh->prepare($sql);
103 $sth->execute( $source_name );
105 while (my ($insert_trigger) = $sth->fetchrow_array) {
106 return $1 if $insert_trigger =~ m!("?\w+"?)\.nextval!i; # col name goes here???
108 $self->throw_exception("Unable to find a sequence INSERT trigger on table '" . $source->name . "'.");
111 sub _sequence_fetch {
112 my ( $self, $type, $seq ) = @_;
113 my ($id) = $self->_get_dbh->selectrow_array("SELECT ${seq}.${type} FROM DUAL");
120 my $dbh = $self->_dbh or return 0;
122 local $dbh->{RaiseError} = 1;
125 $dbh->do("select 1 from dual");
133 my ($dbh, $op, $extra_bind, $ident, $bind_attributes, @args) = @_;
135 my $wantarray = wantarray;
137 my (@res, $exception, $retried);
143 @res = $self->next::method(@_);
145 $res[0] = $self->next::method(@_);
149 if ($exception =~ /ORA-01003/) {
150 # ORA-01003: no statement parsed (someone changed the table somehow,
151 # invalidating your cursor.)
152 my ($sql, $bind) = $self->_prep_for_execute($op, $extra_bind, $ident, \@args);
153 delete $dbh->{CachedKids}{$sql};
157 } while (not $retried++);
160 $self->throw_exception($exception) if $exception;
162 wantarray ? @res : $res[0]
165 =head2 get_autoinc_seq
167 Returns the sequence name for an autoincrement column
171 sub get_autoinc_seq {
172 my ($self, $source, $col) = @_;
174 $self->dbh_do('_dbh_get_autoinc_seq', $source, $col);
177 =head2 columns_info_for
179 This wraps the superclass version of this method to force table
184 sub columns_info_for {
185 my ($self, $table) = @_;
187 $self->next::method($table);
190 =head2 datetime_parser_type
192 This sets the proper DateTime::Format module for use with
193 L<DBIx::Class::InflateColumn::DateTime>.
197 sub datetime_parser_type { return "DateTime::Format::Oracle"; }
199 =head2 connect_call_datetime_setup
203 on_connect_call => 'datetime_setup'
205 In L<DBIx::Class::Storage::DBI/connect_info> to set the session nls date, and
206 timestamp values for use with L<DBIx::Class::InflateColumn::DateTime> and the
207 necessary environment variables for L<DateTime::Format::Oracle>, which is used
210 Maximum allowable precision is used, unless the environment variables have
213 These are the defaults used:
215 $ENV{NLS_DATE_FORMAT} ||= 'YYYY-MM-DD HH24:MI:SS';
216 $ENV{NLS_TIMESTAMP_FORMAT} ||= 'YYYY-MM-DD HH24:MI:SS.FF';
217 $ENV{NLS_TIMESTAMP_TZ_FORMAT} ||= 'YYYY-MM-DD HH24:MI:SS.FF TZHTZM';
219 To get more than second precision with L<DBIx::Class::InflateColumn::DateTime>
220 for your timestamps, use something like this:
222 use Time::HiRes 'time';
223 my $ts = DateTime->from_epoch(epoch => time);
227 sub connect_call_datetime_setup {
230 my $date_format = $ENV{NLS_DATE_FORMAT} ||= 'YYYY-MM-DD HH24:MI:SS';
231 my $timestamp_format = $ENV{NLS_TIMESTAMP_FORMAT} ||=
232 'YYYY-MM-DD HH24:MI:SS.FF';
233 my $timestamp_tz_format = $ENV{NLS_TIMESTAMP_TZ_FORMAT} ||=
234 'YYYY-MM-DD HH24:MI:SS.FF TZHTZM';
237 "alter session set nls_date_format = '$date_format'"
240 "alter session set nls_timestamp_format = '$timestamp_format'"
243 "alter session set nls_timestamp_tz_format='$timestamp_tz_format'"
247 =head2 source_bind_attributes
249 Handle LOB types in Oracle. Under a certain size (4k?), you can get away
250 with the driver assuming your input is the deprecated LONG type if you
251 encode it as a hex string. That ain't gonna fly at larger values, where
252 you'll discover you have to do what this does.
254 This method had to be overridden because we need to set ora_field to the
255 actual column, and that isn't passed to the call (provided by Storage) to
256 bind_attribute_by_data_type.
258 According to L<DBD::Oracle>, the ora_field isn't always necessary, but
259 adding it doesn't hurt, and will save your bacon if you're modifying a
260 table with more than one LOB column.
264 sub source_bind_attributes
272 foreach my $column ($source->columns) {
273 my $data_type = $source->column_info($column)->{data_type} || '';
274 next unless $data_type;
276 my %column_bind_attrs = $self->bind_attribute_by_data_type($data_type);
278 if ($data_type =~ /^[BC]LOB$/i) {
279 if ($DBD::Oracle::VERSION eq '1.23') {
280 $self->throw_exception(
281 "BLOB/CLOB support in DBD::Oracle == 1.23 is broken, use an earlier or later ".
282 "version.\n\nSee: https://rt.cpan.org/Public/Bug/Display.html?id=46016\n"
286 $column_bind_attrs{'ora_type'} = uc($data_type) eq 'CLOB'
287 ? DBD::Oracle::ORA_CLOB()
288 : DBD::Oracle::ORA_BLOB()
290 $column_bind_attrs{'ora_field'} = $column;
293 $bind_attributes{$column} = \%column_bind_attrs;
296 return \%bind_attributes;
300 my ($self, $name) = @_;
301 $self->_get_dbh->do("SAVEPOINT $name");
304 # Oracle automatically releases a savepoint when you start another one with the
306 sub _svp_release { 1 }
309 my ($self, $name) = @_;
310 $self->_get_dbh->do("ROLLBACK TO SAVEPOINT $name")
313 =head2 relname_to_table_alias
315 L<DBIx::Class> uses L<DBIx::Class::Relationship> names as table aliases in
318 Unfortunately, Oracle doesn't support identifiers over 30 chars in length, so
319 the L<DBIx::Class::Relationship> name is shortened and appended with half of an
322 See L<DBIx::Class::Storage/"relname_to_table_alias">.
326 sub relname_to_table_alias {
328 my ($relname, $join_count) = @_;
330 my $alias = $self->next::method(@_);
332 return $alias if length($alias) <= 30;
334 # get a base64 md5 of the alias with join_count
336 my $ctx = Digest::MD5->new;
338 my $md5 = $ctx->b64digest;
340 # remove alignment mark just in case
343 # truncate and prepend to truncated relname without vowels
344 (my $devoweled = $relname) =~ s/[aeiou]//g;
345 my $shortened = substr($devoweled, 0, 18);
348 $shortened . '_' . substr($md5, 0, 30 - length($shortened) - 1);
355 See L<DBIx::Class/CONTRIBUTORS>.
359 You may distribute this code under the same terms as Perl itself.