Oracle: Convert CLOB to varchar

Had problem to read oracle table with column declared as CLOB if it contained more than 4000 byte of data.
Solved it by devide the column into smaller parts, with following select statement;


Select NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,1)),'') AS CLOB_COL_Part1,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,3001)),'') AS CLOB_COL_Part2,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,6001)),'') AS CLOB_COL_Part3,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,9001)),'') AS CLOB_COL_Part4,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,12001)),'') AS CLOB_COL_Part5,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,15001)),'') AS CLOB_COL_Part6,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,18001)),'') AS CLOB_COL_Part7,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,21001)),'') AS CLOB_COL_Part8,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,24001)),'') AS CLOB_COL_Part9,
      NVL(TO_CHAR(DBMS_LOB.SUBSTR(CLOB_COL,3000,27001)),'') AS CLOB_COL_Part10

       DBMS_LOB.getlength(CLOB_COL) AS CLOB_COL_LENGTH,

from DATABASE_TABLE


I use this to copy the data into an Excel file, and there the data can be concatenated again.

Convert $#@ to ÅÄÖ and }{| to åäö

If swedish characters are saved in strange format in DB2, propably due to wrong ebcdic/asscii conversion, use DB2 command "Translate" as in below example:


Select USERID, ORG_UNIT, COMPANY,
       Translate(GIVEN_NAME,'åäöÅÄÖé', '}{¦$#@`') AS GIVEN_NAME,
       Translate(MIDDLE_NAME,'åäöÅÄÖé', '}{¦$#@`') AS MIDDLE_NAME,
       Translate(SURNAME,'åäöÅÄÖé', '}{¦$#@`') AS SURNAME,
       EMPL_NO, EMPL_START, EMPL_END, EMPL_TYPE, COST_CENTER, DEPARTMENT, OFFICE,
       Translate(CITY,'åäöÅÄÖé', '}{¦$#@`') AS CITY,
       Translate(STATE,'åäöÅÄÖé', '}{¦$#@`') AS STATE,
       TELE_NO, MOBILE,
       Translate(CONS_COMPANY,'åäöÅÄÖé', '}{¦$#@`') AS CONS_COMPANY,
       EXT_UID
from DB2TableName
whereTranslate(CITY,'åäöÅÄÖé', '}{¦$#@`') = 'Göteborg'