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

--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

--Create Table
CREATE OR REPLACE TABLE PUBLIC.bfile_table ( col1 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 ***/);

--Insert Bfilename
INSERT INTO PUBLIC.bfile_table VALUES (/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'BFILENAME_UDF' INSERTED. ***/
PUBLIC.BFILENAME_UDF('mydirectory', 'myfile.png') );

--Select
SELECT * FROM PUBLIC.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 
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 not an equivalent for the DBMS_LOB functions and there are no implemented workarounds yet.

Related EWIs

1. MSCEWI1036: Data type converted to another data type.

2. MSCEWI3105: Additional Work Is Needed For BFILE Column Usage.