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

role_names->text causing problems under Oracle

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 1.9
    • Fix Version/s: 1.9
    • Component/s: Roles / Access
    • Labels:
      None
    • Database:
      Oracle
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      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

              • Assignee:
                dougiamas Martin Dougiamas
                Reporter:
                stronk7 Eloy Lafuente (stronk7)
                Participants:
                Component watchers:
                Amaia Anabitarte, Bas Brands, Carlos Escobedo, Sara Arjona (@sarjona), Víctor Déniz Falcón
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  3/Mar/08