How to convert CLOB to CHAR datatype in Oracle?

1
votes

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"

1
sqloracle
Please clarify your question. First, are you sure you want to convert to CHAR and not to VARCHAR2? Are all your values the same length? And then, what do you mean by "it should not be limited to the length of characters..." - the ONLY data type that does NOT have a length limit is CLOB, that's the only reason it was introduced in the first place. – mathguy
I will edit my answer. Thanks for your suggestion – Vignesh Kiswanth
Also: If you have tried DBMS.LOBS_SUBSTR, that should give you a different error (such as, there is no such function). If you find it difficult to copy what GurV wrote, use "copy" and "paste" editing. If in fact you used the correct function, DBMS_LOB.SUBSTR and you got an error, it is definitely not due to nulls in the column; those would work perfectly fine. – mathguy
Please check the error, in the description. If null is not the reason why I am getting these error. – Vignesh Kiswanth
@Vignesh please post the full code which you are using. – Gurwinder Singh

1 Answers

1
votes

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