BFILENAME UDF

Description

This function takes the directory name and the file name parameters of the Oracle BFILENAME() as STRING and returns a concatenation of them using \. Since BFILE is translated to VARCHAR, the BFILENAME result is handled as text.

The \ must be changed to match the corresponding operating system file concatenation character.

Custom UDF overloads

BFILENAME_UDF(string, string)

It concatenates the directory path and the file name.

Parameters

1. DIRECTORYNAME: A STRING that represents the directory path.

2. FILENAME: A STRING that represents the file name.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.B_FILE_NAME_UDF (DIRECTORYNAME STRING, FILENAME STRING)
RETURNS STRING
LANGUAGE SQL
IMMUTABLE 
AS
$$
	DIRECTORYNAME || '\\' || FILENAME
$$;

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 bfile_table ( col1
--** MSC-WARNING - MSCEWI3105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND **
VARCHAR /*** MSC-WARNING - MSCEWI1036 - BFILE DATA TYPE CONVERTED TO VARCHAR ***/
);

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

--Select
SELECT * FROM
bfile_table;

Result

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

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. MSCEWI1020: CUSTOM UDF INSERTED.

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