SSC-EWI-OR0121

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

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:

IN -> Oracle_01.sql

CREATE TABLE table1
(
    bfile_column BFILE
)
SELECT 
DBMS_LOB.SUBSTR(bfile_column, 15, 1)
FROM table1;

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE table1
    (
        bfile_column
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/!!!
    VARCHAR
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;
    SELECT
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0121 - USING DBMS_LOB.SUBSTR ON BFILE SOURCE COLUMN IS NOT SUPPORTED ON SNOWFLAKE ***/!!!
    SUBSTR(bfile_column, 1, 15)
    FROM
    table1;

Recommendations

• Review the documentation for handling files with Snowflake here.

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