SnowConvert: Pacotes incorporados do Oracle¶

Descrição¶

O Oracle fornece muitos pacotes PL/SQL com o servidor Oracle para ampliar a funcionalidade do banco de dados e fornecer acesso a PL/SQL aos recursos SQL. (Pacotes incorporados do Oracle PL/SQL)

DBMS_RANDOM¶

Descrição¶

O pacote DBMS_RANDOM fornece um gerador de números aleatórios integrado. O DBMS_RANDOM não se destina à criptografia. (Oracle PL/SQL DBMS_RANDOM)

Funções VALUE¶

Descrição¶

A função básica obtém um número aleatório, maior ou igual a 0 e menor que 1. Como alternativa, você pode obter um número Oracle aleatório X, em que X é maior ou igual a low e menor que high. (Oracle PL/SQL DBMS_RANDOM.VALUE)

Este UDF é implementado usando a função Math.random do Javascript para replicar a funcionalidade da função Oracle DBMS_RANDOM.VALUE.

Sintaxe¶

DBMS_RANDOM.VALUE()
    RETURN NUMBER;

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

Copy

Sobrecargas personalizadas de UDF¶

Dados de configuração¶

O esquema DBMS_RANDOM deve ser criado.

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

Parâmetros

Sem parâmetros.

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

Observação: O UDF suporta apenas aproximadamente entre 9 e 10 dígitos na parte decimal do número (9 ou 10 dígitos de precisão)

Exemplo de uso¶

Oracle

SELECT DBMS_RANDOM.VALUE() FROM DUAL;

Copy

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

Copy

Observação: A função pode ser chamada também de_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

Observação: No Snowflake, você deve colocar os parênteses.

DBMS_RANDOM.VALUE(NUMBER, NUMBER)¶

Parâmetros

low: O menor NUMBER a partir do qual um número aleatório é gerado. O número gerado é maior ou igual a low.
high: O NUMBER mais alto usado como limite ao gerar um número aleatório. O número gerado será menor que 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

Notas:

A função Oracle DBMS_RANDOM.VALUE(low, high) não exige que os parâmetros tenham uma ordem específica, portanto, o Snowflake UDF é implementado para oferecer suporte a esse recurso, sempre retirando o número mais alto e o mais baixo.
O UDF só suporta aproximadamente entre 9 e 10 dígitos na parte decimal do número (9 ou 10 dígitos de precisão).

Exemplo de uso¶

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

Problemas conhecidos¶

Não foram encontrados problemas.

DBMS_OUTPUT¶

Descrição¶

O pacote DBMS_OUTPUT é especialmente útil para exibir informações de depuração PL/SQL. (Oracle PL/SQL DBMS_OUTPUT)

Procedimento PUT_LINE¶

Descrição¶

Esse procedimento coloca uma linha no buffer. (Oracle PL/SQL DBMSOUTPUT.PUT_LINE)

Esse UDF é implementado usando uma tabela temporária para inserir os dados a serem exibidos para replicar a funcionalidade da função DBMS_OUTPUT.PUT_LINE do Oracle.

Sintaxe¶

DBMS_OUTPUT.PUT_LINE(LOG VARCHAR);

Copy

Procedimento personalizado¶

Dados de configuração¶

O esquema DBMS_OUTPUT deve ser criado.

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

Parâmetros

LOG: Item em um buffer que você deseja exibir.

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

Nota:

Observe que isso está usando uma tabela temporária; se você quiser que os dados persistam após o término de uma sessão, remova TEMPORARY de CREATE TABLE.
As tabelas temporárias armazenam dados transitórios não permanentes. Elas só existem na sessão em que foram criadas e persistem apenas durante o restante da sessão. Após o término da sessão, os dados armazenados na tabela são completamente removidos do sistema e, portanto, não podem ser recuperados, nem pelo usuário que criou a tabela nem pelo Snowflake.

Se você não usar a tabela temporária, lembre-se de que pode precisar de outra coluna na tabela em que o USER que executa DBMS_OUTPUT.PUT_LINE UDF for inserido para evitar confusão.

Exemplo de uso¶

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

Problemas conhecidos¶

O código UDF permanecerá comentado porque pode afetar o desempenho; se o usuário decidir usá-lo, basta descomentar o código.
O usuário pode modificar o UDF para que as informações necessárias sejam inseridas na tabela DBMS_OUTPUT.PUT_LINE.

EWIs relacionados¶

SSC-FDM-OR0035: Verifique a implementação do UDF para DBMS_OUTPUT.PUT_LINE_UDF.

DBMS_LOB¶

Descrição¶

O pacote DBMS_LOB fornece subprogramas para operar em BLOBs, CLOBs, NCLOBs, BFILEs e LOBs temporários. Você pode usar DBMS_LOB para acessar e manipular partes específicas de um LOB ou um LOBs completo. (Oracle PL/SQL DBMS_LOB)

Função SUBSTR¶

Descrição¶

Essa função retorna a quantidade de bytes ou caracteres de um LOB, a partir de um deslocamento absoluto do início do LOB. (Oracle PL/SQL DBMS_LOB.SUBSTR)

Essa função interna foi substituída pela função SUBSTR do Snowflake. No entanto, há algumas diferenças.

Observação: Os parâmetros amount e offset são invertidos no Snowflake

Sintaxe¶

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

Sobrecargas de funções¶

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

Exemplo de uso

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

Exemplo de uso

Observação: Os valores de resultado no Oracle e no Snowflake estão sendo convertidos de bytes para cadeia de caracteres para facilitar a compreensão da função.

Para o Snowflake, considere a possibilidade de usar:

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

e para Oracle considere usar:

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

para obter o resultado como uma cadeia de caracteres.

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

Observação: UTL_RAW.CAST_TO_RAW() não está sendo transformado atualmente em TO_BINARY(). A função é usada para mostrar a equivalência funcional do exemplo.

DBMS_LOB.SUBSTR(CLOB, amount, offset)¶

Exemplo de uso

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

Observação: UTL_RAW.CAST_TO_RAW() não está sendo transformado atualmente em TO_BINARY(). A função é usada para mostrar a equivalência funcional do exemplo.

DBMS_LOB.SUBSTR(BFILE, amount, offset)¶

Exemplo de uso

Usar DBMS_LOB.SUBSTR() em uma coluna BFILE retorna uma substring do conteúdo do arquivo.

O próximo exemplo não é uma migração atual, mas um exemplo funcional para mostrar as diferenças da função SUBSTR nos tipos BFILE.

Conteúdo do arquivo (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

Colunas BFILE são convertidas em colunas VARCHAR, portanto, a aplicação de uma função SUBSTR na mesma coluna retornaria uma substring do nome do arquivo, não o conteúdo do arquivo.

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

Problemas conhecidos¶

1. Using DBMS_LOB.SUBSTR with BFILE columns¶

A transformação atual para os tipos de dados BFILE em colunas é VARCHAR, em que o nome do arquivo é armazenado como uma cadeia de caracteres. Portanto, a aplicação da função SUBSTR em uma coluna BFILE após a transformação retornará uma substring do nome do arquivo, enquanto o Oracle retornaria uma substring do conteúdo do arquivo.

EWIs relacionados¶

SSC-EWI-OR0076: Pacote incorporado não suportado.
SSC-FDM-OR0035: DBMS_OUTPUT.PUTLINE verifique a implementação de UDF.

UTL_FILE¶

Descrição¶

Com o pacote UTL_FILE, os programas PL/SQL podem ler e gravar arquivos de texto. (Oracle PL/SQL UTL_FILE)

Procedimento FCLOSE¶

Descrição¶

Esse procedimento fecha um arquivo aberto identificado por um identificador de arquivo. (Oracle PL/SQL UTL_FILE.FCLOSE)

Esse procedimento é implementado usando o Snowflake STAGE para armazenar os arquivos de texto gravados.

Esse procedimento deve ser usado em conjunto com:

procedimento UTL_FILE.FOPEN

Sintaxe¶

UTL_FILE.FCLOSE(
    FILE VARCHAR
    );

Copy

Dados de configuração¶

O esquema UTL_FILE deve ser criado.

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

Copy

Se você quiser fazer o download do arquivo, execute o seguinte comando.

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

Copy

O comando GET é executado no Snowflake CLI.

Sobrecargas de procedimentos personalizados¶

UTL_FILE.FCLOSE(VARCHAR)¶

Parâmetros

FILE: O manipulador de arquivos ativo retornou da chamada para UTL_FILE.FOPEN

Funcionalidade

Esse procedimento usa a tabela FOPEN_TABLES_LINES criada no procedimento UTL_FILE.FOPEN.

Esse procedimento grava no estágio do utlfile_local_directory todas as linhas com o mesmo FHANDLE do arquivo em 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

Nota:

Observe que esse procedimento usa o estágio que foi criado anteriormente. Por enquanto, se você quiser gravar o arquivo em outro estágio, deverá modificar o nome.
Esses procedimentos são implementados para os estágios internos no COPY INTO

Exemplo de uso¶

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

Para executar esse exemplo, consulte 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

Problemas conhecidos¶

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

O usuário pode modificar o procedimento se for necessário alterar o nome do estágio.

2. Location static.¶

O local usado para gravar nesse procedimento é estático. Espera-se que uma nova versão do procedimento aumente sua extensibilidade usando o local que tem o parâmetro FILE.

5. Files supported.¶

Por enquanto, esse procedimento só grava arquivos .CSV.

EWIs relacionados¶

SSC-FDM-0015: Tipo de dados não reconhecido.
SSC-FDM-OR0036: Parâmetros de pacotes incorporados desnecessários.

Procedimento FOPEN¶

Descrição¶

Esse procedimento abre um arquivo. (Oracle PL/SQL UTL_FILE.FOPEN)

Esse procedimento é implementado usando o Snowflake STAGE para armazenar os arquivos de texto.

O usuário é responsável por fazer o upload dos arquivos locais para o STAGE para serem usados pelo procedimento.

Esse procedimento deve ser usado em conjunto com:

procedimento UTL_FILE.FCLOSE

Sintaxe¶

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

Copy

Dados de configuração¶

O esquema UTL_FILE deve ser criado.

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

Copy

Crie o estágio 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

Se o valor no parâmetro OPEN_MODE for w ou r, será necessário carregar o arquivo no diretório utlfile_local_directory.

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

Copy

O comando PUT é executado no Snowflake CLI.

Sobrecargas de procedimentos personalizados¶

UTL_FILE.FOPEN( VARCHAR, VARCHAR)¶

Parâmetros

FILENAME: O nome do arquivo, incluindo a extensão**.**
OPEN_MODE: Especifica como o arquivo é aberto.

Modos abertos

O procedimento do pacote incorporado Oracle UTL_FILE.FOPEN suporta seis modos de como abrir o arquivo, mas somente três deles são suportados no procedimento 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

Funcionalidade

Esse procedimento usa duas tabelas com as quais a operação de abertura de um arquivo será emulada. A tabela FOPEN_TABLES armazenará os arquivos que estão abertos e a tabela FOPEN_TABLES_LINES armazena as linhas que cada arquivo possui.

Se o arquivo for aberto no modo de gravação, um novo arquivo será criado; se for aberto no modo de leitura ou anexação, ele carregará as linhas do arquivo em FOPEN_TABLES_LINES e inserirá o arquivo em 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

Nota:

Observe que esse procedimento usa o estágio que foi criado anteriormente. Por enquanto, se você quiser usar outro nome para o estágio, deverá modificar o procedimento.
Esses procedimentos são implementados para os estágios internos no COPY INTO

Exemplo de uso¶

Oracle

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

Copy

Para executar esse exemplo, consulte 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

Problemas conhecidos¶

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

O usuário pode modificar o procedimento se for necessário alterar o nome do estágio.

2. `LOCATION` parameter is not used.¶

O parâmetro LOCATION não é usado agora porque o estágio usado no procedimento é estático. Está planejado para uma versão atualizada do procedimento aumentar sua extensibilidade usando esse parâmetro para inserir o nome do estágio em que o arquivo que você deseja abrir está localizado.

3. `MAX_LINESIZE` parameter is not used¶

O procedimento do pacote incorporado Oracle UTL_FILE.FOPEN tem o parâmetro MAX_LINESIZE, mas no procedimento Snowscripting ele é removido porque não é usado.

4. `OPEN_MODE` values supported¶

Esse procedimento é compatível com os modos write (w), read (r) e append (a) para abrir arquivos.

5. Files supported¶

Por enquanto, esse procedimento é compatível apenas com arquivos .CSV.

EWIs relacionados¶

SSC-FDM-0015: Tipo de dados não reconhecido.
SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters

Procedimento PUT_LINE¶

Descrição¶

Esse procedimento grava a cadeia de caracteres de texto armazenada no parâmetro buffer no arquivo aberto identificado pelo identificador de arquivo. (Oracle PL/SQL UTL_FILE.PUT_LINE)

Esse procedimento deve ser usado em conjunto com:

procedimento UTL_FILE.FOPEN
procedimento UTL_FILE.FCLOSE

Sintaxe¶

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

Copy

Dados de configuração¶

O esquema UTL_FILE deve ser criado.

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

Copy

UDF personalizada¶

UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)¶

Parâmetros

FILE: O manipulador de arquivos ativo retornou da chamada para UTL_FILE.FOPEN
BUFFER: Buffer de texto que contém o texto a ser gravado no arquivo**.**

Funcionalidade

Esse procedimento usa a tabela FOPEN_TABLES_LINES criada no procedimento UTL_FILE.FOPEN.

Se o OPEN_MODE do arquivo for write (w) ou append (a), ele insere o buffer em FOPEN_TABLES_LINES, mas se o OPEN_MODE for leitura (r), ele lança a exceção File_is_read_only.

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

Nota:

Para usar esse procedimento, você deve abrir o arquivo com UTL_FILE.FOPEN

Exemplo de uso¶

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

Para executar esse exemplo, consulte 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

Problemas conhecidos¶

1. `AUTOFLUSH` parameter is not used.¶

O procedimento do pacote incorporado Oracle UTL_FILE.PUT_LINE tem o parâmetro AUTOFLUSH, mas no procedimento Snowscripting ele é removido porque não é usado.

EWIs relacionados¶

SSC-FDM-0015: Tipo de dados não reconhecido.
SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters

SnowConvert: Pacotes incorporados do Oracle¶

Descrição¶

DBMS_RANDOM¶

Descrição¶

Funções VALUE¶

Descrição¶

Sintaxe¶

Sobrecargas personalizadas de UDF¶

Dados de configuração¶

DBMS_RANDOM.VALUE()¶

Exemplo de uso¶

DBMS_RANDOM.VALUE(NUMBER, NUMBER)¶

Exemplo de uso¶

Problemas conhecidos¶

EWIs relacionados¶

DBMS_OUTPUT¶

Descrição¶

Procedimento PUT_LINE¶

Descrição¶

Sintaxe¶

Procedimento personalizado¶

Dados de configuração¶

DBMS_OUTPUT.PUT_LINE(VARCHAR)¶

Exemplo de uso¶

Problemas conhecidos¶

EWIs relacionados¶

DBMS_LOB¶

Descrição¶

Função SUBSTR¶

Descrição¶

Sintaxe¶

Sobrecargas de funções¶

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

Problemas conhecidos¶

1. Using DBMS_LOB.SUBSTR with BFILE columns¶

EWIs relacionados¶

UTL_FILE¶

Descrição¶

Procedimento FCLOSE¶

Descrição¶

Sintaxe¶

Dados de configuração¶

Sobrecargas de procedimentos personalizados¶

UTL_FILE.FCLOSE(VARCHAR)¶

Exemplo de uso¶

Problemas conhecidos¶

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

2. Location static.¶

5. Files supported.¶

EWIs relacionados¶

Procedimento FOPEN¶

Descrição¶

Sintaxe¶

Dados de configuração¶

Sobrecargas de procedimentos personalizados¶

UTL_FILE.FOPEN( VARCHAR, VARCHAR)¶

Exemplo de uso¶

Problemas conhecidos¶

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

Procedimento PUT_LINE¶

Descrição¶

Sintaxe¶

Dados de configuração¶

UDF personalizada¶

UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)¶

Exemplo de uso¶

Problemas conhecidos¶

1. AUTOFLUSH parameter is not used.¶

EWIs relacionados¶

2. `LOCATION` parameter is not used.¶

3. `MAX_LINESIZE` parameter is not used¶

4. `OPEN_MODE` values supported¶

1. `AUTOFLUSH` parameter is not used.¶