-
Bug
-
Resolution: Duplicate
-
Minor
-
None
-
2.2.3
-
Oracle
-
MOODLE_22_STABLE
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.
