Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-11629

Oracle can't store multiple choice fractional value questions.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.8.2, 1.9
    • Fix Version/s: 1.8.5, 1.9.1
    • 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

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  8/Apr/08