CODEWITHSUNDEEP
sqloracle-database
Vignesh Kiswanth
Vignesh Kiswanth

Reputation: 101

How to convert CLOB to CHAR datatype in Oracle?

For ETL Purpose, I need to convert DB records into flat-files. For that, I have to convert all the records as CHAR. In that table, it's having few columns with CLOB datatype. So, I tried the following functions: 

DBMS.LOBS_SUBSTR(column_name, Length(column_name))

Error: ORA-06502 Pl/sql: numeric value error: character string buffer too small ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error ℅s"

Upvotes: 2

Views: 39178

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

You can use DBMS_LOB.substr() 

select DBMS_LOB.substr(col, 4000) from table;

Also, from the docs, restrictions are as:

For fixed-width n-byte CLOBs, if the input amount for SUBSTR is greater than (32767/n), then SUBSTR returns a character buffer of length (32767/n), or the length of the CLOB, whichever is lesser. For CLOBs in a varying-width character set, n is the maximum byte-width used for characters in the CLOB

Upvotes: 2

Related Questions