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

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

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.
>>

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