How to visualize CLOB content - Oracle

Question

I want to select and visualize the content of a column with CLOB datatype (>100 bytes).

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(ds_cirurgia,1,4000))
  from AVISO_CIRURGIA
 where cd_paciente = 123456789;

But I get this error:

[SELECT - 0 row(s), 0.000 secs]  [Error Code: 997, SQL State: 42000]  ORA-00997: illegal use of LONG datatype

I used UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR()) in another column and it works.

What is wrong in this case?

please post the result of select data_type from user_tab_columns where table_name = 'AVISO_CIRURGIA' and column_name = upper('ds_cirurgia'); — Marmite Bomber, Jul 21 '15 at 15:38
Hi @MarmiteBomber. Thanks for you help. I ran the query you suggested but it seems my user_tab_columns is empty. When I execute a simple select, like: "select ds_cirurgia from AVISO_CIRURGIA ", it shows me each line as "CLOB, size in bytes", for exemplo: "CLOB,202 Bytes" in the first line, "CLOB,482 Bytes" in the second line.... — Henrique Amaral, Jul 21 '15 at 16:34
Sorry for the last comment. Now it works using: select * from ALL_TAB_COLUMNS where table_name = 'AVISO_CIRURGIA' and column_name = upper('ds_cirurgia'); TABLE_NAME: AVISO_CIRURGIA COLUMN_NAME: DS_CIRURGIA DATA_TYPE: LONG — Henrique Amaral, Jul 21 '15 at 16:39
@Henrique Amaral what you get as a result of this query? Check also the swapped parameter in DBMS_LOB.SUBSTR(col_name,**1,4000**) - should be **4000,1** — Marmite Bomber, Jul 21 '15 at 16:47

score 0 · Answer 1 · answered Jul 21 '15 at 14:26

0

You can just leave it with the call to DBMS_LOB.SUBSTR: it returns a varchar2:

select DBMS_LOB.SUBSTR(ds_cirurgia,1,4000)
from   AVISO_CIRURGIA
where  cd_paciente = 123456789

However, keep in mind that Oracle SQL is only capable of having a varchar2 with 4000 bytes, not 4000 characters. Your call can fail if the string contains Unicode characters.

answered Jul 21 '15 at 14:26

Patrick Hofman

153,850
22
249
325

Why on earth do you edit in your own version of the truth. Let OP edit his question if you are right, but I don't thnk you are. – Patrick Hofman Jul 21 '15 at 15:10

Marmite Bomber · Answer 2 · 2015-07-21T15:00:02.260

Your column type is LONG not CLOB. Simple look up it in USER_TAB_COLUMNS.

Here are some workaround how to resolve it. I'd recoment to change the type to CLOB with CTAS.

create table t1 as
select ...
to_lob(c) c  /* convert to CLOB */
from t;

After that you can simple cast to VARCHAR such as

 cast (substr(col_name,1,4000) as varchar2(4000))

UPDATE

of course you may also use DBMS_LOB.SUBSTR

 DBMS_LOB.SUBSTR(col_name,4000,1)

but please note the signature of this function: 2nd parameter is length, 3rd offset (not vice versa as in substr).

How to visualize CLOB content - Oracle

2 Answers2