How to get the length of a CLOB

View All Scripts Login to Run Script

Script Name How to get the length of a CLOB
Description This script shows the ways you can get the length of a column of type CLOB. The Oracle 21c documentation about LENGTH is here - https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/LENGTH.html#GUID-8F97F652-5AE8-4457-AFD7-7A6F25551E0C and that for DBMS_LOB.GETLENGTH is here https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_LOB.html#GUID-FCA53D7B-8F81-4D70-A680-E39A75F80BDE
Area SQL General
Contributor Oracle
Created Thursday July 22, 2021

Statement 1
There are a few CLOB columns in the supplied, select-only schemas that Live SQL provides. None contain much data but the query shows the one we will use for this example.
Find a CLOB column
```
select data_type   
  from all_tab_columns  
 where owner = 'DD'  
   and table_name = 'DD_MEMBERS'  
   and column_name = 'ABOUT_YOURSELF'
```
```
DATA_TYPE
CLOB
```

DATA_TYPE
CLOB

Statement 2

For a CLOB column, you can use the standard LENGTH or use the DBMS_LOB.GETLENGTH (don't ask me why the function is named differently). These return the length in bytes or characters. For more information about multi-byte length or length of BLOBs, check out the links to the Oracle doc above.

Find the length

select length(about_yourself), dbms_lob.getlength(about_yourself)  
  from dd.dd_members


LENGTH(ABOUT_YOURSELF) DBMS_LOB.GETLENGTH(ABOUT_YOURSELF)
266 266
152 152
184 184
176 176
148 148
201 201
358 358
203 203
291 291
208 208
229 229
173 173
228 228
77 77
240 240
169 169
96 96
171 171
135 135
135 135
122 122
132 132
175 175
100 100
178 178
198 198
143 143
157 157
160 160
206 206
426 426
628 628
313 313
488 488
368 368
250 250
342 342
671 671
496 496
475 475
565 565
597 597
168 168
23 23
269 269
248 248
266 266
175 175
352 352
257 257

Rows 1 - 50. More rows exist.

LENGTH(ABOUT_YOURSELF)	DBMS_LOB.GETLENGTH(ABOUT_YOURSELF)
266	266
152	152
184	184
176	176
148	148
201	201
358	358
203	203
291	291
208	208
229	229
173	173
228	228
77	77
240	240
169	169
96	96
171	171
135	135
135	135
122	122
132	132
175	175
100	100
178	178
198	198
143	143
157	157
160	160
206	206
426	426
628	628
313	313
488	488
368	368
250	250
342	342
671	671
496	496
475	475
565	565
597	597
168	168
23	23
269	269
248	248
266	266
175	175
352	352
257	257

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

LENGTH(ABOUT_YOURSELF)	DBMS_LOB.GETLENGTH(ABOUT_YOURSELF)
266	266
152	152
184	184
176	176
148	148
201	201
358	358
203	203
291	291
208	208
229	229
173	173
228	228
77	77
240	240
169	169
96	96
171	171
135	135
135	135
122	122
132	132
175	175
100	100
178	178
198	198
143	143
157	157
160	160
206	206
426	426
628	628
313	313
488	488
368	368
250	250
342	342
671	671
496	496
475	475
565	565
597	597
168	168
23	23
269	269
248	248
266	266
175	175
352	352
257	257

LENGTH(ABOUT_YOURSELF)	DBMS_LOB.GETLENGTH(ABOUT_YOURSELF)
266	266
152	152
184	184
176	176
148	148
201	201
358	358
203	203
291	291
208	208
229	229
173	173
228	228
77	77
240	240
169	169
96	96
171	171
135	135
135	135
122	122
132	132
175	175
100	100
178	178
198	198
143	143
157	157
160	160
206	206
426	426
628	628
313	313
488	488
368	368
250	250
342	342
671	671
496	496
475	475
565	565
597	597
168	168
23	23
269	269
248	248
266	266
175	175
352	352
257	257

LENGTH(ABOUT_YOURSELF)	DBMS_LOB.GETLENGTH(ABOUT_YOURSELF)
266	266
152	152
184	184
176	176
148	148
201	201
358	358
203	203
291	291
208	208
229	229
173	173
228	228
77	77
240	240
169	169
96	96
171	171
135	135
135	135
122	122
132	132
175	175
100	100
178	178
198	198
143	143
157	157
160	160
206	206
426	426
628	628
313	313
488	488
368	368
250	250
342	342
671	671
496	496
475	475
565	565
597	597
168	168
23	23
269	269
248	248
266	266
175	175
352	352
257	257