SUBSTR Function
Translation reference for DBMS_RANDOM.SUBSTR.
Description
This function returns
amount
bytes or characters of a LOB, starting from an absoluteoffset
from the beginning of the LOB. (Oracle PL/SQL DBMS_LOB.SUBSTR)
This built-in function is replaced with Snowflake SUBSTR function. However, there are some differences.
Syntax
DBMS_LOB.SUBSTR (
lob_loc IN BLOB,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN RAW;
DBMS_LOB.SUBSTR (
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;
DBMS_LOB.SUBSTR (
file_loc IN BFILE,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN RAW;
Function overloads
DBMS_LOB.SUBSTR('string', amount, offset)
Usage example
Oracle
SELECT
-- 1. "some magic here"
DBMS_LOB.SUBSTR('some magic here', 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR('some magic here', 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR('some magic here', 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR('some magic here', 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR('some magic here', 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR('some magic here', 250, 16) "6"
FROM DUAL;
Snowflake
SELECT
-- 1. "some magic here"
SUBSTR('some magic here', 1, 15) "1",
-- 2. "some"
SUBSTR('some magic here', 1, 4) "2",
-- 3. "me magic here"
SUBSTR('some magic here', 3, 15) "3",
-- 4. "magic"
SUBSTR('some magic here', 6, 5) "4",
-- 5. "here"
SUBSTR('some magic here', 12, 20) "5",
-- 6. " "
SUBSTR('some magic here', 16, 250) "6"
FROM DUAL;
DBMS_LOB.SUBSTR(BLOB, amount, offset)
Usage example
Note: Result values in Oracle and Snowflake are being converted from bytes to strings for easier understanding of the function.
For Snowflake consider using:
hex_decode_string( to_varchar(SUBSTR(blob_column, 1, 6), 'HEX'));
and for Oracle consider using:
utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(blob_column, 1, 6));
to obtain the result as a string.
Oracle
-- Create Table
CREATE TABLE blobtable( blob_column BLOB );
-- Insert sample value
INSERT INTO blobtable VALUES (utl_raw.cast_to_raw('some magic here'));
-- Select different examples
SELECT
-- 1. "some magic here"
DBMS_LOB.SUBSTR(blob_column, 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR(blob_column, 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR(blob_column, 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR(blob_column, 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR(blob_column, 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR(blob_column, 250, 16) "6"
FROM BLOBTABLE;
Snowflake
-- Create Table
CREATE OR REPLACE TABLE blobtable ( blob_column BINARY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
-- Insert sample value
INSERT INTO blobtable
VALUES (
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'utl_raw.cast_to_raw' IS NOT CURRENTLY SUPPORTED. **
'' AS cast_to_raw);
-- Select different examples
SELECT
-- 1. "some magic here"
SUBSTR(blob_column, 1, 15) "1",
-- 2. "some"
SUBSTR(blob_column, 1, 4) "2",
-- 3. "me magic here"
SUBSTR(blob_column, 3, 15) "3",
-- 4. "magic"
SUBSTR(blob_column, 6, 5) "4",
-- 5. "here"
SUBSTR(blob_column, 12, 20) "5",
-- 6. " "
SUBSTR(blob_column, 16, 250) "6"
FROM
BLOBTABLE;
Note: UTL_RAW.CAST_TO_RAW()
is currently not being transformed to TO_BINARY()
. The function is used to show the functional equivalence of the example.
DBMS_LOB.SUBSTR(CLOB, amount, offset)
Usage example
Oracle
-- Create Table
CREATE TABLE clobtable(clob_column CLOB);
-- Insert sample value
INSERT INTO clobtable VALUES ('some magic here');
-- Select
SELECT
-- 1. "some magic here"
DBMS_LOB.SUBSTR(clob_column, 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR(clob_column, 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR(clob_column, 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR(clob_column, 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR(clob_column, 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR(clob_column, 250, 16) "6"
FROM clobtable;
Snowflake
-- Create Table
CREATE OR REPLACE TABLE clobtable (clob_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
-- Insert sample value
INSERT INTO clobtable
VALUES ('some magic here');
-- Select
SELECT
-- 1. "some magic here"
SUBSTR(clob_column, 1, 15) "1",
-- 2. "some"
SUBSTR(clob_column, 1, 4) "2",
-- 3. "me magic here"
SUBSTR(clob_column, 3, 15) "3",
-- 4. "magic"
SUBSTR(clob_column, 6, 5) "4",
-- 5. "here"
SUBSTR(clob_column, 12, 20) "5",
-- 6. " "
SUBSTR(clob_column, 16, 250) "6"
FROM
clobtable;
Note: UTL_RAW.CAST_TO_RAW()
is currently not being transformed to TO_BINARY()
. The function is used to show the functional equivalence of the example.
DBMS_LOB.SUBSTR(BFILE, amount, offset)
Usage example
Using DBMS_LOB.SUBSTR() on a BFILE column returns a substring of the file content.
Next example is not a current migration, but a functional example to show the differences of the SUBSTR function on BFILE types.
File Content (file.txt):
some magic here
Oracle
CREATE OR REPLACE PROCEDURE bfile_substr_procedure
IS
fil BFILE := BFILENAME('MY_DIR', 'file.txt');
BEGIN
DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(fil,9,1)));
--Console Output:
-- "some magi"
DBMS_LOB.FILECLOSE(fil);
END;
Snowflake
BFILE columns are translated into VARCHAR columns, therefore applying a SUBSTR
function on the same column would return a substring of the file name, not the file content.
CREATE OR REPLACE PROCEDURE bfile_substr_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
fil VARCHAR := PUBLIC.BFILENAME_UDF('MY_DIR', 'file.txt');
BEGIN
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_LOB.FILEOPEN' IS NOT CURRENTLY SUPPORTED. **
DBMS_LOB.FILEOPEN(:fil,
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_LOB.FILE_READONLY' IS NOT CURRENTLY SUPPORTED. **
'' AS FILE_READONLY);
--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF(
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'UTL_RAW.CAST_TO_VARCHAR2' IS NOT CURRENTLY SUPPORTED. **
'' AS CAST_TO_VARCHAR2);
--Console Output:
-- "some magi"
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_LOB.FILECLOSE' IS NOT CURRENTLY SUPPORTED. **
DBMS_LOB.FILECLOSE(:fil);
END;
$$;
Known Issues
1. Using DBMS_LOB.SUBSTR with BFILE columns
The current transformation for BFILE datatypes in columns is VARCHAR, where the name of the file is stored as a string. Therefore applying the SUBSTR function on a BFILE column after transformation will return a substring of the file name, while Oracle would return a substring of the file content.
Related EWIs
SSC-FDM-OR0041: Built In Package Not Supported.
SSC-EWI-OR0121: Using DBMS_LOB.SUBSTR built-in package with a BFILE column is not supported in Snowflake.
SSC-EWI-OR0115:
Last updated