Re: [Cgi-session-user] ORA-03124

[Ron S. <ro...@sa...>]

Hi Junji

The docs 
https://metacpan.org/pod/CGI::Session::Driver::oracle#The-sessions-table 
describe explicitly how to declare the table for Oracle.

Try that and tell us how it goes.

On 17/12/13 20:13, Junji Ochi wrote:
> 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
>
>
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Cgi-session-user mailing list
> Cgi...@li...
> https://lists.sourceforge.net/lists/listinfo/cgi-session-user
>

-- 
Ron Savage
http://savage.net.au/