SSC-FDM-OR0015

LENGTHB transformed to OCTET_LENGTH results may vary due to memory management of DBMS.

Description

This issue happens when there is an invocation to LENGTHB function that returns the size of a column or literal in bytes. This function is transformed into OCTET_LENGTH Snowflake's function.

When the parameter to the function is a column, the result will be the size of the value that the column has, this size may vary from Oracle to Snowflake, the type of the column plays an important role in the result returned by the function.

Example Code

Input Code:

Queries

IN -> Oracle_01.sql

CREATE TABLE char_table
(
	char_column1 CHAR(15)
);

INSERT INTO char_table VALUES ('Hello world');

SELECT char_column1, LENGTHB(char_column1), LENGTH('Hello world') FROM char_table;

Result

|CHAR_COLUMN1   |LENGTHB(CHAR_COLUMN1)|LENGTH('HELLOWORLD')|
|---------------|---------------------|--------------------|
|Hello world    |15                   |11                  |

Output Code:

Queries

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE char_table
(
	char_column1 CHAR(15)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO char_table
VALUES ('Hello world');

SELECT char_column1,
OCTET_LENGTH(char_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/, LENGTH('Hello world') FROM
char_table;

Result

|CHAR_COLUMN1|OCTET_LENGTH(CHAR_COLUMN1)|LENGTH('HELLO WORLD')|
|------------|--------------------------|---------------------|
|Hello world |11                        |11                   |

Recommendations

• Manually check the data types used.

• Check the encoding of the columns used because OCTET_LENGTH can return bigger sizes when the string contains Unicode code points.

• If you need more support, you can email us at [e-mail]