[Solved] CLOB to CHAR issue with DECODE in oracle 11g

EverSQL Database Performance Knowledge Base

CLOB to CHAR issue with DECODE in oracle 11g

Database type:

I am writing a query to extract data in a usable format for a vendor to move to a new system. The query previously worked on the same data, same tables, etc, etc on an oracle 9i database. We recently had to upgrade that database to Oracle 11g. Here is my statement:

SELECT ACTIVITY_TRACKER_ID,
  MAX(DECODE(PROTOCOL_NUMBER, NULL, TO_CHAR(PROTOCOL_NUMBER1), TO_CHAR(PROTOCOL_NUMBER))) PROTOCOL_NUMBER,
  MAX(DECODE(QUESTION_ID, '1', TO_CHAR(COMMENT_NOTES))) RESEARCHPURPOSE,
  MAX(DECODE(QUESTION_ID, '-1060', TO_CHAR(COMMENT_NOTES))) NOOFSUBJECTS,
  MAX(DECODE(QUESTION_ID, '-1253', TO_CHAR(COMMENT_NOTES))) CONDUCTING,
  ''RESEARCHFUNDED,
  MAX(DECODE(QUESTION_ID,'-1332',TO_CHAR(ANS_CHOICE_NAME))) THESIS,
  MAX(DECODE(QUESTION_ID,'-1172',TO_CHAR(ANS_CHOICE_NAME))) IRBREVIEW,
  MAX(DECODE(QUESTION_ID,'-1175',TO_CHAR(ANS_CHOICE_NAME))) US,
  ''HUMANSUBJECTS,
  MAX(DECODE(QUESTION_ID,'-1337',TO_CHAR(ANS_CHOICE_NAME))) HEALTHINFO,
  ''SUBJECT_DATA_YESNO1,
  MAX(DECODE(QUESTION_ID,'-1079',TO_CHAR(COMMENT_NOTES))) SUBJECTDATA_YESNO1_EXPLAIN,
  ''SUBJECT_DATAYESNO2,
  MAX(DECODE(QUESTION_ID,'-1232',TO_CHAR(COMMENT_NOTES))) SUBJECTDATA_YESNO2_EXPLAIN,
  MAX(DECODE(QUESTION_ID,'-1233',TO_CHAR(COMMENT_NOTES))) SUBJECTDATA_3,
  MAX(DECODE(QUESTION_ID,'-1173',TO_CHAR(COMMENT_NOTES))) EXTERNALIRB,
  MAX(DECODE(QUESTION_ID,'-1173',TO_CHAR(COMMENT_NOTES))) EXTERNALIRBSTATUS,
  MAX(DECODE(QUESTION_ID,'-1173',TO_CHAR(COMMENT_NOTES))) EXTERNALIRBAPPROVALTXT,
  ''EXTERNALIRBAPPROVALDOC,
  ''INTLPERMDOC,
  MAX(DECODE(QUESTION_ID,'-1176',TO_CHAR(COMMENT_NOTES))) INTLSTAFFKNOWLEDGE,
  MAX(DECODE(QUESTION_ID,'-1176',TO_CHAR(COMMENT_NOTES))) INTLCUSTOMS,
  MAX(DECODE(QUESTION_ID,'-1176',TO_CHAR(COMMENT_NOTES))) INTLRISK,
  MAX(DECODE(QUESTION_ID,'-1176',TO_CHAR(COMMENT_NOTES))) INTLCOMM,
  MAX(DECODE(QUESTION_ID,'-1176',TO_CHAR(COMMENT_NOTES))) INTLSTUDYCHANGE,
  MAX(DECODE(QUESTION_ID,'-1176',TO_CHAR(COMMENT_NOTES))) INTLTRAINING,
  MAX(DECODE(QUESTION_ID,'-1176',TO_CHAR(COMMENT_NOTES))) INTLCITI
FROM
  (SELECT IRB_SUB_LAST_ACTIVITY.ACTIVITY_TRACKER_ID,
    IRB_SUBMISSIONS.PROTOCOL_NUMBER,
    IRB_SUBMISSIONS.PROTOCOL_TITLE,
    SUB2.PROTOCOL_NUMBER AS PROTOCOL_NUMBER1,
    IRB_COMMENTS.COMMENT_NOTES,
    IRB_ANS_CHOICE_LIST.ANS_CHOICE_NAME,
    IRB_SUB_QUES_ANSWERS.QUESTION_ID
  FROM IRB_SUBMISSIONS
  FULL JOIN IRB_SUB_LAST_ACTIVITY
  ON IRB_SUB_LAST_ACTIVITY.SUBMISSION_ID = IRB_SUBMISSIONS.SUBMISSION_ID
  FULL JOIN IRB_SUBMISSIONS SUB2
  ON IRB_SUBMISSIONS.PARENT_SUBMISSION_ID = SUB2.SUBMISSION_ID
  FULL JOIN IRB_SUB_QUES_ANSWERS
  ON IRB_SUBMISSIONS.SUBMISSION_ID = IRB_SUB_QUES_ANSWERS.SUBMISSION_ID
  AND SUB2.SUBMISSION_ID           = IRB_SUB_QUES_ANSWERS.SUBMISSION_ID
  FULL JOIN IRB_ANS_CHOICE_LIST
  ON IRB_SUB_QUES_ANSWERS.ANS_CHOICE_ID = IRB_ANS_CHOICE_LIST.ANS_CHOICE_ID
  FULL JOIN IRB_COMMENTS
  ON IRB_SUB_QUES_ANSWERS.COMMENT_ID = IRB_COMMENTS.COMMENT_ID
  )
GROUP BY ACTIVITY_TRACKER_ID

When the query is run, I get the following error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4316, maximum: 4000)
22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
           the LOB size was bigger than the buffer limit for CHAR and RAW
           types.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Do one of the following
           1. Make the LOB smaller before performing the conversion,
           for example, by using SUBSTR on CLOB
           2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

Any help would be greatly appreciated.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries (query line: 74): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  2. Use Numeric Column Types For Numeric Values (query line: 8): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
The optimized query:
SELECT
        ACTIVITY_TRACKER_ID,
        MAX(DECODE(PROTOCOL_NUMBER,
        NULL,
        TO_CHAR(PROTOCOL_NUMBER1),
        TO_CHAR(PROTOCOL_NUMBER))) PROTOCOL_NUMBER,
        MAX(DECODE(QUESTION_ID,
        '1',
        TO_CHAR(COMMENT_NOTES))) RESEARCHPURPOSE,
        MAX(DECODE(QUESTION_ID,
        '-1060',
        TO_CHAR(COMMENT_NOTES))) NOOFSUBJECTS,
        MAX(DECODE(QUESTION_ID,
        '-1253',
        TO_CHAR(COMMENT_NOTES))) CONDUCTING,
        '' RESEARCHFUNDED,
        MAX(DECODE(QUESTION_ID,
        '-1332',
        TO_CHAR(ANS_CHOICE_NAME))) THESIS,
        MAX(DECODE(QUESTION_ID,
        '-1172',
        TO_CHAR(ANS_CHOICE_NAME))) IRBREVIEW,
        MAX(DECODE(QUESTION_ID,
        '-1175',
        TO_CHAR(ANS_CHOICE_NAME))) US,
        '' HUMANSUBJECTS,
        MAX(DECODE(QUESTION_ID,
        '-1337',
        TO_CHAR(ANS_CHOICE_NAME))) HEALTHINFO,
        '' SUBJECT_DATA_YESNO1,
        MAX(DECODE(QUESTION_ID,
        '-1079',
        TO_CHAR(COMMENT_NOTES))) SUBJECTDATA_YESNO1_EXPLAIN,
        '' SUBJECT_DATAYESNO2,
        MAX(DECODE(QUESTION_ID,
        '-1232',
        TO_CHAR(COMMENT_NOTES))) SUBJECTDATA_YESNO2_EXPLAIN,
        MAX(DECODE(QUESTION_ID,
        '-1233',
        TO_CHAR(COMMENT_NOTES))) SUBJECTDATA_3,
        MAX(DECODE(QUESTION_ID,
        '-1173',
        TO_CHAR(COMMENT_NOTES))) EXTERNALIRB,
        MAX(DECODE(QUESTION_ID,
        '-1173',
        TO_CHAR(COMMENT_NOTES))) EXTERNALIRBSTATUS,
        MAX(DECODE(QUESTION_ID,
        '-1173',
        TO_CHAR(COMMENT_NOTES))) EXTERNALIRBAPPROVALTXT,
        '' EXTERNALIRBAPPROVALDOC,
        '' INTLPERMDOC,
        MAX(DECODE(QUESTION_ID,
        '-1176',
        TO_CHAR(COMMENT_NOTES))) INTLSTAFFKNOWLEDGE,
        MAX(DECODE(QUESTION_ID,
        '-1176',
        TO_CHAR(COMMENT_NOTES))) INTLCUSTOMS,
        MAX(DECODE(QUESTION_ID,
        '-1176',
        TO_CHAR(COMMENT_NOTES))) INTLRISK,
        MAX(DECODE(QUESTION_ID,
        '-1176',
        TO_CHAR(COMMENT_NOTES))) INTLCOMM,
        MAX(DECODE(QUESTION_ID,
        '-1176',
        TO_CHAR(COMMENT_NOTES))) INTLSTUDYCHANGE,
        MAX(DECODE(QUESTION_ID,
        '-1176',
        TO_CHAR(COMMENT_NOTES))) INTLTRAINING,
        MAX(DECODE(QUESTION_ID,
        '-1176',
        TO_CHAR(COMMENT_NOTES))) INTLCITI 
    FROM
        (SELECT
            IRB_SUB_LAST_ACTIVITY.ACTIVITY_TRACKER_ID,
            IRB_SUBMISSIONS.PROTOCOL_NUMBER,
            IRB_SUBMISSIONS.PROTOCOL_TITLE,
            SUB2.PROTOCOL_NUMBER AS PROTOCOL_NUMBER1,
            IRB_COMMENTS.COMMENT_NOTES,
            IRB_ANS_CHOICE_LIST.ANS_CHOICE_NAME,
            IRB_SUB_QUES_ANSWERS.QUESTION_ID 
        FROM
            IRB_SUBMISSIONS FULL 
        JOIN
            IRB_SUB_LAST_ACTIVITY 
                ON IRB_SUB_LAST_ACTIVITY.SUBMISSION_ID = IRB_SUBMISSIONS.SUBMISSION_ID FULL 
        JOIN
            IRB_SUBMISSIONS SUB2 
                ON IRB_SUBMISSIONS.PARENT_SUBMISSION_ID = SUB2.SUBMISSION_ID FULL 
        JOIN
            IRB_SUB_QUES_ANSWERS 
                ON IRB_SUBMISSIONS.SUBMISSION_ID = IRB_SUB_QUES_ANSWERS.SUBMISSION_ID 
                AND SUB2.SUBMISSION_ID = IRB_SUB_QUES_ANSWERS.SUBMISSION_ID FULL 
        JOIN
            IRB_ANS_CHOICE_LIST 
                ON IRB_SUB_QUES_ANSWERS.ANS_CHOICE_ID = IRB_ANS_CHOICE_LIST.ANS_CHOICE_ID FULL 
        JOIN
            IRB_COMMENTS 
                ON IRB_SUB_QUES_ANSWERS.COMMENT_ID = IRB_COMMENTS.COMMENT_ID) 
    GROUP BY
        ACTIVITY_TRACKER_ID

Related Articles



* original question posted on StackOverflow here.