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

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

Hi Ron,

Thank you for your prompt email reply.

I changed the table definition by following The dosc.
That works fine.

    ID CHAR(40) primary key
    A_SESSION LONG NOT NULL  #  VARCHAR2 --> LONG

But Oracle docs says not use LONG type because it is for backward 
compatibility.
ref:http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF00201

and CLOB has some restrictions.
ref:http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_data_interface.htm#CACIFCJF

That's why I choosed the VARCHAR2.

A_SESSION colums must be LONG type?

Sincerely,



(2013/12/18 9:36), Ron Savage wrote:
> 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
>>
>