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.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
$$;

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|

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. SSC-EWI-OR0105: Additional Work Is Needed For BFILE Column Usage.