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

Oracle Case-sensitivity fix

XMLWordPrintable

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

      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

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

            skodak Petr Skoda
            danmarsden Dan Marsden
            Votes:
            7 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved:

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