[Cgi-session-user] ORA-03124

[Junji O. <_j...@wa...>]

Dear members,

This is my first post to cgi-session-user ML.
I'm a Oracle DBA in japanese company.

My co-worker gets following error messages when using 
CGI::Session->load() function
to get the CHAR type column's data from Oracle 11g R1.

/////qw start/////
DBD::Oracle::st fetchrow_array failed: ORA-03124: two-task internal 
error (DBD ERROR: OCIStmtFetch) [for Statement "SELECT a_SESSION FROM 
TEST_TB WHERE ID=?" with ParamValues: 
:p1='84c8aab5140cbf000a3ddaf80257b7c2695b6c23'] at 
CGI/Session/Driver/DBI.pm line 74.
/////qw end/////

'TEST_TB' table was defined like

   "ID" CHAR(40 BYTE)             as PRIMARY KEY
   "A_SESSION" VARCHAR2(4000 BYTE)

As "CGI::Session::DBI" manual says that CAHR data type
is recommended for ID column.

but , we got some workarounds here.
    changing table column type from 'CHAR' to 'VARCHAR2'
  or
    adding oracle's init parameter
  [ alter session set \"_enable_row_shipping\"=false]

then ,everything will work fine.

Oracle support guys said
" that's perl side problem, because no alert messeges in alart_log at DB 
server side , and no ORA-03124 message between client and server by 
taking Oracle Net trace at oracle client side.
but Oracle Net trace shows 'ORA-01013' just before error, which means 
that client canceled request to query for some reasons."

We can easily reproduce the same error without CGI::Session like ,

//////////////
    $sql1 = "SELECT a_SESSION FROM TEST_TB WHERE ID=?";
    $sth = $dbh->prepare($sql1);
    $sth->bind_param( 1,'84c8aab5140cbf000a3ddaf80257b7c2695b6c23');
    $sth->execute();
    $sth->fetchrow_arrayref();
//////////////
then, get error ORA-03124.

and workarround here like,
//////////////
    $sth->bind_param( 1,'84c8aab5140cbf000a3ddaf80257b7c2695b6c23',SQL_CHAR)
//////////////
then ,work fine.

Is this a perl's problem or Oracle?

Could you advise me about why this happened.

Sincerely,

### CODE ###
#### code CGI::Session->load() ####
$s2 = CGI::Session->load(
         'driver:Oracle',
         '84c8aab5140cbf000a3ddaf80257b7c2695b6c23',
         { TableName => 'TEST_TB',
                 IdColName   => 'ID',
                 DataColName => 'a_SESSION',
                 Handle      => $dbh,
           }
);

$rv =$s2->param('_SESSION_ID');
print "$rv \n";


### Oracle table DDL###
  CREATE TABLE "TEST_TB"
    (    "ID" CHAR(40 BYTE),
     "A_SESSION" VARCHAR2(4000 BYTE) CONSTRAINT "TEST_TB_CK1" NOT
NULL ENABLE,
      CONSTRAINT "TEST_TB_PK1" PRIMARY KEY ("ID")
    )

### our lab ##
Oracle 11.1.0.7.12 SE RAC
CentOS 5.3 64bit

perl, v5.8.8
'CGI/Session.pm' => '4.42
'DBI.pm' => '1.609