Oracle Column With Long Value

oracle

Scenario 1:

SELECT XMLAGG (XMLELEMENT (E, CODE, ',')).EXTRACT ('//text()').getClobVal() CODE
  FROM CM_REFCODES
 WHERE     TO_DATE ('07/22/2020', 'MM/DD/YYYY') BETWEEN STARTDATE
                                                    AND NVL (
                                                            ENDDATE,
                                                            TO_DATE (
                                                                '12/31/2999',
                                                                'MM/DD/YYYY'))
       AND DOCTABLE = 'J_APX_A_8.2e'

In the above code, the CM_REFCODES has a column named CODE. The value in this column is not too long. However, this contains 1758 rows, and on the Java side, we looped over the result set, and do a lot of string concatenation, and the performance was poor. Therefore, we changed the original SQL statement to using XMLAGG (instead of LISTAGG), and XMLELEMENT, and getClobVal(). We have to do all this so that Oracle does not error, and to deal with an issue where rs.getString("CODE") returned null.

Scenario 2:

DECLARE
     v_SQL_SELECT_STUB_CLOB            CLOB;
     v_SQL_EXPORT_SELECT_STUB        CLOB;
BEGIN
  v_SQL_SELECT_STUB_CLOB := q'{}';

    UPDATE report.qreport_output_sql
       SET SQL_SELECT_STUB = '',
           SQL_WHERE_STUB = '',
           SQL_GROUP_BY_STUB = '',
           SQL_ORDER_BY_STUB = '',
           SQL_MISSING_STUB = '',
           SQL_TBL_STUB = '',
           SQL_SELECT_STUB_CLOB = V_SQL_SELECT_STUB_CLOB,
           SQL_EXPORT_SELECT_STUB = ''
    WHERE QREPORT_OUTPUT_SQL_ID = 30500 AND QREPORTS_ID = 31621
    ;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Update Done.');
EXCEPTION
  WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

In this scenario, I had to update a BLOG / CLOB column. The MERGE statement failed. To get around this issue with the MERGE statement, I used the above code. In the above code, we use the custom quote operator q'{}', and inside the curley braces, we can put arbitrary amount of text. So the trick is to use PLSQL variable.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License