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

Glossary Full Text Search throws ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    Details

    • Type: Bug
    • Status: Reopened
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 2.6, 2.8.2, 2.8.8, 2.9.2, BACKEND
    • Fix Version/s: None
    • Component/s: Glossary
    • Labels:
    • Database:
      Oracle
    • Testing Instructions:
      Hide

      Use Moodle with Oracle DB

      Before applying the patch

      1. Create a glossary
      2. Create a huge entry with more than 4000 chars, you can create some of them.
      3. Use glossary search (look up a word in your large entry - I used a lot of "lorem ipsum" so I searched for "lorem")
      4. You should get a database error

      After applying the patch
      NB You must reinstall the oci_native_moodle_package.sql script. You can do this by using sqlplus:

      1. drop package MOODLELIB
      2. drop package moodlelib
      3. @path/to/my/moode/lib/dml/oci_native_moodle_package.sql;
      4. GRANT EXECUTE ON moodlelib to your_oracle_user;
      5. GRANT EXECUTE ON MOODLELIB to your_oracle_user;
      6. GRANT EXECUTE ON DBMS_LOCK to your_oracle_user;
      7. Verify you installed the updated package with desc MOODLELIB, check that you see:
      FUNCTION TRICONCAT RETURNS VARCHAR2
       Argument Name			Type			In/Out Default?
       ------------------------------ ----------------------- ------ --------
       STRING1			CLOB			IN
       STRING2			CLOB			IN
       STRING3			CLOB			IN
      

      (If you see VARCHAR2 instead of CLOB, something didn't work properly).

      Testing

      1. Follow steps 1-3 from before applying the patch.
      2. Ensure you do not get a database error, and you see the large glossary definition with all instances of your search term highlighted.
      Show
      Use Moodle with Oracle DB Before applying the patch Create a glossary Create a huge entry with more than 4000 chars, you can create some of them. Use glossary search (look up a word in your large entry - I used a lot of "lorem ipsum" so I searched for "lorem") You should get a database error After applying the patch NB You must reinstall the oci_native_moodle_package.sql script. You can do this by using sqlplus: drop package MOODLELIB drop package moodlelib @path/to/my/moode/lib/dml/oci_native_moodle_package.sql; GRANT EXECUTE ON moodlelib to your_oracle_user; GRANT EXECUTE ON MOODLELIB to your_oracle_user; GRANT EXECUTE ON DBMS_LOCK to your_oracle_user; Verify you installed the updated package with desc MOODLELIB , check that you see: FUNCTION TRICONCAT RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- STRING1 CLOB IN STRING2 CLOB IN STRING3 CLOB IN (If you see VARCHAR2 instead of CLOB, something didn't work properly). Testing Follow steps 1-3 from before applying the patch. Ensure you do not get a database error, and you see the large glossary definition with all instances of your search term highlighted.
    • Affected Branches:
      MOODLE_26_STABLE, MOODLE_28_STABLE, MOODLE_29_STABLE
    • Pull Master Branch:
      MDL-43816_2_master
    • Sprint:
      Team '; drop tables Sprint 9, Team ';drop tables Sprint 10
    • Issue size:
      Large

      Description

      Using the full text search in the glossary returns:

      Error reading from database

      More information about this error
      Debug info: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "MOODLEP.MOODLELIB", line 86

      SELECT COUNT(DISTINCT(ge.id)) FROM m_glossary_entries ge
      LEFT JOIN m_glossary_alias al ON al.entryid = ge.id WHERE (ge.glossaryid = :o_gid1 or ge.sourceglossaryid = :o_gid2) AND
      (ge.approved <> 0 OR ge.userid = :o_myid)
      AND (LOWER( MOODLELIB.UNDO_MEGA_HACK(MOODLELIB.TRICONCAT(ge.concept, '*OCISP*', MOODLELIB.TRICONCAT(ge.definition, '*OCISP*', COALESCE(al.alias, :o_emptychar)))) ) LIKE LOWER(:o_ss1) ESCAPE '\')
      [array (
      'o_gid1' => '121',
      'o_gid2' => '121',
      'o_myid' => '282',
      'o_emptychar' => '',
      'o_ss1' => '%Account%',
      )]
      

      Error code: dmlreadexception
      Stack trace:

      line 441 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 271 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
      line 1122 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      line 1415 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_records_sql()
      line 1056 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->get_record_sql()
      line 1488 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_record_sql()
      line 1659 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      line 270 of /mod/glossary/sql.php: call to moodle_database->count_records_sql()
      line 378 of /mod/glossary/view.php: call to require()

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  3 Vote for this issue
                  Watchers:
                  6 Start watching this issue

                  Dates

                  • Created:
                    Updated: