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

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

Hi Junji

On 19/12/13 15:14, Junji Ochi wrote:
> Hi Ron
>
> I'm so sorry to take your time for some extra work with my fault.
> I should be more careful to my words.

No problem!

>   > But (guessing) perhaps the app stores \x0 (null) into the session?
>   > Or perhaps it stores a value which contains a control char?
> It could be. I need more investigation.

Don't spend too much time on it. Now you know how to set the column 
types, the problem is solved.

> I have two more questions.
>
> 1. Which do you think there is a problem of perl or Oracle?

It's the combination. Perl definitely assumes /any/ string of chars can 
be stored in the a_session column, where as (IMHO) Oracle is imposing 
some sort of constraint on the chars (or on their sequence).

> 2. ORA-03124 comes out at "$sth->fetchrow_array()" line
> on CGI/Session/Driver/DBI.pm line 74
> Is this your code ?
> or
> DBD::Oracle::st's author know a clue?
> pelase see below quote

CGI::Session::Driver::DBI was I believe written by the original author 
of CGI::Session, Sherzod Ruzmetov. He has not been sighted on this list 
for quite a few years now.

The error is caused by the execution of code in 
CGI::Session::Driver::DBI, but it is DBD::Oracle which is detecting and 
reporting the error.

> /////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/////
>
> Sincerely,
>
> (2013/12/19 10:51), Ron Savage wrote:
>> Hi Junji
>>
>> Note: I'm on the mailing list, so no need to copy me personally.
>>
>> More below.
>>
>> On 19/12/13 12:12, Junji Ochi wrote:
>>> Good morning Ron
>>>
>>> I had a screw-up.
>>>
>>> I said that changing the ID col data type to VARCHAR2 would be a
>>> workarround.
>>> but ,It doesn't matter if the ID col is VARCHAR2 or CHAR
>>>
>>> I tested again for you.
>>> +--------+---------+--------+
>>> |ID      |A_SESSION|RESULT  |
>>> +--------+---------+--------+
>>> |CHAR    |VARCHAR2 |NOT WORK|
>>> +--------+---------+--------+
>>> |VARCHAR2|VARCHAR2 |NOT WORK|
>>> +--------+---------+--------+
>>> |CHAR    |LONG     |WORK    |
>>> +--------+---------+--------+
>>> |VARCHAR2|LONG     |WORK    |
>>> +--------+---------+--------+
>>> |CHAR    |CLOB     |WORK    |
>>> +--------+---------+--------+
>>> |VARCHAR2|CLOB     |WORK    |
>>> +--------+---------+--------+
>>
>> Thanx for doing these tests.
>>
>> This is of great importance because I've already patched
>> CGI::Session::Driver::oracle and (my fork)
>> Data::Session::Driver::Oracle, but have not released them :-)).
>>
>> Now I won't have to, and using git I can just roll back the code to the
>> previous release :-).
>>
>>> It seemed VARCHAR2 on A_SESSION col caused problem.
>>>
>>> I have two question.
>>> 1.Should we use LONG type on A_SESSION col after all?
>>
>> Yes. It used to work, and I've just read the Oracle docs you linked to,
>> and I don't think we need worry about all the restrictions Oracle
>> mentioned pertaining to long. The code only uses the long field in a
>> /very/ simple way.
>>
>> BTW Don't forget that the id field is fixed-width. That is, once the
>> program runs, all values written into the id field are the same length.
>>
>> So char(N) makes sense there.
>>
>>> 2.Could you explain why VARCHAR2 cause problem. Was the data type
>>> strictly checked on A_SESSION col ?
>>
>> I think so. This link
>> http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF50956
>>
>> points to text which says 'To ensure proper data conversion between
>> databases with different character sets, you must ensure that VARCHAR2
>> data consists of well-formed strings.'. That last part, 'well-formed
>> strings', is very interesting.
>>
>> I feel - but can't be sure - that that is what is causing the problem.
>>
>> I cannot find any definition of 'well-formed strings' in Oracle's docs,
>> except for well-formed XML.
>>
>> But (guessing) perhaps the app stores \x0 (null) into the session?
>> Or perhaps it stores a value which contains a control char?
>>
>> Cheers
>> Ron
>>
>>> Sincerely,
>>>
>>>
>>> (2013/12/18 15:04), Ron Savage wrote:
>>>> Hi Junji
>>>>
>>>> See below.
>>>>
>>>> On 18/12/13 14:14, Junji Ochi wrote:
>>>>> Hi Ron,
>>>>>
>>>>> Thank you for your prompt email reply.
>>>>>
>>>>> I changed the table definition by following The dosc.
>>>>> That works fine.
>>>>
>>>> Excellent!
>>>>
>>>> And for letting us know:
>>>>
>>>> $many x $thanx;
>>>>
>>>>>      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?
>>>>
>>>> No I believe it does not have to be. It's just that when that code was
>>>> written years ago (1st version 2005) I believed it had to be.
>>>>
>>>> Since then, Oracle has been upgraded a few times :-).
>>>>
>>>> As long as VARCHAR2 works, and it's not deprecated by Oracle, then it's
>>>> a better choice that what's recommended in the docs.
>>>>
>>>> I'd better update the docs.
>>>>
>>
>
> ------------------------------------------------------------------------------
> 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/