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

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Won't Do
    • Icon: Critical Critical
    • None
    • 2.6, 2.8.2, 2.8.8, 2.9.2, BACKEND
    • Glossary
    • Oracle
    • MOODLE_26_STABLE, MOODLE_28_STABLE, MOODLE_29_STABLE
    • MDL-43816_2_master
    • 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.
    • Team '; drop tables Sprint 9, Team ';drop tables Sprint 10
    • Large

      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()

            Unassigned Unassigned
            icefresh Mike
            David Monllaó David Monllaó
            Votes:
            4 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved:

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