Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 1.8.2, 1.9
-
Component/s: Database SQL/XMLDB
-
Labels:None
-
Environment:Moodle 1.8.2+, PHP 5.2.4, Oracle 10g, Windows 2003.
-
Database:Oracle
-
Affected Branches:MOODLE_18_STABLE, MOODLE_19_STABLE
-
Fixed Branches:MOODLE_18_STABLE, MOODLE_19_STABLE
Description
When defining multiple choice questions with multiple valid anwers, we can define how mucho of the grade is assgined to each answer (20%, 50% and so on). When we try to save that into the database, the grade percentage is stored as a floating point number. save_question_options() (from .../question/type/multichoice/questiontype.php) sets $anwer->fraction to $question->fraction[$key], which is a floating point value, and then calls either insert_record() or update_record() with the $answer object.
Neither insert_record() nor update_record() do any special treatment to floating point numbers. They just put the value inside single quotes and execute the SQL sentece. But Oracle is very picky with value types and NLS conventions. If you give it a character string value where it expects a number, as long as it can convert the string to a valid number (of the same type of the field we are using) it's happy. But If you execute:
SELECT to_number('0.5') FROM DUAL;
in a database configured to use Spanish NLS conventions (',' as the decimal separator and '.' as the group separator), it barfs about a non-valid value, whie if you execute:
SELECT to_number('0,5') FROM DUAL;
it converts the number happily.
As the questions code is working with floating values everywhere, we need to make sure that what we send to Oracle can be converted (taking NLS issues into account) to anything that matches the destination field type. We can do this by changing the session NLS settings (so we don't need to change the whole database NSL settings). This can be done with a query like this:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'
to use the '.' character as the decimal separator and ',' as the group separator (see additional details at http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/initparams130.htm)
The attached patch (for 1.8 at the moment) fixes the issue and defines a new optional config.php setting to specify the NLS_NUMERIC_CHARACTERS value in case someone needs some particular value. Otherwise '.,' is used as the default.
Saludos. Iñaki.
Attachments
Issue Links
Activity
Hi! Thanks for pinging me with this. I had it lost in a mountain of tasks! Sorry!
About the change to apply:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' (in the configure_dbconnection() function)
I agree 100%. I only think that perhaps it isn't necessary to have that value modifiable at all. AFAIK, PHP always uses floats internally using the DOT as decimal separator, and the new gradebook, has automatic conversion for manual floats back to DOT floats.
So I think we can guarantee that all floats going to DB will be always DOT floats. So I think it's safe to set the DOT always as session decimal separator. Do you see any case where this isn't valid and we need the $CFG possibility?
Ciao 
Hi Eloy,
I don't really know if there is a case where that assumption isn't valid or not. That's why I initally proposed the $CFG configuration parameter. But you know Moodle internal more than I do, so feel free to drop it if you think we don't need it
Saludos. Iñaki.
Saludos. Iñaki. BTW, what are you doing awake ? :-P
I'll commit the change tomorrow....thanks for feedback and ciao
Change applied to 18_STABLE, 19_STABLE and HEAD.
Thanks!
Ciao
I think it is important to fix the code in quiz in the first place, because this fixes it only for Oracle, not other databases - if I remember it correctly they get the numbers without the decimals 
Patch for 1.9dev