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 from VARCHAR2, NVARCHAR2, or NCLOB to a CLOB

• TO_NCLOB(): Converts from VARCHAR2, NVARCHAR2, or CLOB to an NCLOB

• TO_BLOB(varchar|clob, destcsid,[mime_type]): Converts the object from its current character set to the given character set in destcsid. The resultant object is BLOB. 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 parameter mime_type is applicable only to INSERT and UPDATE statements on Secure File LOB columns. If the mime_type parameter is used in SELECT statements or in temporary or BasicFile LOBs, then it is ignored.

• TO_BLOB(varchar): Converts the input to RAW before converting to BLOB. In other words, TO_BLOB(HEXTORAW(varchar)) and TO_BLOB(varchar) are equivalent.

  Note:

  TO_BLOB(CLOB) is not supported.

• TO_CHAR(): Converts a CLOB to a CHAR 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 an NCLOB to an NCHAR 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 use CAST to convert a CLOB value into a character data type, an NCLOB value into a national character data type, or a BLOB value into a RAW 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.