[Oracle]讀取Clob欄位資料

方法一

SELECT DBMS_LOB.SUBSTR(欄位, 讀取長度,第幾個開始)

SELECT DBMS_LOB.SUBSTR(A.ITEM133, 2000)

方法二

這就比較複雜一點，因為背後其實轉成varchar2，所以會有長度限制的問題

DECLARE
  V_CLOB       CLOB;
  V_CLOB_LEN   NUMBER;
  V_OFFSET     NUMBER := 1;
  V_CHUNK_SIZE NUMBER := 32767;
  V_CLOB_CHUNK VARCHAR2(32767);

  CURSOR C_CLOB IS
    SELECT --HP.PARTY_NAME,
    --HCA.ACCOUNT_NUMBER,
    --D.DESCRIPTION,
    --AD.PK1_VALUE,
     DLT.LONG_TEXT
    --INTO CLOB_COLUMN
      FROM FND_DOCUMENTS_VL           D,
           FND_ATTACHED_DOCUMENTS     AD,
           FND_DOCUMENT_ENTITIES      E,
           FND_USER                   U,
           FND_DOCUMENT_CATEGORIES_TL CL,
           FND_DM_NODES               NODE,
           FND_DOCUMENTS_LONG_TEXT    DLT,
           HZ_PARTIES                 HP,
           HZ_CUST_ACCOUNTS_ALL       HCA
     WHERE AD.DOCUMENT_ID = D.DOCUMENT_ID
       AND AD.ENTITY_NAME = E.DATA_OBJECT_CODE(+)
       AND AD.LAST_UPDATED_BY = U.USER_ID(+)
       AND CL.LANGUAGE = USERENV('LANG')
       AND AD.ENTITY_NAME = 'AR_CUSTOMERS'
       AND HP.PARTY_ID = HCA.PARTY_ID
       AND HCA.CUST_ACCOUNT_ID = PK1_VALUE
       AND PK1_VALUE = '492970' --CUST_ACCOUNT_ID
       AND CL.CATEGORY_ID = NVL(AD.CATEGORY_ID, D.CATEGORY_ID)
       AND D.DM_NODE = NODE.NODE_ID(+)
       AND D.MEDIA_ID = DLT.MEDIA_ID
       AND EXISTS (SELECT 1
              FROM HZ_CUST_ACCT_SITES_ALL AA
             WHERE AA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
               AND AA.ORG_ID = 136);
BEGIN
  FOR R_CLOB IN C_CLOB LOOP
    V_CLOB     := R_CLOB.LONG_TEXT;
    V_CLOB_LEN := DBMS_LOB.GETLENGTH(V_CLOB);
  
    WHILE V_OFFSET <= V_CLOB_LEN LOOP
      DBMS_LOB.READ(V_CLOB, V_CHUNK_SIZE, V_OFFSET, V_CLOB_CHUNK);
      DBMS_OUTPUT.PUT_LINE(V_CLOB_CHUNK);
      V_OFFSET := V_OFFSET + V_CHUNK_SIZE;
    END LOOP;
    V_OFFSET := 1;
  END LOOP;
END;