Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-30370 Meta: Oracle SQL issues
  3. MDL-32063

Error viewing assignments with teacher role (in Oracle)

    XMLWordPrintable

    Details

    • Database:
      Oracle
    • Workaround:
      Hide

      A casting must be done with the troublemaker column (data2). This one works at least in Oracle:

      ...
      return $DB->count_records_sql("SELECT COUNT('x')
                                       FROM {assignment_submissions} s
                                  LEFT JOIN {assignment} a ON a.id = s.assignment
                                 INNER JOIN ($enroledsql) u ON u.id = s.userid
                                      WHERE s.assignment = :assignmentid AND "
                                            . $DB->sql_compare_text("s.data2") . " = 'submitted'", $params);

      Show
      A casting must be done with the troublemaker column (data2). This one works at least in Oracle: ... return $DB->count_records_sql("SELECT COUNT('x') FROM {assignment_submissions} s LEFT JOIN {assignment} a ON a.id = s.assignment INNER JOIN ($enroledsql) u ON u.id = s.userid WHERE s.assignment = :assignmentid AND " . $DB->sql_compare_text("s.data2") . " = 'submitted'", $params);
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE

      Description

      Using Oracle, when you try to view an assignment (/mod/assignment/view.php) sometimes it crashes with ORA-00932 inconsistent datatypes: expected - got CLOB

      * line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
      * line 268 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end()
      * line 1093 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      * line 1290 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_records_sql()
      * line 1029 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->get_record_sql()
      * line 1365 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_record_sql()
      * line 1536 of \lib\dml\moodle_database.php: call to moodle_database->get_field_sql()
      * line 406 of \mod\assignment\type\upload\assignment.class.php: call to moodle_database->count_records_sql()
      * line 3753 of \mod\assignment\lib.php: call to assignment_upload->count_real_submissions()
      * line 3498 of \lib\navigationlib.php: call to assignment_extend_settings_navigation()
      * line 2914 of \lib\navigationlib.php: call to settings_navigation->load_module_settings()
      * line 601 of \lib\pagelib.php: call to settings_navigation->initialise()
      * line 617 of \lib\pagelib.php: call to moodle_page->magic_get_settingsnav()
      * line 133 of \blocks\settings\block_settings.php: call to moodle_page->__get()
      * line 280 of \blocks\moodleblock.class.php: call to block_settings->get_content()
      * line 232 of \blocks\moodleblock.class.php: call to block_base->formatted_contents()
      * line 926 of \lib\blocklib.php: call to block_base->get_content_for_output()
      * line 978 of \lib\blocklib.php: call to block_manager->create_block_contents()
      * line 349 of \lib\blocklib.php: call to block_manager->ensure_content_created()
      * line 6 of \theme\base\layout\general.php: call to block_manager->region_has_content()
      * line 654 of \lib\outputrenderers.php: call to include()
      * line 612 of \lib\outputrenderers.php: call to core_renderer->render_page_layout()
      * line ? of unknownfile: call to core_renderer->header()
      * line 1296 of \lib\setuplib.php: call to call_user_func_array()
      * line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->__call()
      * line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->header()
      * line 51 of \mod\assignment\type\upload\assignment.class.php: call to assignment_base->view_header()
      * line 51 of \mod\assignment\view.php: call to assignment_upload->view().

      I've seen that the problem is when it executes the following sql on line 401 of /mod/assignment/type/upload/assignment.class.php:

      ...
      SELECT COUNT('x')
        FROM {assignment_submissions} s
        LEFT JOIN {assignment} a ON a.id = s.assignment
       INNER JOIN ($enroledsql) u ON u.id = s.userid
       WHERE s.assignment = :assignmentid
         AND s.data2 = 'submitted'

      The reason is that the data2 columns' datatype is clob, and (in Oracle) you cannot make comparisons between LOB columns and strings.

        Attachments

          Activity

            People

            Assignee:
            stronk7 Eloy Lafuente (stronk7)
            Reporter:
            izendegi Iñigo Zendegi
            Participants:
            Component watchers:
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: