How we handle CLOB and BLOB Datatypes in HANA DB

[Former Member]

Dear HANA Gurus,

We have would like to build EDW using HANA base on our source system Oracle and it's supports CLOB and BLOB datatypes

Would you please suggest how do we handle in HANA DB.

Let not say it's oracle specific.

Regards,

Manoj

[Former Member]

Thanks Tomas

[yanan_zhao]

Hi Manoj,

How can you resolve it, I hit the same issue:

Could not execute 'SELECT "OriginalLanguageRef" FROM "SYSTEM"."ErrorReproduce_3" where TO_CLOB('k') = TO_CLOB('k')' in 5 ms 745 µs .

SAP DBTech JDBC: [266] (at 70): inconsistent datatype: lob type comparison: line 3 col 8 (at pos 70)

Thanks

Yanan

[Former Member]

Hi Yanan,

I checked with support and they informed me that comparison on LOB column is not allowed, the documentation is not correct and said would be fixed for SPS6.

Also I was told that in the where clause only LIKE operator is allowed to compare LOB columns.

Thanks!

-Aiysha

[yanan_zhao]

Hi Aiysha,

Thank you very much!

[tomas-krojzl]

Hello,

check SAP HANA SQL Reference Guide for list of data types:

(page 14 - Classification of Data Types)

https://service.sap.com/~sapidb/011000358700000604922011

For this purpose might be useful following data types:

Large Object (LOB) Types
LOB (large objects) data types, CLOB, NCLOB and BLOB, are used to store a large amount of data such as text documents and images. The maximum size of an LOB is 2 GB.
BLOB
The BLOB data type is used to store large binary data.
CLOB
The CLOB data type is used to store large ASCII character data.
NCLOB
The NCLOB data type is used to store a large Unicode character object.

Tomas

[tomas-krojzl]

Hello,

be sure to read the guide to understand the restrictions of work with LOBs in HANA

(I am not going to paste them here as I would be copying whole guide into the forums)

Tomas

[Former Member]

Hi Thomas,

A quick follow up about lob datatypes. I looked though the documentation for the restrictions of lob datatypes. According to the documentation I should have no issues running a simple select statement that compared lob datatypes using '=', for example

select TO_CLOB('1') from dummy where TO_CLOB('1')=TO_CLOB('1');

However, I get an error

Could not execute 'select TO_CLOB('1') from dummy where TO_CLOB('1')=TO_CLOB('1')' in 3 ms 965 µs .

SAP DBTech JDBC: [266] (at 37): inconsistent datatype: lob type comparison: line 1 col 38 (at pos 37)

According to the documentation : LOB columns cannot appear in WHERE clauses as a predicate except LIKE, CONTAINS, =, or <>.

Am I missing something?

Thanks!