Moodle
  1. Moodle
  2. MDL-31382

Cannot create questions using Oracle 10 in Moodle 2.1 and onwards

    Details

    • Database:
      Oracle
    • Testing Instructions:
      Hide

      Install a Moodle 2.1 or 2.2 version
      Go to the question bank (general or in a course)
      Create a new question multichoice, with penalty (decimal)

      Show
      Install a Moodle 2.1 or 2.2 version Go to the question bank (general or in a course) Create a new question multichoice, with penalty (decimal)
    • Workaround:
      Hide

      I think the problem is in the base form:
      https://github.com/moodle/moodle/blob/MOODLE_21_STABLE/question/type/edit_question_form.php#L382

      The penalty is not "typed" as number

      In previous versions (2.0), the penalty field is "typed" as number
      https://github.com/moodle/moodle/blob/MOODLE_20_STABLE/question/type/edit_question_form.php#L149
      $mform->setType('penalty', PARAM_NUMBER);

      Show
      I think the problem is in the base form: https://github.com/moodle/moodle/blob/MOODLE_21_STABLE/question/type/edit_question_form.php#L382 The penalty is not "typed" as number In previous versions (2.0), the penalty field is "typed" as number https://github.com/moodle/moodle/blob/MOODLE_20_STABLE/question/type/edit_question_form.php#L149 $mform->setType('penalty', PARAM_NUMBER);
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Rank:
      37899

      Description

      When trying to create a new question (multichoice) we got this error:

      [Wed Jan 18 18:26:04 2012] [error] [client 10.100.100.21] Default exception handler: Error al escribir a la base de datos Debug: ORA-01722: n\xc3\xbamero no v\xc3\xa1lido\n
      INSERT INTO m_question (category,qtype,name,parent,length,penalty,questiontext,questiontextformat,generalfeedback,generalfeedbackformat,defaultmark,stamp,createdby,timecreated) VALUES (:category,:qtype,:name,:parent,:length,:penalty,:questiontext,:questiontextformat,:generalfeedback,:generalfeedbackformat,:defaultmark,:stamp,:createdby,:timecreated) RETURNING id INTO :oracle_id\n[array (\n 'category' => '2',\n 'qtype' => 'multichoice',\n 'name' => '1',\n 'parent' => 0,\n 'length' => 1,\n 'penalty' => '0.3333333',\n 'questiontext' => '<p>asfsadf</p>',\n 'questiontextformat' => '1',\n 'generalfeedback' => '',\n 'generalfeedbackformat' => '1',\n 'defaultmark' => 1,\n 'stamp' => 'mydomain.es+120118172604+bxcrL1',\n 'createdby' => '2',\n 'timecreated' => 1326907564,\n)]\n* line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown\n* line 273 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()\n* line 1205 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()\n* line 1248 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->insert_record_raw()\n* line 393 of /question/type/questiontypebase.php: call to oci_native_moodle_database->insert_record()\n* line 264 of /question/question.php: call to question_type->save_question()\n, referer: http://mydomain.es/question/question.php?returnurl=%2Fquestion%2Fedit.php%3Fcmid%3D82&cmid=82&category=2&qtype=multichoice

      According to: http://www.orafaq.com/wiki/ORA-01722
      An ORA-01722 error occurs when an attempt is made to convert a character string into a number

        Issue Links

          Activity

          Hide
          Juan Leyva added a comment -

          Please, note that seems that the incorrect field is:

          'penalty' => '0.3333333',

          See the workaround, I cannot test it because I haven't access to the server where the problem is

          Show
          Juan Leyva added a comment - Please, note that seems that the incorrect field is: 'penalty' => '0.3333333', See the workaround, I cannot test it because I haven't access to the server where the problem is
          Hide
          Juan Leyva added a comment -

          I was suggested in the Developers chat to do the following:

          Set this enviroment option in Oracle:

          NLS_NUMERIC_CHARACTERS='.,'

          before the webserver starts

          Show
          Juan Leyva added a comment - I was suggested in the Developers chat to do the following: Set this enviroment option in Oracle: NLS_NUMERIC_CHARACTERS='.,' before the webserver starts
          Hide
          Tim Hunt added a comment -

          Any news about whether that suggested fix solves the problem?

          Show
          Tim Hunt added a comment - Any news about whether that suggested fix solves the problem?
          Hide
          Juan Leyva added a comment -

          Our client switch to MySQL and since Moodle is faster they are not going back to Oracle

          Sorry for not providing any useful feedback

          Show
          Juan Leyva added a comment - Our client switch to MySQL and since Moodle is faster they are not going back to Oracle Sorry for not providing any useful feedback
          Hide
          Eloy Lafuente (stronk7) added a comment -

          From HQ:

          12:59:27] <Cindereloy> please, allow me to pass here. I did not delete that line from the driver nor agreed with that measure. So I'm not going to decide about that. The guy that did that (Petr) seemed to have some good reasons that I don't know.

          Show
          Eloy Lafuente (stronk7) added a comment - From HQ: 12:59:27] <Cindereloy> please, allow me to pass here. I did not delete that line from the driver nor agreed with that measure. So I'm not going to decide about that. The guy that did that (Petr) seemed to have some good reasons that I don't know.
          Hide
          Tim Hunt added a comment -

          To explain Eloy's last comment:

          (11:55:29 AM) jleyva@jabber.org: the problem is that ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' was present in configure_dbconnection (Moodle 1.9) but now has been removed

          ...

          (11:59:27 AM) Cindereloy: please, allow me to pass here. I did not delete that line from the driver nor agreed with that measure. So I'm not going to decide about that. The guy that did that (Petr) seemed to have some good reasons that I don't know.

          Petr, can you help us here?

          • Do I need to add a cast to float to my code, or
          • Should the oracle DB class handle this, or
          • Should I just close this won'tfix and rely on Oracle admins reading the docs and setting NLS_NUMERIC_CHARACTERS manually?

          Thanks.

          Show
          Tim Hunt added a comment - To explain Eloy's last comment: (11:55:29 AM) jleyva@jabber.org: the problem is that ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' was present in configure_dbconnection (Moodle 1.9) but now has been removed ... (11:59:27 AM) Cindereloy: please, allow me to pass here. I did not delete that line from the driver nor agreed with that measure. So I'm not going to decide about that. The guy that did that (Petr) seemed to have some good reasons that I don't know. Petr, can you help us here? Do I need to add a cast to float to my code, or Should the oracle DB class handle this, or Should I just close this won'tfix and rely on Oracle admins reading the docs and setting NLS_NUMERIC_CHARACTERS manually? Thanks.
          Hide
          Juan Leyva added a comment -

          I've added a note also in the WIKI page for the Oracle installation (PHP)

          http://docs.moodle.org/en/Installing_Oracle_for_PHP#Configure_Apache

          See also this related ticket: http://tracker.moodle.org/browse/MDL-11629

          Show
          Juan Leyva added a comment - I've added a note also in the WIKI page for the Oracle installation (PHP) http://docs.moodle.org/en/Installing_Oracle_for_PHP#Configure_Apache See also this related ticket: http://tracker.moodle.org/browse/MDL-11629
          Hide
          Petr Škoda added a comment -

          Hi,

          I do not have Oracle test install and necessary knowledge, sorry. I never maintained the oracle driver, feel free to do any changes inside it that do not affect other drivers.

          We should imo:

          • convert all user input (strings with , or . based on current lang) to PHP floats
          • us PHP floats when inserting/updating database
          Show
          Petr Škoda added a comment - Hi, I do not have Oracle test install and necessary knowledge, sorry. I never maintained the oracle driver, feel free to do any changes inside it that do not affect other drivers. We should imo: convert all user input (strings with , or . based on current lang) to PHP floats us PHP floats when inserting/updating database
          Hide
          Tim Hunt added a comment -

          This is not user input. This is a value from a select element in a form.

          Show
          Tim Hunt added a comment - This is not user input. This is a value from a select element in a form.
          Hide
          Petr Škoda added a comment - - edited

          Anything coming from POST is a user input here - labels have to be localised for the user, the values shoudl be casted to proper PHP type.

          Show
          Petr Škoda added a comment - - edited Anything coming from POST is a user input here - labels have to be localised for the user, the values shoudl be casted to proper PHP type.
          Hide
          Petr Škoda added a comment -

          I guess the DML unittests should be improved first and then the driver + coding style and code should be updated. My vote goes to explicit casting of string decimals to PHP floats before storage to database. Fetching from database is yet another problem, I guess it is broken in Oracle driver too when some locale settings used there.

          I think that HQ (or anybody else) should either provide test servers for each DB supported (all possible versions and configurations) or we have to stop pretending we are supporting Oracle and MSSQL...

          Show
          Petr Škoda added a comment - I guess the DML unittests should be improved first and then the driver + coding style and code should be updated. My vote goes to explicit casting of string decimals to PHP floats before storage to database. Fetching from database is yet another problem, I guess it is broken in Oracle driver too when some locale settings used there. I think that HQ (or anybody else) should either provide test servers for each DB supported (all possible versions and configurations) or we have to stop pretending we are supporting Oracle and MSSQL...
          Hide
          Tim Hunt added a comment -

          OK, reassigning to HQ. This is clearly a database/policy issue.

          Show
          Tim Hunt added a comment - OK, reassigning to HQ. This is clearly a database/policy issue.
          Hide
          Matthias Then added a comment -

          I had a similar Problem (Moodle 2.2.1, Oracle 11) and added the following lines of code in oci_native_moodle_database.php after line 214:
          ...
          $sql1 = "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'";
          $this->query_start($sql1, null, SQL_QUERY_UPDATE);
          $stmt1 = $this->parse_query($sql1);
          $result = oci_execute($stmt1, $this->commit_status);
          $this->query_end($result, $stmt1);
          oci_free_statement($stmt1);
          ...
          After I restarted my server it worked.
          I think the problem was, that our database is configured for German language and so the NLS_NUMERIC_CHARACTERS is ',.' by default.

          Show
          Matthias Then added a comment - I had a similar Problem (Moodle 2.2.1, Oracle 11) and added the following lines of code in oci_native_moodle_database.php after line 214: ... $sql1 = "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'"; $this->query_start($sql1, null, SQL_QUERY_UPDATE); $stmt1 = $this->parse_query($sql1); $result = oci_execute($stmt1, $this->commit_status); $this->query_end($result, $stmt1); oci_free_statement($stmt1); ... After I restarted my server it worked. I think the problem was, that our database is configured for German language and so the NLS_NUMERIC_CHARACTERS is ',.' by default.
          Hide
          Michael de Raadt added a comment -

          This looks like it has been hanging around for a bit.

          Show
          Michael de Raadt added a comment - This looks like it has been hanging around for a bit.

            People

            • Votes:
              4 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated: