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

After upgraded to 2.2.3, view assignment get ORA-00932 "inconsistent datatypes: expected - got CLOB"

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 2.2.3
    • Fix Version/s: None
    • Component/s: Assignment (2.2)
    • Labels:
    • Database:
      Oracle
    • Affected Branches:
      MOODLE_22_STABLE

      Description

      After upgraded to 2.2.3, view assignment get ORA-00932: "inconsistent datatypes: expected - got CLOB". I have tracked the problem is from this SQL statement.

      mod/assignment/type/upload/assignment.class.php line 415-420

              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
                                                    s.data2 = 'submitted'", $params);

      In Oracle, the datatype of m_assignment_submission.data2 is clob, clob cannot compare with varchar. So I changed code like this:

      changed sql statement

              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
                                                    dbms_lob.substr(s.data2,9,1) = 'submitted'", $params);

      This time, there's no error, but all submitted assignment is lost, because this sql statement return zero records in my old course.
      At last, I changed code like this:

      code that's seems ok 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 dbms_lob.substr(s.data2,9,1) = 'submitted'"                                                     
                                            , $params);
       

      Now, all the submitted assignments is showed. But omitted data2 field must means something which I don't understand.
      I hope moodle developer can pay attention to this, solve it quickly.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                poltawski Dan Poltawski
                Reporter:
                luyanfei Yanfei Lu
                Participants:
                Component watchers:
                Damyon Wiese
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: