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

Gradebook Export to Excel causes SQL Error (ORA-00923) on Oracle

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Duplicate
    • Icon: Major Major
    • None
    • 2.5, 2.5.1, 2.5.2
    • Gradebook
    • Oracle
    • MOODLE_25_STABLE

      Trying to export the gradebook as Excel spreadsheet causes SQL errors if an Oracle database is being used.

      The issue occurs in two spots: (1) the preview and (2) when a user actually clicks on the download button.

      -----------------------------------------------
      (1) Preview SQL Error:
      -----------------------------------------------
      Debug info: ORA-00923: FROM keyword not found where expected
      SELECT u.*, cf0.data AS 'customfield_managersemail' , u.lastname AS usrt1, u.firstname AS usrt2, u.id AS usrt
      FROM m_user u
      JOIN (SELECT DISTINCT eu1_u.id
      FROM m_user eu1_u
      JOIN m_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id
      JOIN m_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = :o_eu1_courseid)
      WHERE eu1_u.deleted = 0 AND eu1_u.id <> :o_eu1_guestid AND eu1_ue.status = :o_eu1_active AND eu1_e.status = :o_eu1_enabled AND eu1_ue.timestart < :o_eu1_now1 AND (eu1_ue.timeend = 0 OR eu1_ue.timeend > :o_eu1_now2)) je ON je.id = u.id

      LEFT JOIN (SELECT * FROM m_user_info_data
      WHERE fieldid = :o_cf0) cf0
      ON u.id = cf0.userid
      JOIN (
      SELECT DISTINCT ra.userid
      FROM m_role_assignments ra
      WHERE ra.roleid IN (:o_grbr9,:o_grbr10)
      AND ra.contextid IN (2265,654,1)
      ) rainner ON rainner.userid = u.id
      WHERE u.deleted = 0

      ORDER BY usrt1 ASC, usrt2 ASC, usrt ASC
      [array (
      'o_eu1_courseid' => '464',
      'o_eu1_guestid' => '1',
      'o_eu1_active' => 0,
      'o_eu1_enabled' => 0,
      'o_eu1_now1' => 1378997300,
      'o_eu1_now2' => 1378997300,
      'o_cf0' => '22',
      'o_grbr9' => '5',
      'o_grbr10' => '7',
      )]
      Error code: dmlreadexception
      Stack trace:

      line 423 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 280 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
      line 1096 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      line 216 of /grade/lib.php: call to oci_native_moodle_database->get_recordset_sql()
      line 244 of /grade/export/lib.php: call to graded_users_iterator->init()
      line 61 of /grade/export/xls/index.php: call to grade_export->display_preview()

      ---------------------------------------------------------------------
      ---------------------------------------------------------------------

      (2) Download SQL Error:
      -----------------------------------------------
      Debug info: ORA-00923: FROM keyword not found where expected
      SELECT u.*, cf0.data AS 'customfield_managersemail' , u.lastname AS usrt1, u.firstname AS usrt2, u.id AS usrt
      FROM m_user u
      JOIN (SELECT DISTINCT eu1_u.id
      FROM m_user eu1_u
      JOIN m_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id
      JOIN m_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = :o_eu1_courseid)
      WHERE eu1_u.deleted = 0 AND eu1_u.id <> :o_eu1_guestid AND eu1_ue.status = :o_eu1_active AND eu1_e.status = :o_eu1_enabled AND eu1_ue.timestart < :o_eu1_now1 AND (eu1_ue.timeend = 0 OR eu1_ue.timeend > :o_eu1_now2)) je ON je.id = u.id

      LEFT JOIN (SELECT * FROM m_user_info_data
      WHERE fieldid = :o_cf0) cf0
      ON u.id = cf0.userid
      JOIN (
      SELECT DISTINCT ra.userid
      FROM m_role_assignments ra
      WHERE ra.roleid IN (:o_grbr1,:o_grbr2)
      AND ra.contextid IN (2265,654,1)
      ) rainner ON rainner.userid = u.id
      WHERE u.deleted = 0

      ORDER BY usrt1 ASC, usrt2 ASC, usrt ASC
      [array (
      'o_eu1_courseid' => '464',
      'o_eu1_guestid' => '1',
      'o_eu1_active' => 0,
      'o_eu1_enabled' => 0,
      'o_eu1_now1' => 1378997600,
      'o_eu1_now2' => 1378997600,
      'o_cf0' => '22',
      'o_grbr1' => '5',
      'o_grbr2' => '7',
      )]
      Error code: dmlreadexception
      Stack trace:

      line 423 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 280 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
      line 1096 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      line 216 of /grade/lib.php: call to oci_native_moodle_database->get_recordset_sql()
      line 69 of /grade/export/xls/grade_export_xls.php: call to graded_users_iterator->init()
      line 49 of /grade/export/xls/export.php: call to grade_export_xls->print_grades()

            poltawski Dan Poltawski
            icefresh Mike
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.