BLOB Data Type

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

The BLOB data type stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. (Oracle SQL Language Reference BLOB Data Type).

BLOB Data Type is not supported in Snowflake. BINARY is used instead.

Sample Source Patterns

BLOB in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE blobtable( blob_column BLOB, empty_column BLOB );

INSERT INTO blobtable VALUES (NULL, EMPTY_BLOB());

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE blobtable ( blob_column BINARY,
empty_column BINARY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO blobtable
VALUES (NULL, TO_BINARY(' '));

Retrieving Data

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM blobtable;

Result

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
blobtable;

Result

Functional Example

This example is not a translation of SnowConvert, it is only used to show the functional equivalence between Oracle BLOB and Snowflake BINARY

We are using "utl_raw.cast_to_raw" and "DBMS_LOB.SUBSTR" functions. The conversion for these functions is currently not supported by SnowConvert.

Oracle

Query

IN -> Oracle_03.sql

INSERT INTO blobtable VALUES(
utl_raw.cast_to_raw('hello world'), EMPTY_BLOB());

SELECT DBMS_LOB.SUBSTR(blob_column) AS result
FROM blobtable;

Result

Snowflake

Query

OUT -> Oracle_03.sql

INSERT INTO blobtable
VALUES(
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'utl_raw.cast_to_raw' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS cast_to_raw, TO_BINARY(' '));


SELECT
SUBSTR(blob_column, 1) AS result
FROM
blobtable;

Result

Known Issues

1. The difference in max length BLOB (Oracle) and BINARY (Snowflake)

An Oracle BLOB column's maximum size is (4 gigabytes - 1) * (database block size), but Snowflake BINARY is limited to 8MB.

2. Empty value with EMPTY_BLOB

Initializing a column using EMPTY_BLOB() will return an empty LOB locator. While after translation the column will return a string with ' '.

3. No access to the DBMS_LOB built-in package

Since LOB data types are not supported in Snowflake there is no equivalent for the DBMS_LOB functions and there are no implemented workarounds yet.

Related EWIs

1. SSC-EWI-OR0076: Built In Package Not Supported.