How can data from a CLOB (text) field in Oracle be converted to varchar2 and delivered in a build? When reading directly from the CLOB field, the following error is returned.
ORA-00932: inconsistent datatypes: expected - got <datatype>
For CLOB:
Use the DBMS_LOB package to convert CLOB columns in datasource SQL.Verify that the DBMS_LOB package is installed on the Oracle server.
In the SELECT statement use the DBMS_LOB package functions to retrieve a specific number of characters (in this example, from <your_clob_column>, start at character 1 and bring 100 characters):
select dbms_lob.substr(<your_clob_column>,100,1) from <your_table>
For BLOB:
The Oracle dbms_lob.substr package function can be used for BLOB columns.
When transforming BLOB data type to varchar2, it's important to ensure that there is convertible text in those BLOBs. Conversion of pure binary data (which BLOBs sre intended for) is pointless.
If the BLOB text length doesn't exceed the character limit for varchar2 you could use Oracle packaged function:
UTL_RAW.CAST_TO_VARCHAR2
To reduce a BLOB to a manageable size for varchar2, use DBMS_LOB.SUBSTR as for a CLOB, and then transform the resulting RAW to varchar2 using
UTL_RAW.CAST_TO_VARCHAR2
The expression is:
utl_raw.cast_to_varchar2(dbms_lob.substr(<your_blob_column>,100,1 )) from <your_table>
[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Install and Config","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF033","label":"Windows"}],"Version":"1.1;7.1;8.1;8.2","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Framework Manager","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Install and Config","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Query Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Report Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SUNSET","label":"PRODUCT REMOVED"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"ReportNet","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSTQPQ","label":"IBM Cognos Series 7 PowerPlay"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"DecisionStream","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]