SnowConvert : Paquets Oracle intégrés¶

Description¶

Oracle fournit de nombreux paquets PL/SQL avec le serveur Oracle pour étendre la fonctionnalité de la base de données et fournir l’accès PL/SQL aux fonctions SQL. (Paquets intégrés Oracle Oracle PL/SQL)

DBMS_RANDOM¶

Description¶

Le paquet DBMS_RANDOM fournit un générateur de nombres aléatoires intégré. DBMS_RANDOM n’est pas destiné à la cryptographie. (Oracle PL/SQL DBMS_RANDOM)

Fonctions VALUE¶

Description¶

La fonction de base permet d’obtenir un nombre aléatoire, supérieur ou égal à 0 et inférieur à 1. Vous pouvez également obtenir un nombre Oracle aléatoire X, où X est supérieur ou égal à low et inférieur à high. (Oracle PL/SQL DBMS_RANDOM.VALUE)

Cette UDF est implémentée en utilisant la fonction Math.random de Javascript pour répliquer la fonctionnalité de la fonction Oracle DBMS_RANDOM.VALUE.

Syntaxe¶

DBMS_RANDOM.VALUE()
    RETURN NUMBER;

DBMS_RANDOM.VALUE(
    low NUMBER,
    high NUMBER)
    RETURN NUMBER;

Copy

Surcharges UDF personnalisées¶

Données de configuration¶

Le schéma DBMS_RANDOM doit être créé.

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()¶

Paramètres

Aucun paramètre.

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

Remarque : L’UDF ne prend en charge qu’approximativement entre 9 et 10 chiffres dans la partie décimale du nombre (9 ou 10 chiffres de précision)

Exemple d’utilisation¶

Oracle

SELECT DBMS_RANDOM.VALUE() FROM DUAL;

Copy

|DBMS_RANDOM.VALUE()                         |
|--------------------------------------------|
|0.47337471168356406022193430290380483126    |

Copy

Remarque : La fonction peut être appelée_DBMS_RANDOM.VALUE()_ ou _ DBMS_RANDOM.VALUE._

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

Remarque : Dans Snowflake, vous devez mettre les parenthèses.

DBMS_RANDOM.VALUE(NUMBER, NUMBER)¶

Paramètres

low : Le plus petit NUMBER à partir duquel un nombre aléatoire est généré. Le nombre généré est supérieur ou égal à low.
high : Le plus haut NUMBER utilisé comme limite lors de la génération d’un nombre aléatoire. Le nombre généré sera inférieur à high.

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

Remarques :

La fonction Oracle DBMS_RANDOM.VALUE(low, high) n’exige pas que les paramètres aient un ordre spécifique. L’UDF Snowflake est donc mise en œuvre pour prendre en charge cette fonction en retirant toujours le nombre le plus élevé et le plus bas.
L’UDF ne prend en charge qu’approximativement entre 9 et 10 chiffres dans la partie décimale du nombre (9 ou 10 chiffres de précision).

Exemple d’utilisation¶

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

Problèmes connus¶

Aucun problème n’a été constaté.

DBMS_OUTPUT¶

Description¶

Le paquet DBMS_OUTPUT est particulièrement utile pour afficher les informations de débogage PL/SQL. (Oracle PL/SQL DBMS_OUTPUT)

Procédure PUT_LINE¶

Description¶

Cette procédure place une ligne dans la mémoire tampon. (Oracle PL/SQL DBMSOUTPUT.PUT_LINE)

Cette UDF est mise en œuvre en utilisant une table temporaire pour insérer les données à afficher afin de répliquer la fonctionnalité de la fonction Oracle DBMS_OUTPUT.PUT_LINE.

Syntaxe¶

DBMS_OUTPUT.PUT_LINE(LOG VARCHAR);

Copy

Procédure personnalisée¶

Données de configuration¶

Le schéma DBMS_OUTPUT doit être créé.

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)¶

Paramètres

LOG : Élément d’une mémoire tampon que vous souhaitez afficher.

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

Remarque :

Notez que vous utilisez une table temporaire. Si vous souhaitez que les données persistent après la fin d’une session, supprimez TEMPORARY dans CREATE TABLE.
Les tables temporaires stockent des données transitoires non permanentes. Elles n’existent que dans la session au cours de laquelle elles ont été créées et ne persistent que pour le reste de la session. Une fois la session terminée, les données stockées dans la table sont complètement supprimées du système et ne peuvent donc pas être récupérées, que ce soit par l’utilisateur qui a créé la table ou par Snowflake.

Si vous n’utilisez pas la table temporaire, gardez à l’esprit que vous aurez peut-être besoin d’une autre colonne dans la table où est inséré l’USER exécutant l’UDF DBMS_OUTPUT.PUT_LINE afin d’éviter toute confusion.

Exemple d’utilisation¶

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

Problèmes connus¶

L’UDF restera commentée car cela peut affecter les performances. Si l’utilisateur décide de l’utiliser, il lui suffit de supprimer le commentaire du code.
L’utilisateur peut modifier l’UDF afin que les informations nécessaires soient insérées dans la table DBMS_OUTPUT.PUT_LINE.

EWIs connexes¶

SSC-FDM-OR0035 : Vérifiez l’implémentation de l’UDF pour DBMS_OUTPUT.PUT_LINE_UDF.

DBMS_LOB¶

Description¶

Le paquet DBMS_LOB fournit des sous-programmes permettant d’opérer sur BLOBs, CLOBs, NCLOBs, BFILEs et sur les LOBs temporaires. Vous pouvez utiliser DBMS_LOB pour accéder et manipuler des parties spécifiques d’un LOB ou des LOBs complets. (Oracle PL/SQL DBMS_LOB)

Fonction SUBSTR¶

Description¶

Cette fonction renvoie la quantité d’octets ou de caractères d’un LOB, à partir d’un décalage absolu `` du début du LOB. (Oracle PL/SQL DBMS_LOB.SUBSTR)

Cette fonctionnalité intégrée est remplacée par la fonction SUBSTR Snowflake. Il existe cependant quelques différences.

Remarque : Les paramètres amount et offset sont inversés dans Snowflake

Syntaxe¶

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

Surcharges de fonctions¶

DBMS_LOB.SUBSTR(“string”, amount, offset)¶

Exemple d’utilisation

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)¶

Exemple d’utilisation

Remarque : Les valeurs des résultats dans Oracle et Snowflake sont converties d’octets en chaînes pour faciliter la compréhension de la fonction.

Pour Snowflake, vous pouvez utiliser :

hex_decode_string(to_varchar(SUBSTR(blob_column, 1, 6), “HEX”));

et pour Oracle, vous pouvez utiliser :

utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(blob_column, 1, 6));

pour obtenir le résultat sous forme de chaîne.

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

Remarque : UTL_RAW.CAST_TO_RAW() n’est actuellement pas transformé en TO_BINARY(). La fonction est utilisée pour montrer l’équivalence fonctionnelle de l’exemple.

DBMS_LOB.SUBSTR(CLOB, amount, offset)¶

Exemple d’utilisation

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

Remarque : UTL_RAW.CAST_TO_RAW() n’est actuellement pas transformé en TO_BINARY(). La fonction est utilisée pour montrer l’équivalence fonctionnelle de l’exemple.

DBMS_LOB.SUBSTR(BFILE, amount, offset)¶

Exemple d’utilisation

L’utilisation de DBMS_LOB.SUBSTR() sur une colonne BFILE renvoie une sous-chaîne du contenu du fichier.

L’exemple suivant n’est pas une migration actuelle, mais d’un exemple fonctionnel pour montrer les différences de la fonction SUBSTR sur les types BFILE.

Contenu du fichier (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

Les colonnes BFILE sont traduites en colonnes VARCHAR. Par conséquent, l’application d’une fonction SUBSTR à la même colonne renverrait une sous-chaîne du nom du fichier, et non de son contenu.

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

Problèmes connus¶

1. Using DBMS_LOB.SUBSTR with BFILE columns¶

La transformation actuelle pour les types de données BFILE dans les colonnes est VARCHAR, où le nom du fichier est stocké sous forme de chaîne. Par conséquent, l’application de la fonction SUBSTR à une colonne BFILE après transformation renverra une sous-chaîne du nom du fichier, alors qu’Oracle renverrait une sous-chaîne du contenu du fichier.

EWIs connexes¶

SSC-EWI-OR0076 : Le paquet intégré n’est pas pris en charge.
SSC-FDM-OR0035 : DBMS_OUTPUT.PUTLINE vérifie la mise en œuvre de l’UDF.

UTL_FILE¶

Description¶

Avec le paquet UTL_FILE, les programmes PL/SQL peuvent lire et écrire des fichiers texte. (Oracle PL/SQL UTL_FILE)

Procédure FCLOSE¶

Description¶

Cette procédure ferme un fichier ouvert identifié par un gestionnaire de fichier. (Oracle PL/SQL UTL_FILE.FCLOSE)

Cette procédure est mise en œuvre en utilisant Snowflake STAGE pour stocker les fichiers texte écrits.

Cette procédure nécessite d’être utilisée en conjonction avec :

Procédure UTL_FILE.FOPEN

Syntaxe¶

UTL_FILE.FCLOSE(
    FILE VARCHAR
    );

Copy

Données de configuration¶

Le schéma UTL_FILE doit être créé.

CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';

Copy

Si vous souhaitez télécharger le fichier, exécutez la commande suivante.

GET @UTL_FILE.utlfile_local_directory/<filename> file://<path_to_file>/<filename>;

Copy

La commande GET s’exécute dans Snowflake CLI.

Surcharges de procédures personnalisées¶

UTL_FILE.FCLOSE(VARCHAR)¶

Paramètres

FILE : Gestionnaire de fichier actif renvoyé par l’appel à UTL_FILE.FOPEN

Fonctionnalité

Cette procédure utilise la table FOPEN_TABLES_LINES créée dans la procédure UTL_FILE.FOPEN.

Cette procédure écrit dans la zone de préparation utlfile_local_directory toutes les lignes portant le même numéro FHANDLE provenant du fichier dans 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

Remarque :

Notez que cette procédure utilise la zone de préparation qui a été créée précédemment. Pour l’instant, si vous voulez écrire le fichier dans une autre zone de préparation, vous devez modifier le nom.
Ces procédures sont mises en œuvre pour les zones de préparation interne dans COPY INTO

Exemple d’utilisation¶

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

Pour exécuter cet exemple, voir ORACLE UTL_FILE

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

Problèmes connus¶

1. Modify the procedure for changing the name of the stage.¶

L’utilisateur peut modifier la procédure s’il est nécessaire de changer le nom de la zone de préparation.

2. Location static.¶

L’emplacement utilisé pour écrire dans cette procédure est statique. Une nouvelle version de la procédure devrait accroître son extension en utilisant l’emplacement qui possède le paramètre FILE.

5. Files supported.¶

Pour l’instant, cette procédure n’écrit que les fichiers .CSV.

EWIs connexes¶

SSC-FDM-0015 : Type de données non reconnu.
SSC-FDM-OR0036 : Paramètres inutiles des paquets intégrés.

Procédure FOPEN¶

Description¶

Cette procédure ouvre un fichier. (Oracle PL/SQL UTL_FILE.FOPEN)

Cette procédure est mise en œuvre avec Snowflake STAGE pour stocker les fichiers texte.

L’utilisateur est chargé de télécharger les fichiers locaux dans STAGE afin qu’ils soient utilisés par la procédure.

Cette procédure nécessite d’être utilisée en conjonction avec :

Procédure UTL_FILE.FCLOSE

Syntaxe¶

UTL_FILE.FOPEN(
    LOCATION VARCHAR,
    FILENAME VARCHAR,
    OPEN_MODE VARCHAR,
    MAX_LINESIZE NUMBER,
    );

Copy

Données de configuration¶

Le schéma UTL_FILE doit être créé.

CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';

Copy

Créez la zone de préparation 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

Si la valeur du paramètre OPEN_MODE est w ou r, il est nécessaire de télécharger le fichier dans le répertoire utlfile_local_directory.

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

Copy

La commande PUT s’exécute dans Snowflake CLI.

Surcharges de procédures personnalisées¶

UTL_FILE.FOPEN(VARCHAR, VARCHAR)¶

Paramètres

FILENAME : Le nom du fichier, y compris l’extension**.**
OPEN\MODE : Spécifie le mode d’ouverture du fichier.

Modes ouverts

Le paquet intégré Oracle UTL_FILE.FOPEN prend en charge six modes d’ouverture du fichier, mais seuls trois d’entre eux sont pris en charge dans la procédure Snowscripting.

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

Fonctionnalité

Cette procédure utilise deux tables avec lesquelles l’opération d’ouverture d’un fichier sera émulée. La table FOPEN_TABLES contient les fichiers ouverts et la table FOPEN_TABLES_LINES contient les lignes de chaque fichier.

Si le fichier est ouvert en mode écriture, un nouveau fichier est créé, s’il est ouvert en mode lecture ou ajout, il charge les lignes du fichier dans FOPEN_TABLES_LINES et insère le fichier dans FOPEN_TABLES.

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

Remarque :

Notez que cette procédure utilise la zone de préparation qui a été créée précédemment. Pour l’instant, si vous souhaitez utiliser un autre nom pour la zone de préparation, vous devez modifier la procédure.
Ces procédures sont mises en œuvre pour les zones de préparation interne dans les COPY INTO

Exemple d’utilisation¶

Oracle

DECLARE 
    w_file UTL_FILE.FILE_TYPE;
BEGIN
    w_file:= UTL_FILE.FOPEN('MY_DIR','test.csv','w',1024);
END;

Copy

Pour exécuter cet exemple, voir ORACLE UTL_FILE

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

Problèmes connus¶

1. Modify the procedure for changing the name of the stage.¶

L’utilisateur peut modifier la procédure s’il est nécessaire de changer le nom de la zone de préparation.

2. `LOCATION` parameter is not used.¶

Le paramètre LOCATION n’est pas utilisé maintenant car la zone de préparation utilisée dans la procédure est statique. Il est prévu, dans une version actualisée de la procédure, d’accroître son extension en utilisant ce paramètre pour entrer le nom de la zone de préparation où se trouve le fichier que vous souhaitez ouvrir.

3. `MAX_LINESIZE` parameter is not used¶

Le paquet intégré Oracle UTL_FILE.FOPEN comporte le paramètre MAX_LINESIZE, mais dans la procédure Snowscripting, il est supprimé car il n’est pas utilisé.

4. `OPEN_MODE` values supported¶

Cette procédure prend en charge les modes write (w), read (r) et append (a) pour ouvrir les fichiers.

5. Files supported¶

Pour l’instant, cette procédure ne prend en charge que les fichiers .CSV.

EWIs connexes¶

SSC-FDM-0015 : Type de données non reconnu.
SSC-FDM-OR0036 : UnnecessaryBuiltInPackagesParameters

Procédure PUT_LINE¶

Description¶

Cette procédure écrit la chaîne de texte stockée dans le paramètre tampon vers le fichier ouvert identifié par le gestionnaire du fichier. (Oracle PL/SQL UTL_FILE.PUT_LINE)

Cette procédure nécessite d’être utilisée en conjonction avec :

Procédure UTL_FILE.FOPEN
Procédure UTL_FILE.FCLOSE

Syntaxe¶

UTL_FILE.PUT_LINE(
    FILE VARCHAR,
    BUFFER VARCHAR,
    );

Copy

Données de configuration¶

Le schéma UTL_FILE doit être créé.

CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';

Copy

UDF personnalisées¶

UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)¶

Paramètres

FILE : Gestionnaire de fichier actif renvoyé par l’appel à UTL_FILE.FOPEN
BUFFER : Tampon de texte qui contient le texte à écrire dans le fichier**.**

Fonctionnalité

Cette procédure utilise la table FOPEN_TABLES_LINES créée dans la procédure UTL_FILE.FOPEN.

Si OPEN_MODE du fichier est write (w) ou append (a), le tampon est inséré dans FOPEN_TABLES_LINES, mais si OPEN_MODE est read (r), l’exception File_is_read_only est levée.

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

Remarque :

Pour utiliser cette procédure, vous devez ouvrir le fichier avec UTL_FILE.FOPEN

Exemple d’utilisation¶

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

Pour exécuter cet exemple, voir ORACLE UTL_FILE

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

Problèmes connus¶

1. `AUTOFLUSH` parameter is not used.¶

Le paquet intégré Oracle UTL_FILE.PUT_LINE contient le paramètre AUTOFLUSH, mais dans la procédure Snowscripting, il est supprimé car il n’est pas utilisé.

EWIs connexes¶

SSC-FDM-0015 : Type de données non reconnu.
SSC-FDM-OR0036 : UnnecessaryBuiltInPackagesParameters

SnowConvert : Paquets Oracle intégrés¶

Description¶

DBMS_RANDOM¶

Description¶

Fonctions VALUE¶

Description¶

Syntaxe¶

Surcharges UDF personnalisées¶

Données de configuration¶

DBMS_RANDOM.VALUE()¶

Exemple d’utilisation¶

DBMS_RANDOM.VALUE(NUMBER, NUMBER)¶

Exemple d’utilisation¶

Problèmes connus¶

EWIs connexes¶

DBMS_OUTPUT¶

Description¶

Procédure PUT_LINE¶

Description¶

Syntaxe¶

Procédure personnalisée¶

Données de configuration¶

DBMS_OUTPUT.PUT_LINE(VARCHAR)¶

Exemple d’utilisation¶

Problèmes connus¶

EWIs connexes¶

DBMS_LOB¶

Description¶

Fonction SUBSTR¶

Description¶

Syntaxe¶

Surcharges de fonctions¶

DBMS_LOB.SUBSTR(“string”, amount, offset)¶

DBMS_LOB.SUBSTR(B LOB, amount, offset)¶

DBMS_LOB.SUBSTR(CLOB, amount, offset)¶

DBMS_LOB.SUBSTR(BFILE, amount, offset)¶

Problèmes connus¶

1. Using DBMS_LOB.SUBSTR with BFILE columns¶

EWIs connexes¶

UTL_FILE¶

Description¶

Procédure FCLOSE¶

Description¶

Syntaxe¶

Données de configuration¶

Surcharges de procédures personnalisées¶

UTL_FILE.FCLOSE(VARCHAR)¶

Exemple d’utilisation¶

Problèmes connus¶

1. Modify the procedure for changing the name of the stage.¶

2. Location static.¶

5. Files supported.¶

EWIs connexes¶

Procédure FOPEN¶

Description¶

Syntaxe¶

Données de configuration¶

Surcharges de procédures personnalisées¶

UTL_FILE.FOPEN(VARCHAR, VARCHAR)¶

Exemple d’utilisation¶

Problèmes connus¶

1. Modify the procedure for changing the name of the stage.¶

2. LOCATION parameter is not used.¶

3. MAX_LINESIZE parameter is not used¶

4. OPEN_MODE values supported¶

5. Files supported¶

EWIs connexes¶

Procédure PUT_LINE¶

Description¶

Syntaxe¶

Données de configuration¶

UDF personnalisées¶

UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)¶

Exemple d’utilisation¶

Problèmes connus¶

1. AUTOFLUSH parameter is not used.¶

EWIs connexes¶

2. `LOCATION` parameter is not used.¶

3. `MAX_LINESIZE` parameter is not used¶

4. `OPEN_MODE` values supported¶

1. `AUTOFLUSH` parameter is not used.¶