MSCEWI3121

Using DBMS_LOB.SUBSTR built-in package with a BFILE column is not supported in Snowflake

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

Oracle BFILE columns are migrated to VARCHAR in Snowflake. The file name is stored as a string in the new column. Therefore, using a SUBSTR function, in Snowflake, on the migrated column will return a substring of the file name. While Oracle DBMS_LOB.SUBSTR will return a substring of the file content. For more information review BFILE data type.

Example Code

Input Code:

-- Create Table
CREATE TABLE table_bfile
(
    bfile_column BFILE
);

-- SUBSTR on BFILE column
SELECT 
DBMS_LOB.SUBSTR(bfile_column, 15, 1)
FROM bfile_column;

Output Code:

-- Create Table
CREATE OR REPLACE TABLE PUBLIC.table_bfile (
bfile_column VARCHAR /*** MSC-WARNING - MSCEWI1036 - BFILE DATA TYPE CONVERTED TO VARCHAR ***/
/*** MSC-WARNING - MSCEWI3105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/
);

-- SUBSTR on BFILE column
SELECT
/*** MSC-ERROR - MSCEWI3121 - USING DBMS_LOB.SUBSTR ON BFILE SOURCE COLUMN IS NOT SUPPORTED ON SNOWFLAKE ***/
SUBSTR(bfile_column, 1, 15)
FROM
PUBLIC.table_bfile;

Recommendations

• Review the documentation for handling files with Snowflake here.

• If you need more support, you can email us at [email protected]