SSC-FDM-OR0015

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

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

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 [email protected]