BFILE Data Type

Description

Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. A BFILE column or attribute stores a BFILE locator, which serves as a pointer to a binary file on the server file system. The locator maintains the directory name and the filename. (Oracle SQL Language Reference BFILE Data Type).

BFILE Data Type is not supported in Snowflake. VARCHAR is used instead.

Sample Source Patterns

Bfile data type in Create Table

Oracle BFILE columns are used to store a locator with the directory and filename. They are changed to Snowflake VARCHAR in order to store the directory and filename into the column. However, loading the content of the file must be done manually.

Oracle

Query

IN -> Oracle_01.sql

--Create Table
CREATE TABLE bfile_table 
(
    col1 BFILE 
);

--Insert Bfilename
INSERT INTO bfile_table VALUES (
    BFILENAME('mydirectory', 'myfile.png')
);

--Select
SELECT * FROM bfile_table;

Result

COL1              |
------------------+
[BFILE:myfile.png]|

Snowflake

Query

OUT -> Oracle_01.sql

--Create Table
CREATE OR REPLACE TABLE bfile_table
    (
        col1
    !!!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"}}'
;

--Insert Bfilename
INSERT INTO bfile_table
VALUES (PUBLIC.BFILENAME_UDF('mydirectory', 'myfile.png')
);

--Select
SELECT * FROM
    bfile_table;

Result

COL1                  |
----------------------+
mydirectory\myfile.png|

UDF added to replace BFILENAME().

UDF Added

CREATE OR REPLACE FUNCTION PUBLIC.BFILENAME_UDF (DIRECTORYNAME STRING, FILENAME STRING)
RETURNS STRING
LANGUAGE SQL
IMMUTABLE 
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DIRECTORYNAME || '\\' || FILENAME
$$;

Known Issues

1. 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-OR0105: Additional work is needed for BFILE column usage. BUILD_STAGE_URL function is a recommended workaround.