[Dbix-class] trouble with oracle sequence

Rom Bre rbreslav2 at gmail.com
Thu May 24 19:50:47 GMT 2007


I'm getting the following error after attempting to insert into a table
where i have setup an oracle sequence:

DBIx::Class::ResultSet::create(): Can't get last insert id


This is happening because dbic is attempting to do a SELECT
STORE_CART_SEQ.CURRVAL FROM DUAL, without a preceding NEXTVAL.


Here's the create statement:

$c->model('StoreDB::Cart')->create({ session_id =3D> $session_id });


I'm under the assumption that the NEXTVAL will be handled
automatically and that I don't need to specify anything in the create



Here's the table model:

package StoreDB::Cart;

use strict;
use base qw/DBIx::Class/;
use DateTime ();

__PACKAGE__->load_components(qw/PK::Auto Core InflateColumn::DateTime/);

__PACKAGE__->table('store_cart');
__PACKAGE__->add_columns(qw/cart_id session_id created updated/);

__PACKAGE__->set_primary_key('cart_id');
__PACKAGE__->sequence('store_cart_seq');

__PACKAGE__->has_many(items =3D> 'StoreDB::Cart::Item', {
'foreign.cart_id' =3D> 'self.cart_id'});


Here's the DBIC_TRACE:


    -> DBI->connect(dbi:Oracle:XXXX, xxxx, ****, HASH(0xa2c02ec))

    -> DBI->install_driver(Oracle) for linux perl=3D5.008007 pid=3D2781
ruid=3D501 euid=3D501
       install_driver: DBD::Oracle version 1.16 loaded from
/usr/local/lib/perl5/site_perl/5.8.7/i686-linux/DBD/Oracle.pm

    <- STORE('ShowErrorStatement' 1)=3D 1 at Oracle.pm <http://oracle.pm/> =
line 61
    <- install_driver=3D DBI::dr=3DHASH(0xa6cf5a0)
ORACLE_HOME environment variable not set
    <- connect('XXXXX' 'xxxxxx' ...)=3D DBI::db=3DHASH(0xa9333cc) at
DBI.pm line 625
    <- STORE('RaiseError' 0)=3D 1 at DBI.pm line 672
    <- STORE('PrintError' 0)=3D 1 at DBI.pm line 672
    <- STORE('AutoCommit' 1)=3D 1 at DBI.pm line 672
    <- STORE('Username' 'xxxx')=3D 1 at
DBI.pm line 675
    <> FETCH('Username')=3D 'xxxx' ('Username' from cache) at DBI.pm line 6=
75
    <- connect=3D DBI::db=3DHASH(0xa9333cc)
    <- STORE('dbi_connect_closure' CODE(0xa933468))=3D 1 at
DBI.pm line 691
    <- FETCH('Driver')=3D DBI::dr=3DHASH(0xa6cf5a0) at DBI.pm line 630
    <> FETCH('Name')=3D 'Oracle' ('Name' from cache) at DBI.pm line 630
INSERT INTO store_cart (session_id) VALUES (?):
'5d6630ab21bbffd47e534530e254ab39b7246656'

    <- FETCH('Active')=3D 1 at DBI.pm line 538
    <- ping=3D 1 at DBI.pm line 538
    <- prepare_cached('INSERT INTO store_cart (session_id) VALUES (?)'
HASH(0xa990674) ...)=3D DBI::st=3DHASH(0xa990734) at
DBI.pm line 890
    <- execute('5d6630ab21bbffd47e534530e254ab39b7246656')=3D 1 at DBI.pm l=
ine 782
    <- FETCH('Active')=3D 1 at DBI.pm line 538
    <- ping=3D 1 at DBI.pm line 538
    <- FETCH('RaiseError')=3D '' at
DBI.pm line 906
    <- STORE('RaiseError' undef)=3D 1 at DBI.pm line 906
    <- STORE('RaiseError' 1)=3D 1 at DBI.pm line 906
    <- FETCH('PrintError')=3D '' at DBI.pm line 907

    <- STORE('PrintError' undef)=3D 1 at DBI.pm line 907
    <- STORE('PrintError' 0)=3D 1 at DBI.pm line 907
    <- column_info(undef undef ...)=3D DBI::st=3DHASH(0xa8efb6c) at DBI.pm =
line 910

    <- execute=3D '0E0' at DBI.pm line 911
    <- fetchrow_hashref=3D HASH(0xa8efec0)18keys row1 at DBI.pm line 913
    <- fetchrow_hashref=3D undef row4 at DBI.pm line 922
    <- DESTROY(DBI::st=3DHASH(a8efb00))=3D undef at
DBI.pm line 925
    <- STORE('PrintError' '')=3D 1 at Oracle.pm <http://oracle.pm/> line 44
    <- STORE('RaiseError' '')=3D 1 at Oracle.pm <http://oracle.pm/>
 line 44
    !! ERROR: '8002' 'ORA-08002: sequence STORE_CART_SEQ.CURRVAL is
not yet defined in this session (DBD ERROR: OCIStmtExecute)' (err#0)
    <- selectrow_array('SELECT store_cart_seq.currval FROM DUAL')=3D (
undef ) [1 items] at
Oracle.pm <http://oracle.pm/> line 16
       ERROR: '8002' 'ORA-08002: sequence STORE_CART_SEQ.CURRVAL is
not yet defined in this session (DBD ERROR: OCIStmtExecute)' (err#1)
    <- DESTROY(DBI::st=3DHASH(a8efc80))=3D undef at
Oracle.pm <http://oracle.pm/> line 17


Any direction in the proper way to handle oracle sequences would be
greatly appreciate.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070524/40d=
3a405/attachment.htm


More information about the Dbix-class mailing list