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

role_names->text causing problems under Oracle

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 1.9
    • 1.9
    • Roles / Access
    • None
    • Oracle
    • MOODLE_19_STABLE
    • MOODLE_19_STABLE

    Description

      Was testing a bit Moodle 1.9 under Oracle, when I got this error:

      ORA-00932: inconsistent datatypes: expected CLOB got CHAR

      SELECT r.id, COALESCE(rn.text, r.name) AS name FROM m_role r JOIN ( SELECT DISTINCT allowassign as allowedrole FROM m_role_allow_assign raa WHERE raa.roleid IN (3,1) ) ar ON r.id=ar.allowedrole JOIN m_role_capabilities rc ON (r.id = rc.roleid AND rc.capability = 'moodle/course:view' AND rc.capability != 'moodle/site:doanything') LEFT OUTER JOIN m_role_names rn ON (rn.roleid = r.id AND rn.contextid = 23) ORDER BY sortorder ASC

      It seems that the role_names->text field is of type TEXT (CLOB) and that prevents it to be used within COALESCE and other functions.

      If I'm not wrong that table, role_names, is exclusively used to name roles in a different way per context. If that is the only usage I would recommend to:

      1) create one new role_names->name char(255) not null field.
      2) copy current role_names->text contents to role_names->name
      3) drop role_names->text
      4) Change usages from the old field to the new one.

      That way the field will be better and his behaviour cross-db.

      Ciao

      P.S.: Raising this to critical... to alarm about it (prevents Oracle usage completely)

      Attachments

        Issue Links

          Activity

            People

              dougiamas Martin Dougiamas
              stronk7 Eloy Lafuente (stronk7)
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Laurent David, Sara Arjona (@sarjona)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                3/Mar/08