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

Oracle Case-sensitivity fix

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Duplicate
    • 1.9
    • 2.0
    • Database SQL/XMLDB
    • None
    • MOODLE_19_STABLE
    • MOODLE_20_STABLE

    Description

      Oracle is case sensitive, so a query like:
      select * from mdl_user where firstname = "name"

      will only return exact matches - it won't return "Name", "NAME" etc like Mysql/postgres do.

      this is a problem in many areas of moodle code and there seem to be various bugs in the tracker that are related.

      Oracle 10g allows the setting of a Session var that changes this.

      one of our clients came up with this code which they've put in lib/setup.lib which seems to fix it nicely!

      if($CFG->dbtype == 'oci8po'){
      if (!empty ($CFG->nls_sort))

      { $db->execute('ALTER SESSION SET NLS_SORT=' . $CFG->nls_sort); }

      if (!empty ($CFG->nls_comp))

      { $db->execute('ALTER SESSION SET NLS_COMP=' . $CFG->nls_comp); }

      }

      they have then placed the correct vars for their version of oracle in the config vars $CFG->nls_sort and $CFG-nls_comp.

      I think we should be a bit more clever and determine which Oracle version they are using, and if they are using a version prior to 10gR2 then use "ANSI" as the NLS_COMP and 'BINARY_CI' for NLS_SORT
      but if they're using 10gR2 or higher use 'LINGUISTIC' for NLS_COMP and 'BINARY_CI' for NLS_SORT

      there's a good Oracle article here that describes the commands:
      http://www.orafaq.com/node/91

      NOTE: the changes for older versions of oracle will not make "LIKE" queries case insensitve - only oracle 10gR2 supports this with the above changes.

      Dan

      Attachments

        1. like.diff
          39 kB
        2. olike.txt
          2 kB

        Issue Links

          Activity

            People

              skodak Petr Skoda
              danmarsden Dan Marsden
              David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
              Votes:
              7 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                24/Nov/10