[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;

[Oracle]BLOB_TO_CLOB

  FUNCTION BLOB_TO_CLOB(P_DATA IN BLOB) RETURN CLOB AS
    L_CLOB         CLOB;
    L_DEST_OFFSET  PLS_INTEGER := 1;
    L_SRC_OFFSET   PLS_INTEGER := 1;
    L_LANG_CONTEXT PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    L_WARNING      PLS_INTEGER;
  BEGIN
  
    DBMS_LOB.CREATETEMPORARY(LOB_LOC => L_CLOB, CACHE => TRUE);
  
    DBMS_LOB.CONVERTTOCLOB(DEST_LOB     => L_CLOB,
                           SRC_BLOB     => P_DATA,
                           AMOUNT       => DBMS_LOB.LOBMAXSIZE,
                           DEST_OFFSET  => L_DEST_OFFSET,
                           SRC_OFFSET   => L_SRC_OFFSET,
                           BLOB_CSID    => DBMS_LOB.DEFAULT_CSID,
                           LANG_CONTEXT => L_LANG_CONTEXT,
                           WARNING      => L_WARNING);
  
    RETURN L_CLOB;
  END;

[Oracle]CLOB_TO_BLOB

  
  FUNCTION CLOB_TO_BLOB(P_DATA IN CLOB) RETURN BLOB AS
    L_BLOB         BLOB;
    L_DEST_OFFSET  PLS_INTEGER := 1;
    L_SRC_OFFSET   PLS_INTEGER := 1;
    L_LANG_CONTEXT PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    L_WARNING      PLS_INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
    B              BLOB;
    C              BLOB;
  BEGIN
  
    DBMS_LOB.CREATETEMPORARY(LOB_LOC => L_BLOB, CACHE => TRUE);
  
    DBMS_LOB.CONVERTTOBLOB(DEST_LOB     => L_BLOB,
                           SRC_CLOB     => P_DATA,
                           AMOUNT       => DBMS_LOB.LOBMAXSIZE,
                           DEST_OFFSET  => L_DEST_OFFSET,
                           SRC_OFFSET   => L_SRC_OFFSET,
                           BLOB_CSID    => DBMS_LOB.DEFAULT_CSID,
                           LANG_CONTEXT => L_LANG_CONTEXT,
                           WARNING      => L_WARNING);
  
    --RETURN L_BLOB;
    BEGIN
      SELECT 'EFBBBF' INTO B FROM DUAL;
      DBMS_LOB.CREATETEMPORARY(C, TRUE);
      DBMS_LOB.APPEND(C, B);
      DBMS_LOB.APPEND(C, L_BLOB);
    END;
    RETURN C;
  END;