Reputation: 101
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: 39131
Reputation: 39527
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