DBMS_LOB.substr returns null

Question

v_tmp_clob := DBMS_LOB.substr(v_qry_strng, dbms_lob.getlength(v_qry_strng)-10,1);

the previous line returns null though v_qry_strng is a CLOB of length 32897 characters ... can any one explain ?

Answer 1

You haven't specified Oracle version.

DBMS_LOB.SUBSTR returns a VARCHAR2 (among others, but not CLOB), which can hold at most 32767 bytes worth of characters.

Also, DBMS_LOB.SUBSTR returns NULL on the following conditions:

1. Any parameter is explicitly NULL
2. Param 2 (amount) < 1
3. Param 2 (amount) > 32767
4. Param 3 (offset) < 1
5. Param 3 (offset) > LOBMAXSIZE

In your expression, you have: dbms_lob.getlength(v_qry_strng)-10 --> 32897-10 --> 32887 as param 2.

32887 > 32767 <-- This is why.

---

Update:

To copy from LOB to LOB directly, use DBMS_LOB.COPY.

In your example, it could be like:

DBMS_LOB.COPY(v_tmp_clob, v_qry_strng, DBMS_LOB.GETLENGTH(v_qry_strng) - 10, 1, 1);