| Subject: | _dbh_last_insert_id |
Pre-conditions:
*Oracle 11g database
*Insert row into a table with auto-increment id
*2 or more database schemas (e.g. sym10 and sym30)
*Authentication with sym30 user -> default schema is sym30
Attention: Bug is not present in version 0.08115 of DBIx::Class and the
module DBIx::Class::Storage::DBI::Oracle::Generic didn't change between
the two versions.
Code snippet that fails:
$dbh->resultset('Cfgproperty')->create( $data_entry );
Module DBIx::Class::Storage::DBI::Oracle::Generic determines the maximum
sequence number of the auto-increment column (_dbh_last_insert_id).
The auto-increment trigger is defined as follows:
create or replace trigger ai_cfgproperty
BEFORE INSERT
ON cfgproperty
for each row
DECLARE NUMROWS INTEGER;
BEGIN
select cfgid.nextval
into :NEW.cfgid
FROM dual;
END;
_dbh_get_autoinc_seq fetches two records. One for each schema.
The column owner will not be considered if the schema name is not given
at the insert statement.
Hence, the database command
insert into cfgproperty ... (sym30, because default schema of the user)
instead of
insert into sym30.cfgproperty
is created.
Because of the declaration of the trigger, the schema of the table is
still unknown.
The following statement then fails:
Eval hat eine Exception abgefangen: 'DBIx::Class::ResultSet::create():
DBI Exception: DBD::Oracle::db selectrow_array failed: ORA-00942: table
or view does not exist (DBD ERROR: error possibly near <*> indicator at
char 13 in 'SELECT SYM10.<*>cfgid.currval FROM DUAL') [for Statement
"SELECT SYM10.cfgid.currval FROM DUAL"] at ....
The statement is addressing the cfgid column of the schema sym10, but
the insert should be done on the table within sym30.