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

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"}}';
Copy
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();
$$;
Copy

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;
Copy
|DBMS_RANDOM.VALUE()                         |
|--------------------------------------------|
|0.47337471168356406022193430290380483126    |
Copy

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;
Copy
|DBMS_RANDOM.VALUE() |
|--------------------|
|0.1014560867        |
Copy

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 gleich low.

  • high: Die höchste NUMBER, die bei der Generierung einer Zufallszahl als Grenze verwendet wird. Die erzeugte Zahl wird niedriger als high 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);
$$;
Copy

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;
Copy
|DBMS_RANDOM.VALUE(-10,30)                   |
|--------------------------------------------|
|16.0298681859960167648070354679783928085    |
Copy

Snowflake

SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF(-10,30) FROM DUAL;
Copy
|DBMS_RANDOM.VALUE(-10,30)   |
|----------------------------|
|-6.346055187                |
Copy

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);
Copy

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"}}';
Copy
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;
$$;
Copy

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();
Copy
|DBMS_OUTPUT.PUT_LINE('test') |
|-----------------------------|
|test                         |
Copy

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();
Copy
|ROW |WHEN                    |DATABASE    |LOG      |
|----|------------------------|------------|---------|
| 1  |2022-04-25 11:16:23.844 |CODETEST    |test     |
Copy

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

  1. [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;
Copy

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;
Copy
1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Copy

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;
Copy
1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Copy
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;
Copy
1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Copy

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;
Copy
1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Copy

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;
Copy
1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Copy

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;
Copy
1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Copy

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
Copy

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;
Copy
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(fil,4,1))) |
-------------------------------------------------------------------------|
some magic                                                               |
Copy

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;
$$;
Copy
SUBSTR(bfile_column, 1, 9) |
---------------------------|
MY_DIR\fi                  |
Copy

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

  1. [SSC-EWI-OR0076](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/oracleEWI. md#ssc-ewi-or0076): Integriertes Paket wird nicht unterstützt.

  2. [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:

Syntax
UTL_FILE.FCLOSE(
    FILE VARCHAR
    );
Copy
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"}}';
Copy
  • 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>;
Copy

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

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

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

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

  1. [SSC-FDM-0015](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0015): Datentyp wird nicht erkannt.

  2. [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:

Syntax
UTL_FILE.FOPEN(
    LOCATION VARCHAR,
    FILENAME VARCHAR,
    OPEN_MODE VARCHAR,
    MAX_LINESIZE NUMBER,
    );
Copy
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"}}';
Copy
  • 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;
Copy
  • Wenn der Wert im Parameter OPEN_MODE w oder r lautet, müssen Sie die Datei in das Verzeichnis utlfile_local_directory hochladen.

PUT file://<path_to_file>/<filename> @UTL_FILE.utlfile_local_directory auto_compress=false;
Copy

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_MODEDESCRIPTIONSTATUS
wWrite modeSupported
aAppend modeSupported
rRead modeSupported
rbRead byte modeUnsupported
wbWrite byte modeUnsupported
abAppend byte modeUnsupported

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

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

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

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

  1. [SSC-FDM-0015](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0015): Datentyp wird nicht erkannt.

  2. [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:

Syntax
UTL_FILE.PUT_LINE(
    FILE VARCHAR,
    BUFFER VARCHAR,
    );
Copy
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"}}';
Copy

Benutzerdefinierte UDF

UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)

Parameter

  • FILE: Aktiver Dateihandler, der vom Aufruf von UTL_FILE.FOPEN zurückgegeben wurde

  • BUFFER: 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;
Copy

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

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

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

  1. [SSC-FDM-0015](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0015): Datentyp wird nicht erkannt.

  2. [SSC-FDM-OR0036](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/oracleFDM. md#ssc-fdm-or0036): UnnecessaryBuiltInPackagesParameters