6.2 Explicit Data Type Conversion Functions
This section describes the explicit conversion functions in SQL and PL/SQL to convert other data types to and from CLOB
, NCLOB
, and BLOB
data types.
-
TO_CLOB()
: Converts fromVARCHAR2
,NVARCHAR2
, orNCLOB
to aCLOB
-
TO_NCLOB()
: Converts fromVARCHAR2
,NVARCHAR2
, orCLOB
to anNCLOB
-
TO_BLOB(varchar|clob, destcsid,[mime_type])
: Converts the object from its current character set to the given character set indestcsid
. The resultant object isBLOB
. Following are various ways in which you can use the conversion function:TO_BLOB(character, destcsid)
TO_BLOB(character, destcsid, mime_type)
TO_BLOB(clob, destcsid)
TO_BLOB(clob, destcsid, mime_type)
If the
destcsid
is 0, then it converts to the database character set ID. The parametermime_type
is applicable only toINSERT
andUPDATE
statements on Secure FileLOB
columns. If themime_type
parameter is used inSELECT
statements or in temporary or BasicFileLOB
s, then it is ignored. TO_BLOB(varchar)
: Converts the input toRAW
before converting toBLOB
. In other words,TO_BLOB(HEXTORAW(varchar))
andTO_BLOB(varchar)
are equivalent.Note:
TO_BLOB(CLOB)
is not supported.-
TO_CHAR()
: Converts aCLOB
to aCHAR
type. When you use this function to convert a character LOB into the database character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit. -
TO_NCHAR()
: Converts anNCLOB
to anNCHAR
type. When you use this function to convert a character LOB into the national character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit. -
CAST
does not directly support any of the LOB data types. When you useCAST
to convert aCLOB
value into a character data type, anNCLOB
value into a national character data type, or aBLOB
value into aRAW
data type, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target data type. If the resulting value is larger than the target type, then the database returns an error.