SnowConvert: Integrierte Oracle-Pakete¶
Beschreibung¶
Oracle liefert viele PL/SQL-Pakete mit dem Oracle Server, um die Datenbankfunktionalität zu erweitern und PL/SQL Zugriff auf SQL-Funktionen zu bieten. (Integrierte Oracle PL/SQL-Pakete)
DBMS_RANDOM¶
Beschreibung¶
Das Paket DBMS_RANDOM
bietet einen integrierten Zufallszahlengenerator. DBMS_RANDOM
ist nicht für die Kryptographie gedacht. ([Oracle PL/SQL DBMS_RANDOM](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_RANDOM. html#GUID-8DC48B0C-3707-4172-A306-C0308DD2EB0F))
VALUE-Funktionen¶
Beschreibung¶
Die Basisfunktion liefert eine Zufallszahl, die größer oder gleich 0 und kleiner als 1 ist. Alternativ können Sie auch eine zufällige Oracle-Zahl X
ermitteln, wobei X
größer oder gleich low
und kleiner als high
ist. ([Oracle PL/SQL DBMS_RANDOM.VALUE](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_RANDOM. html#GUID-AAD9E936-D74F-440D-9E16-24F3F0DE8D31))
Diese UDF ist unter Verwendung der Javascript-Funktion Math.random implementiert, um die Funktionalität der Oracle-Funktion DBMS_RANDOM.VALUE zu replizieren.
Syntax¶
DBMS_RANDOM.VALUE()
RETURN NUMBER;
DBMS_RANDOM.VALUE(
low NUMBER,
high NUMBER)
RETURN NUMBER;
Benutzerdefinierte UDF-Überlastungen¶
Datenkonfiguration¶
Das Schema DBMS_RANDOM
muss erstellt werden.
CREATE SCHEMA IF NOT EXISTS DBMS_RANDOM
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
DBMS_RANDOM.VALUE()¶
Parameter
Keine Parameter.
CREATE OR REPLACE FUNCTION DBMS_RANDOM.VALUE_UDF()
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
return Math.random();
$$;
Anmerkung: Die UDF unterstützt nur etwa 9 bis 10 Stellen im Dezimalteil der Zahl (9 oder 10 Ziffern Genauigkeit)
Verwendungsbeispiel¶
Oracle
SELECT DBMS_RANDOM.VALUE() FROM DUAL;
|DBMS_RANDOM.VALUE() |
|--------------------------------------------|
|0.47337471168356406022193430290380483126 |
Anmerkung: Die Funktion kann entweder _DBMS_RANDOM.VALUE()
_ oder _ DBMS_RANDOM.VALUE aufgerufen werden.
_
Snowflake
SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF() FROM DUAL;
|DBMS_RANDOM.VALUE() |
|--------------------|
|0.1014560867 |
Anmerkung: In Snowflake müssen Sie die Klammern setzen.
DBMS_RANDOM.VALUE(NUMBER, NUMBER)¶
Parameter
low: Die niedrigste
NUMBER
, aus der eine Zufallszahl generiert wird. Die erzeugte Zahl ist größer oder gleichlow
.high: Die höchste
NUMBER
, die bei der Generierung einer Zufallszahl als Grenze verwendet wird. Die erzeugte Zahl wird niedriger alshigh
sein.
CREATE OR REPLACE FUNCTION DBMS_RANDOM.VALUE_UDF(low double, high double)
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
if (LOW > HIGH) {
[LOW, HIGH] = [HIGH, LOW];
}
const MAX_DECIMAL_DIGITS = 38;
return (Math.random() * (HIGH - LOW) + LOW).toFixed(MAX_DECIMAL_DIGITS);
$$;
Anmerkungen:
Die Funktion Oracle DBMS_RANDOM.VALUE(low, high) erfordert keine bestimmte Reihenfolge der Parameter, so dass die Snowflake UDF implementiert wurde, um dieses Feature zu unterstützen, indem immer die höchste und die niedrigste Zahl herausgenommen wird.
Die UDF unterstützt nur etwa 9 bis 10 Ziffern im Dezimalteil der Zahl (9 oder 10 Ziffern Genauigkeit).
Verwendungsbeispiel¶
Oracle
SELECT DBMS_RANDOM.VALUE(-10,30) FROM DUAL;
|DBMS_RANDOM.VALUE(-10,30) |
|--------------------------------------------|
|16.0298681859960167648070354679783928085 |
Snowflake
SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF(-10,30) FROM DUAL;
|DBMS_RANDOM.VALUE(-10,30) |
|----------------------------|
|-6.346055187 |
Bekannte Probleme¶
Es wurden keine Probleme gefunden.
DBMS_OUTPUT¶
Beschreibung¶
Das Paket DBMS_OUTPUT
ist besonders nützlich für die Anzeige von PL/SQL-Debugging-Informationen. (Oracle PL/SQL DBMS_OUTPUT)
PUT_LINE-Prozedur¶
Beschreibung¶
Diese Prozedur setzt eine Zeile in den Puffer. (Oracle PL/SQL DBMSOUTPUT.PUT_LINE)
Diese UDF ist mit einer temporären Tabelle implementiert, in die die anzuzeigenden Daten eingefügt werden, um die Funktionalität von Oracle DBMS_OUTPUT.PUT_LINE
-Funktion zu replizieren.
Syntax¶
DBMS_OUTPUT.PUT_LINE(LOG VARCHAR);
Benutzerdefinierte Prozedur¶
Datenkonfiguration¶
Das Schema DBMS_OUTPUT
muss erstellt werden.
CREATE SCHEMA IF NOT EXISTS DBMS_OUTPUT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
DBMS_OUTPUT.PUT_LINE(VARCHAR)¶
Parameter
LOG: Element in einem Puffer, das Sie anzeigen möchten.
CREATE OR REPLACE procedure DBMS_OUTPUT.PUT_LINE_UDF(LOG VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS $$
//Performance may be affected by using this UDF.
//If you want to start logging information, please uncomment the implementation.
//Once the calls of DBMS_OUTPUT.PUT_LINE have been done, please use
//the following query to read all the logs:
//SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG.
//snowflake.execute({sqlText:`
//CREATE TEMPORARY TABLE IF NOT EXISTS DBMS_OUTPUT_LOG
//(
// WHEN TIMESTAMP,
// DATABASE VARCHAR,
// LOG VARCHAR
//);`});
//snowflake.execute({sqlText:`INSERT INTO DBMS_OUTPUT_LOG(WHEN, DATABASE, LOG) VALUES (CURRENT_TIMESTAMP,CURRENT_DATABASE(),?)`, binds:[LOG]});
return LOG;
$$;
Anmerkung:
Beachten Sie, dass hier eine temporäre Tabelle verwendet wird. Wenn Sie möchten, dass die Daten auch nach dem Ende einer Sitzung erhalten bleiben, entfernen Sie bitte TEMPORARY aus CREATE TABLE.
Die temporären Tabellen speichern nicht dauerhafte, vorübergehende Daten. Sie existieren nur innerhalb der Sitzung, in der sie erstellt wurden, und bleiben auch nur für den Rest der Sitzung bestehen. Nach Beendigung der Sitzung werden die in der Tabelle gespeicherten Daten vollständig aus dem System entfernt und sind daher nicht wiederherstellbar, weder für den Benutzer, der die Tabelle erstellt hat, noch für Snowflake.
Wenn Sie die temporäre Tabelle nicht verwenden, denken Sie daran, dass Sie möglicherweise eine weitere Spalte in der Tabelle benötigen, in die die DBMS_OUTPUT.PUT_LINE UDF eingefügt wird, um Verwechslungen zu vermeiden.
Verwendungsbeispiel¶
Oracle
CREATE OR REPLACE PROCEDURE PROC
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Test');
END;
CALL PROC();
|DBMS_OUTPUT.PUT_LINE('test') |
|-----------------------------|
|test |
Snowflake
CREATE OR REPLACE PROCEDURE PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF('Test');
END;
$$;
CALL PROC();
|ROW |WHEN |DATABASE |LOG |
|----|------------------------|------------|---------|
| 1 |2022-04-25 11:16:23.844 |CODETEST |test |
Bekannte Probleme¶
Der Code UDF bleibt auskommentiert, da er die Leistung beeinträchtigen kann. Wenn der Benutzer ihn verwenden möchte, muss er den Code nur auskommentieren
Der Benutzer kann die UDF so ändern, dass die erforderlichen Informationen in die Tabelle DBMS_OUTPUT.PUT_LINE eingefügt werden.
Zugehörige EWIs¶
[SSC-FDM-OR0035](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/oracleFDM. md#ssc-fdm-or0035): Überprüfen Sie die UDF-Implementierung für DBMS\_OUTPUT.PUT_LINE\_UDF.
DBMS_LOB¶
Beschreibung¶
Das Paket DBMS_LOB
enthält Unterprogramme, die auf BLOBs
, CLOBs
, NCLOBs
, BFILEs
und temporär LOBs
arbeiten. Sie können DBMS_LOB
verwenden, um auf bestimmte Teile eines LOB oder eines kompletten LOBs zuzugreifen und diese zu bearbeiten. (Oracle PL/SQL DBMS_LOB)
SUBSTR-Funktion¶
Beschreibung¶
Diese Funktion gibt amount
Bytes oder Zeichen einer LOB zurück, beginnend mit einem absoluten offset
vom Anfang der LOB. (Oracle PL/SQL DBMS_LOB.SUBSTR)
Diese integrierte Funktion wird durch die Snowflake SUBSTR-Funktion ersetzt. Allerdings gibt es einige Unterschiede.
Anmerkung: Die Parameter amount und offset sind in Snowflake invertiert
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;
Funktionsüberlastungen¶
DBMS_LOB.SUBSTR(‚string‘, amount, offset)¶
Verwendungsbeispiel
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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
DBMS_LOB.SUBSTR(B LOB, amount, offset)¶
Verwendungsbeispiel
Anmerkung: Ergebniswerte in Oracle und Snowflake werden zum besseren Verständnis der Funktion von Bytes in Zeichenfolgen konvertiert.
Für Snowflake verwenden Sie bitte:
hex_decode_string( to_varchar(SUBSTR(blob_column, 1, 6), ‚HEX‘));
und für Oracle:
utl\_raw.cast_to\_varchar2(DBMS\_LOB.SUBSTR(blob_column, 1, 6));
um das Ergebnis in Form einer Zeichenfolge zu erhalten.
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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
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 (
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - 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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Anmerkung: UTL_RAW.CAST_TO_RAW()
wird derzeit nicht in TO_BINARY()
umgewandelt. Die Funktion wird verwendet, um die Funktionsäquivalenz des Beispiels zu zeigen.
DBMS_LOB.SUBSTR(CLOB, amount, offset)¶
Verwendungsbeispiel
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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Anmerkung: UTL_RAW.CAST_TO_RAW()
wird derzeit nicht in TO_BINARY()
umgewandelt. Die Funktion wird verwendet, um die Funktionsäquivalenz des Beispiels zu zeigen.
DBMS_LOB.SUBSTR(BFILE, amount, offset)¶
Verwendungsbeispiel
Die Verwendung von DBMS_LOB.SUBSTR() für eine BFILE-Spalte gibt eine Teilzeichenkette des Dateiinhalts zurück.
Das nächste Beispiel ist keine aktuelle Migration, sondern ein funktionales Beispiel, um die Unterschiede der Funktion SUBSTR auf BFILE-Typen zu zeigen.
Dateiinhalt (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;
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(fil,4,1))) |
-------------------------------------------------------------------------|
some magic |
Snowflake
BFILE-Spalten werden in VARCHAR-Spalten übersetzt, daher würde die Anwendung einer SUBSTR
-Funktion auf dieselbe Spalte eine Teilzeichenfolge des Dateinamens zurückgeben, nicht den Inhalt der Datei.
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
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_LOB.FILEOPEN' IS NOT CURRENTLY SUPPORTED. ***/!!!
DBMS_LOB.FILEOPEN(:fil,
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - 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(
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'UTL_RAW.CAST_TO_VARCHAR2' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS CAST_TO_VARCHAR2);
--Console Output:
-- "some magi"
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_LOB.FILECLOSE' IS NOT CURRENTLY SUPPORTED. ***/!!!
DBMS_LOB.FILECLOSE(:fil);
END;
$$;
SUBSTR(bfile_column, 1, 9) |
---------------------------|
MY_DIR\fi |
Bekannte Probleme¶
1. Using DBMS_LOB.SUBSTR with BFILE columns¶
Die aktuelle Transformation für BFILE-Datentypen in Spalten ist VARCHAR, wobei der Name der Datei als Zeichenfolge gespeichert wird. Die Anwendung der Funktion SUBSTR auf eine BFILE-Spalte nach der Transformation wird daher eine Teilzeichenfolge des Dateinamens zurückgeben, während Oracle eine Teilzeichenfolge des Dateiinhalts zurückgeben würde.
Zugehörige EWIs¶
[SSC-EWI-OR0076](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/oracleEWI. md#ssc-ewi-or0076): Integriertes Paket wird nicht unterstützt.
[SSC-FDM-OR0035](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/oracleFDM. md#ssc-fdm-or0035): DBMS_OUTPUT.PUTLINE UDF Implementierung überprüfen.
UTL_FILE¶
Beschreibung¶
Mit dem Paket UTL_FILE
können PL/SQL-Programme Textdateien lesen und schreiben. ([Oracle PL/SQL UTL_FILE](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_FILE. html#GUID-EBC42A36-EB72-4AA1-B75F-8CF4BC6E29B4))
FCLOSE-Prozedur¶
Beschreibung¶
Diese Prozedur schließt eine offene Datei, die durch ein Dateihandle identifiziert wird. (Oracle PL/SQL UTL_FILE.FCLOSE)
Diese Prozedur wird mit Snowflake STAGE implementiert, um die geschriebenen Textdateien zu speichern.
Diese Prozedur muss in Verbindung verwendet werden mit:
UTL_FILE.FOPEN
Prozedur
Syntax¶
UTL_FILE.FCLOSE(
FILE VARCHAR
);
Datenkonfiguration¶
Das Schema
UTL_FILE
muss erstellt werden.
CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
Wenn Sie die Datei herunterladen möchten, führen Sie den folgenden Befehl aus.
GET @UTL_FILE.utlfile_local_directory/<filename> file://<path_to_file>/<filename>;
Der Befehl GET wird in Snowflake CLI ausgeführt.
Benutzerdefinierte Prozedurüberlastungen¶
UTL_FILE.FCLOSE(VARCHAR)¶
Parameter
FILE: Aktiver Dateihandler, der vom Aufruf von
UTL_FILE.FOPEN
zurückgegeben wurde
Funktionalität
Diese Prozedur verwendet die Tabelle FOPEN_TABLES_LINES
, die mit der Prozedur UTL_FILE.FOPEN
erstellt wurde.
Diese Prozedur schreibt in den Stagingbereich von utlfile_local_directory alle Zeilen mit demselben FHANDLE
aus der Datei in FOPEN_TABLES_LINES
.
CREATE OR REPLACE PROCEDURE UTL_FILE.FCLOSE_UDF(FILE VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
DECLARE
fhandle VARCHAR;
fileParse VARIANT;
File_is_read_only exception;
fileNameConcat VARCHAR;
copyIntoQuery VARCHAR ;
BEGIN
fileParse:= PARSE_JSON(FILE);
fhandle:= :fileParse:handle;
fileNameConcat:= '@UTL_FILE.utlfile_local_directory/'||:fileParse:name;
copyIntoQuery:= 'COPY INTO '||:fileNameConcat||' FROM (SELECT LINE FROM UTL_FILE.FOPEN_TABLES_LINES WHERE FHANDLE = ? ORDER BY SEQ) FILE_FORMAT= (FORMAT_NAME = my_csv_format COMPRESSION=NONE) OVERWRITE=TRUE';
EXECUTE IMMEDIATE :copyIntoQuery USING (fhandle);
DELETE FROM UTL_FILE.FOPEN_TABLES_LINES WHERE FHANDLE = :fhandle;
DELETE FROM UTL_FILE.FOPEN_TABLES WHERE FHANDLE = :fhandle;
END
$$;
Anmerkung:
Beachten Sie, dass diese Prozedur den Stagingbereich verwendet, der zuvor erstellt wurde. Wenn Sie die Datei in einem anderen Stagingbereich schreiben möchten, müssen Sie den Namen zunächst ändern.
Diese Prozeduren werden für die internen Stagingbereiche in
COPY INTO
implementiert
Verwendungsbeispiel¶
Oracle
DECLARE
w_file UTL_FILE.FILE_TYPE;
BEGIN
w_file:= UTL_FILE.FOPEN('MY_DIR','test.csv','w',1024);
UTL_FILE.PUT_LINE(w_file,'New line');
UTL_FILE.FCLOSE(w_file);
END;
Um dieses Beispiel auszuführen, siehe [ORACLE UTL_FILE
](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_FILE. html#GUID-FA16A38B-26AA-4002-9BE0-7D3950557F8C)
Snowflake
DECLARE
w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := OBJECT_CONSTRUCT();
call_results VARIANT;
BEGIN
w_file:=
--** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
--** SSC-FDM-OR0036 - PARAMETERS: 'AUTOFLUSH_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
call_results := (
CALL UTL_FILE.PUT_LINE_UDF(:w_file,'New line')
);
call_results := (
CALL UTL_FILE.FCLOSE_UDF(:w_file)
);
RETURN call_results;
END;
Bekannte Probleme¶
1. Modify the procedure for changing the name of the stage.¶
Der Benutzer kann die Prozedur ändern, wenn der Name des Stagingbereichs geändert werden muss.
2. Location static.¶
Der Speicherort, an dem diese Prozedur geschrieben wird, ist statisch. Es wird erwartet, dass eine neue Version der Prozedur ihre Erweiterbarkeit erhöht, indem sie den Speicherort verwendet, der den Parameter FILE
hat.
5. Files supported.¶
Diese Prozedur schreibt vorerst nur .CSV-Dateien.
Zugehörige EWIs¶
[SSC-FDM-0015](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0015): Datentyp wird nicht erkannt.
[SSC-FDM-OR0036](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/oracleFDM. md#ssc-fdm-or0036): Unnötige Parameter für integrierte Pakete.
FOPEN-Prozedur¶
Beschreibung¶
Diese Prozedur öffnet eine Datei. ([Oracle PL/SQL UTL_FILE.FOPEN](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_FILE. html#GUID-DF14ADC3-983D-4E0F-BE2C-60733FF58539))
Diese Prozedur wird mit Snowflake STAGE implementiert, um die Textdateien zu speichern.
Der Benutzer ist für das Hochladen der lokalen Dateien in den STAGE, der von der Prozedur verwendet werden sollen, verantwortlich.
Diese Prozedur muss in Verbindung verwendet werden mit:
UTL_FILE.FCLOSE
Prozedur
Syntax¶
UTL_FILE.FOPEN(
LOCATION VARCHAR,
FILENAME VARCHAR,
OPEN_MODE VARCHAR,
MAX_LINESIZE NUMBER,
);
Datenkonfiguration¶
Das Schema
UTL_FILE
muss erstellt werden.
CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
Erstellen Sie den Stagingbereich
utlfile_local_directory
.
CREATE OR REPLACE FILE FORMAT my_csv_format TYPE = csv;
CREATE OR REPLACE STAGE utlfile_local_directory
file_format = my_csv_format;
Wenn der Wert im Parameter
OPEN_MODE
w oder r lautet, müssen Sie die Datei in das Verzeichnisutlfile_local_directory
hochladen.
PUT file://<path_to_file>/<filename> @UTL_FILE.utlfile_local_directory auto_compress=false;
Der Befehl PUT wird in Snowflake CLI ausgeführt.
Benutzerdefinierte Prozedurüberlastungen¶
UTL_FILE.FOPEN(VARCHAR, VARCHAR)¶
Parameter
FILENAME: Der Name der Datei, einschließlich der Erweiterung**.**
OPEN_MODE: Legt fest, wie die Datei geöffnet wird.
Offene Modi
Die Prozedur Oracle Integriertes Paket [UTL_FILE.FOPEN
](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_FILE. html#GUID-DF14ADC3-983D-4E0F-BE2C-60733FF58539) unterstützt sechs Modi für das Öffnen der Datei, aber nur drei davon werden in der Snowscripting-Prozedur unterstützt.
OPEN_MODE | DESCRIPTION | STATUS |
---|---|---|
w | Write mode | Supported |
a | Append mode | Supported |
r | Read mode | Supported |
rb | Read byte mode | Unsupported |
wb | Write byte mode | Unsupported |
ab | Append byte mode | Unsupported |
Funktionalität
Diese Prozedur verwendet zwei Tabellen, mit denen die Operation des Öffnens einer Datei emuliert wird. In der Tabelle FOPEN_TABLES
werden die geöffneten Dateien gespeichert und in der Tabelle FOPEN_TABLES_LINES
die Zeilen, die zu jeder Datei gehören.
Wenn die Datei im Schreibmodus geöffnet wird, wird eine neue Datei erstellt, wenn sie im Lese- oder Anfügemodus geöffnet wird, lädt sie die Zeilen der Datei in FOPEN_TABLES_LINES
und fügt die Datei in FOPEN_TABLES
ein.
CREATE OR REPLACE PROCEDURE UTL_FILE.FOPEN_UDF(FILENAME VARCHAR,OPEN_MODE VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS $$
DECLARE
fhandle VARCHAR;
key VARCHAR;
status VARCHAR;
File_is_not_loaded_on_stage exception;
fileNameConcat VARCHAR:= '@UTL_FILE.utlfile_local_directory/'||:FILENAME;
copyIntoQuery VARCHAR DEFAULT 'COPY INTO UTL_FILE.FOPEN_TABLES_LINES (FHANDLE, LINE) FROM (SELECT ? , stageFile.$1 FROM '||:fileNameConcat||' stageFile)';
BEGIN
CREATE TABLE IF NOT EXISTS UTL_FILE.FOPEN_TABLES
(
FHANDLE VARCHAR,
FILENAME VARCHAR,
OPEN_MODE VARCHAR
);
CREATE TABLE IF NOT EXISTS UTL_FILE.FOPEN_TABLES_LINES
(
SEQ NUMBER AUTOINCREMENT,
FHANDLE VARCHAR,
LINE VARCHAR
);
SELECT FHANDLE INTO fhandle FROM UTL_FILE.FOPEN_TABLES WHERE FILENAME = :FILENAME;
SELECT UUID_STRING() INTO key;
IF (OPEN_MODE = 'w') THEN
INSERT INTO UTL_FILE.FOPEN_TABLES(FHANDLE, FILENAME, OPEN_MODE) VALUES(:key,:FILENAME,:OPEN_MODE);
RETURN TO_JSON({ 'name': FILENAME, 'handle': key});
ELSE
IF (fhandle IS NULL) THEN
EXECUTE IMMEDIATE :copyIntoQuery USING (key);
SELECT OBJECT_CONSTRUCT(*):status INTO status FROM table(result_scan(last_query_id()));
IF (status = 'LOADED') THEN
INSERT INTO UTL_FILE.FOPEN_TABLES(FHANDLE, FILENAME, OPEN_MODE) VALUES(:key,:FILENAME,:OPEN_MODE);
RETURN TO_JSON({'name': FILENAME, 'handle': key});
ELSE
raise File_is_not_loaded_on_stage;
END IF;
ELSE
UPDATE UTL_FILE.FOPEN_TABLES SET OPEN_MODE = :OPEN_MODE WHERE FHANDLE = :fhandle;
RETURN TO_JSON({'name': FILENAME, 'handle': fhandle});
END IF;
END IF;
END
$$;
Anmerkung:
Beachten Sie, dass diese Prozedur den Stagingbereich verwendet, der zuvor erstellt wurde. Wenn Sie jetzt einen anderen Namen für den Stagingbereich verwenden möchten, müssen Sie die Prozedur ändern.
Diese Prozeduren werden für die internen Stagingbereiche in
COPY INTO
implementiert
Verwendungsbeispiel¶
Oracle
DECLARE
w_file UTL_FILE.FILE_TYPE;
BEGIN
w_file:= UTL_FILE.FOPEN('MY_DIR','test.csv','w',1024);
END;
Um dieses Beispiel auszuführen, siehe [ORACLE UTL_FILE
](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_FILE. html#GUID-FA16A38B-26AA-4002-9BE0-7D3950557F8C)
Snowflake
DECLARE
w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := OBJECT_CONSTRUCT();
BEGIN
w_file:=
--** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
END;
Bekannte Probleme¶
1. Modify the procedure for changing the name of the stage.¶
Der Benutzer kann die Prozedur ändern, wenn der Name des Stagingbereichs geändert werden muss.
2. LOCATION
parameter is not used.¶
Der Parameter LOCATION
wird jetzt nicht mehr verwendet, da der Stagingbereich, der in der Prozedur verwendet wird, statisch ist. Es ist für eine aktualisierte Version der Prozedur geplant, seine Erweiterbarkeit zu erhöhen, indem Sie mit diesem Parameter den Namen des Stagingbereichs eingeben, in dem sich die zu öffnende Datei befindet.
3. MAX_LINESIZE
parameter is not used¶
Die Prozedur Oracle Integriertes Paket [UTL_FILE.FOPEN
](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_FILE. html#GUID-DF14ADC3-983D-4E0F-BE2C-60733FF58539) hat den Parameter MAX_LINESIZE
, aber in der Snowscripting-Prozedur wird er entfernt, weil er nicht verwendet wird.
4. OPEN_MODE
values supported¶
Diese Prozedur unterstützt die Modi write (w), read (r) und append (a) zum Öffnen von Dateien.
5. Files supported¶
Diese Prozedur unterstützt vorerst nur .CSV-Dateien.
Zugehörige EWIs¶
[SSC-FDM-0015](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0015): Datentyp wird nicht erkannt.
[SSC-FDM-OR0036](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/oracleFDM. md#ssc-fdm-or0036): UnnecessaryBuiltInPackagesParameters
PUT_LINE-Prozedur¶
Beschreibung¶
Diese Prozedur schreibt die im Puffer-Parameter gespeicherten Text-Zeichenfolge in die geöffnete Datei, die durch den Datei-Handle identifiziert wird. (Oracle PL/SQL UTL_FILE.PUT_LINE)
Diese Prozedur muss in Verbindung verwendet werden mit:
UTL_FILE.FOPEN
ProzedurUTL_FILE.FCLOSE
Prozedur
Syntax¶
UTL_FILE.PUT_LINE(
FILE VARCHAR,
BUFFER VARCHAR,
);
Datenkonfiguration¶
Das Schema
UTL_FILE
muss erstellt werden.
CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
Benutzerdefinierte UDF¶
UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)¶
Parameter
FILE: Aktiver Dateihandler, der vom Aufruf von
UTL_FILE.FOPEN
zurückgegeben wurdeBUFFER: Textpuffer, der den Text enthält, der in die Datei**.**geschrieben werden soll
Funktionalität
Diese Prozedur verwendet die Tabelle FOPEN_TABLES_LINES
, die mit der Prozedur UTL_FILE.FOPEN
erstellt wurde.
Wenn OPEN_MODE
der Datei write (w) oder append (a) ist, wird der Puffer in FOPEN_TABLES_LINES
eingefügt, aber wenn OPEN_MODE
read ist (r), wird die Ausnahme File_is_read_only
ausgelöst.
CREATE OR REPLACE PROCEDURE UTL_FILE.PUT_LINE_UDF(FILE VARCHAR,BUFFER VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS $$
DECLARE
openMode VARCHAR;
openModeTemp VARCHAR;
fhandle VARCHAR;
fileParse VARIANT;
File_is_read_only exception;
BEGIN
fileParse:= PARSE_JSON(FILE);
fhandle:= :fileParse:handle;
SELECT OPEN_MODE INTO openModeTemp FROM UTL_FILE.FOPEN_TABLES WHERE FHANDLE = :fhandle;
IF (openModeTemp = 'a' or openModeTemp = 'w') THEN
INSERT INTO UTL_FILE.FOPEN_TABLES_LINES(FHANDLE,LINE) VALUES(:fhandle,:BUFFER);
ELSE
raise File_is_read_only;
END IF;
END
$$;
-- This SELECT is manually added and not generated by Snowconvert
SELECT * FROM UTL_FILE.FOPEN_TABLES_LINES;
Anmerkung:
Um diese Prozedur zu verwenden, müssen Sie die Datei mit UTL_FILEFOPEN. öffnen
Verwendungsbeispiel¶
Oracle
DECLARE
w_file UTL_FILE.FILE_TYPE;
BEGIN
w_file:= UTL_FILE.FOPEN('MY_DIR','test.csv','w',1024);
UTL_FILE.PUT_LINE(w_file,'New line');
END;
Um dieses Beispiel auszuführen, siehe [ORACLE UTL_FILE
](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_FILE. html#GUID-FA16A38B-26AA-4002-9BE0-7D3950557F8C)
Snowflake
DECLARE
w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := OBJECT_CONSTRUCT();
call_results VARIANT;
BEGIN
w_file:=
--** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
--** SSC-FDM-OR0036 - PARAMETERS: 'AUTOFLUSH_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
call_results := (
CALL UTL_FILE.PUT_LINE_UDF(:w_file,'New line')
);
RETURN call_results;
END;
Bekannte Probleme¶
1. AUTOFLUSH
parameter is not used.¶
Die Prozedur Oracle Integriertes Paket [UTL_FILE.PUT_LINE
](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_FILE. html#GUID-BC046363-6F14-4128-B4D2-836DDBDB9B48) hat den Parameter AUTOFLUSH
, aber in der Snowscripting-Prozedur wird er entfernt, weil er nicht verwendet wird.
Zugehörige EWIs¶
[SSC-FDM-0015](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0015): Datentyp wird nicht erkannt.
[SSC-FDM-OR0036](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/oracleFDM. md#ssc-fdm-or0036): UnnecessaryBuiltInPackagesParameters