SnowConvert: Oracle 내장 패키지

설명

Oracle은 데이터베이스 기능을 확장하고 SQL 기능에 대한 PL/SQL 액세스를 제공하기 위해 Oracle 서버와 함께 다양한 PL/SQL 패키지를 공급합니다. (Oracle PL/SQL Built-in Packages)

DBMS_RANDOM

설명

DBMS_RANDOM 패키지는 내장 난수 생성기를 제공합니다. DBMS_RANDOM 은 암호화용이 아닙니다. (Oracle PL/SQL DBMS_RANDOM)

VALUE 함수

설명

기본 함수는 0보다 크거나 1보다 작은 임의의 숫자를 가져옵니다. 또는 임의의 Oracle 번호 X 를 얻을 수 있습니다. 여기서 X낮음 보다 크거나 같고 높음 보다 작은 값입니다. (Oracle PL/SQL DBMS_RANDOM.VALUE)

이 UDF 는 Javascript의 Math.random 함수를 사용해 구현되어 Oracle DBMS_RANDOM.VALUE 함수의 기능을 복제합니다.

구문
DBMS_RANDOM.VALUE()
    RETURN NUMBER;

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

사용자 정의 UDF 오버로드

설정 데이터

DBMS_RANDOM 스키마를 생성해야 합니다.

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

매개 변수

  • 매개 변수가 없습니다.

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

참고: UDF 는 소수점 이하 9자리~10자리의 숫자만 지원합니다(전체 자릿수 9자리 또는 10자리)

사용법 예

Oracle

SELECT DBMS_RANDOM.VALUE() FROM DUAL;
Copy
|DBMS_RANDOM.VALUE()                         |
|--------------------------------------------|
|0.47337471168356406022193430290380483126    |
Copy

참고: 이 함수는 _DBMS_RANDOM.VALUE()_ 또는 _ 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

참고: Snowflake에서는 괄호를 넣어야 합니다.

DBMS_RANDOM.VALUE(NUMBER, NUMBER)

매개 변수

  • low: 난수가 생성되는 가장 낮은 NUMBER 입니다. 생성되는 숫자는 low 보다 크거나 같습니다.

  • high: 난수 생성 시 제한값으로 사용되는 가장 높은 NUMBER 입니다. 생성되는 숫자는 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

참고:

  • Oracle DBMS_RANDOM.VALUE(low, high) 함수는 매개 변수에 특정 순서가 필요하지 않으므로 항상 가장 높은 수와 가장 낮은 수를 꺼내서 이 함수를 지원하기 위해 Snowflake UDF 를 구현했습니다.

  • UDF 는 소수점 이하 9자리~10자리의 숫자만 지원합니다(전체 자릿수 9자리 또는 10자리).

사용법 예

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

Known Issues

문제가 발견되지 않았습니다.

DBMS_OUTPUT

설명

DBMS_OUTPUT 패키지는 PL/SQL 디버깅 정보를 표시하는 데 특히 유용합니다. (Oracle PL/SQL DBMS_OUTPUT)

PUT_LINE 프로시저

설명

이 프로시저는 버퍼에 줄을 배치합니다. (Oracle PL/SQL DBMSOUTPUT.PUT_LINE)

이 UDF 는 임시 테이블을 사용하여 표시할 데이터를 삽입하여 Oracle DBMS_OUTPUT.PUT_LINE 함수의 기능을 복제하도록 구현됩니다.

구문
DBMS_OUTPUT.PUT_LINE(LOG VARCHAR);
Copy

사용자 지정 프로시저

설정 데이터

DBMS_OUTPUT 스키마를 생성해야 합니다.

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)

매개 변수

  • LOG: 표시하려는 버퍼의 항목입니다.

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

참고:

  • 임시 테이블을 사용하고 있으므로 세션이 종료된 후에도 데이터를 유지하려면 CREATE TABLE 에서 TEMPORARY 를 제거하십시오.

  • 임시 테이블 은 비영구적인 일시적 데이터를 저장합니다. 생성된 세션 내에서만 존재하며 나머지 세션 동안만 유지됩니다. 세션이 종료되면 테이블에 저장된 데이터는 시스템에서 완전히 제거되므로 테이블을 만든 사용자나 Snowflake에서 복구할 수 없습니다.

임시 테이블을 사용하지 않는 경우 혼동을 피하기 위해 USER 실행 DBMS_OUTPUT.PUT_LINE UDF 를 삽입하는 테이블에 다른 열이 필요할 수 있다는 점에 유의하십시오.

사용법 예

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

Known Issues

  • UDF 코드는 성능에 영향을 줄 수 있으므로 주석 처리된 상태로 유지되며, 사용자가 사용하기로 결정하면 코드의 주석 처리를 해제하면 됩니다.

  • 사용자는 DBMS_OUTPUT.PUT_LINE 테이블에 필요한 정보가 삽입되도록 UDF 를 수정할 수 있습니다.

관련 EWIs

  1. SSC-FDM-OR0035: DBMS_OUTPUT.PUT_LINE_UDF 의 UDF 구현 확인.

DBMS_LOB

설명

DBMS_LOB 패키지는 BLOBs, CLOBs, NCLOBs, BFILEs 및 임시 LOBs 에서 작업할 수 있는 하위 프로그램을 제공합니다. DBMS_LOB 를 사용하여 LOB 의 특정 부분에 액세스하고 조작하거나 LOBs 를 완성할 수 있습니다. (Oracle PL/SQL DBMS_LOB)

SUBSTR 함수

설명

이 함수는 LOB 의 시작 부분에서 절대 offset 으로 시작하여 LOB 의 amount 바이트 또는 문자를 반환합니다 (Oracle PL/SQL DBMS_LOB.SUBSTR)

이 기본 제공 함수는 Snowflake SUBSTR 함수 로 대체됩니다. 하지만 몇 가지 차이점이 있습니다.

참고: amountoffset 매개 변수는 Snowflake에서 반전됩니다

구문
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

함수 오버로드

DBMS_LOB.SUBSTR(‘string’, amount, offset)

사용법 예

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)

사용법 예

참고: 함수를 더 쉽게 이해할 수 있도록 Oracle 및 Snowflake의 결과 값을 바이트에서 문자열로 변환하고 있습니다.

Snowflake 의 경우 다음을 사용하는 것이 좋습니다.

hex_decode_string( to_varchar(SUBSTR(blob_column, 1, 6), ‘HEX’));

Oracle 의 경우 다음을 사용하는 것이 좋습니다.

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

결과를 문자열로 가져옵니다.

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

참고: UTL_RAW.CAST_TO_RAW() 는 현재 TO_BINARY() 로 변환되지 않습니다. 이 함수는 예제의 기능적 동등성을 보여주기 위해 사용됩니다.

DBMS_LOB.SUBSTR(CLOB, amount, offset)

사용법 예

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

참고: UTL_RAW.CAST_TO_RAW() 는 현재 TO_BINARY() 로 변환되지 않습니다. 이 함수는 예제의 기능적 동등성을 보여주기 위해 사용됩니다.

DBMS_LOB.SUBSTR(BFILE, amount, offset)

사용법 예

DBMS_LOB.SUBSTR() 을 BFILE 열에 사용하면 파일 내용의 하위 문자열이 반환됩니다.

다음 예제는 현재 마이그레이션이 아니라 BFILE 유형에 대한 SUBSTR 함수의 차이점을 보여주기 위한 함수 예제입니다.

File Content (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 열은 VARCHAR 열로 변환되므로 같은 열에 SUBSTR 함수를 적용하면 파일 내용이 아닌 파일 이름의 하위 문자열이 반환됩니다.

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

Known Issues

1. Using DBMS_LOB.SUBSTR with BFILE columns

열의 BFILE 데이터 타입에 대한 현재 변환은 파일 이름이 문자열로 저장되는 VARCHAR 입니다. 따라서 변환 후 BFILE 열에 SUBSTR 함수를 적용하면 파일 이름의 하위 문자열이 반환되는 반면, Oracle은 파일 내용의 하위 문자열을 반환합니다.

관련 EWIs

  1. SSC-EWI-OR0076: 기본 제공 패키지는 지원되지 않습니다.

  2. SSC-FDM-OR0035: DBMS_OUTPUT.PUTLINE UDF 구현 확인.

UTL_FILE

설명

UTL_FILE 패키지를 사용하면 PL/SQL 프로그램이 텍스트 파일을 읽고 쓸 수 있습니다. (Oracle PL/SQL UTL_FILE)

FCLOSE 프로시저

설명

이 프로시저는 파일 핸들로 식별된 열린 파일을 닫습니다. (Oracle PL/SQL UTL_FILE.FCLOSE)

이 프로시저는 작성된 텍스트 파일을 저장하기 위해 Snowflake STAGE 를 사용하여 구현됩니다.

이 프로시저는 다음과 함께 사용해야 합니다.

구문
UTL_FILE.FCLOSE(
    FILE VARCHAR
    );
Copy
설정 데이터
  • UTL_FILE 스키마를 생성해야 합니다.

CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
Copy
  • 파일을 다운로드하려면 다음 명령을 실행하십시오.

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

사용자 지정 프로시저 오버로드

UTL_FILE.FCLOSE(VARCHAR)

매개 변수

  • FILE: UTL_FILE.FOPEN로의 호출에서 반환된 활성 파일 처리기

기능

이 프로시저는 UTL_FILE.FOPEN 프로시저에서 만든 FOPEN_TABLES_LINES 테이블을 사용합니다.

이 프로시저는 FOPEN_TABLES_LINES 에 있는 파일에서 FHANDLE 같은 모든 라인을 utlfile_local_directory 스테이지에 씁니다.

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

참고:

  • 이 프로시저는 이전에 생성한 스테이지 를 사용합니다. 지금은 다른 스테이지에서 파일을 작성하려면 이름을 수정해야 합니다.

  • 이 프로시저는 COPY INTO의 내부 스테이지에 대해 구현됩니다.

사용법 예

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

이 예제를 실행하려면 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

Known Issues

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

스테이지의 이름을 변경해야 하는 경우 사용자는 프로시저를 수정할 수 있습니다.

2. Location static.

이 프로시저에 쓰는 데 사용되는 위치는 정적입니다. 새 버전의 프로시저는 FILE 매개 변수가 있는 위치를 사용하여 확장성을 높일 것으로 예상됩니다.

5. Files supported.

현재 이 프로시저는 .CSV 파일만 씁니다.

관련 EWIs

  1. SSC-FDM-0015: 데이터 타입이 인식되지 않습니다.

  2. SSC-FDM-OR0036: 불필요한 기본 제공 패키지 매개 변수.

FOPEN 프로시저

설명

이 프로시저는 파일을 엽니다. (Oracle PL/SQL UTL_FILE.FOPEN)

이 프로시저는 Snowflake STAGE 를 사용하여 텍스트 파일을 저장합니다.

사용자는 프로시저에서 사용할 로컬 파일을 STAGE 에 업로드해야 합니다.

이 프로시저는 다음과 함께 사용해야 합니다.

구문
UTL_FILE.FOPEN(
    LOCATION VARCHAR,
    FILENAME VARCHAR,
    OPEN_MODE VARCHAR,
    MAX_LINESIZE NUMBER,
    );
Copy
설정 데이터
  • UTL_FILE 스키마를 생성해야 합니다.

CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
Copy
  • 스테이지 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
  • OPEN_MODE 매개 변수의 값이 w 또는 r 인 경우 utlfile_local_directory 에 파일을 업로드해야 합니다.

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

사용자 지정 프로시저 오버로드

UTL_FILE.FOPEN( VARCHAR, VARCHAR)

매개 변수

  • FILENAME: 확장자**.** 를 포함한 파일 이름

  • OPEN_MODE: 파일을 여는 방법을 지정합니다.

오픈 모드

Oracle 내장 패키지 UTL_FILE.FOPEN 프로시저는 파일을 여는 방법의 6가지 모드를 지원하지만, Snowscripting 프로시저는 그 중 3가지 모드만 지원합니다.

OPEN_MODEDESCRIPTIONSTATUS
wWrite modeSupported
aAppend modeSupported
rRead modeSupported
rbRead byte modeUnsupported
wbWrite byte modeUnsupported
abAppend byte modeUnsupported

기능

이 프로시저는 파일 열기 작업을 에뮬레이션할 2개의 테이블을 사용합니다. FOPEN_TABLES 테이블에는 열려 있는 파일이 저장되고 FOPEN_TABLES_LINES 테이블에는 각 파일이 소유하고 있는 라인이 저장됩니다.

파일을 쓰기 모드로 열면 새 파일이 생성되고, 읽기 또는 추가 모드로 열면 FOPEN_TABLES_LINES 에 있는 파일의 라인을 로드하여 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

참고:

  • 이 프로시저는 이전에 생성한 스테이지 를 사용합니다. 현재로서는 스테이지에 다른 이름을 사용하려면 프로시저를 수정해야 합니다.

  • 이 프로시저는 COPY INTO의 내부 스테이지에 대해 구현됩니다.

사용법 예

Oracle

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

이 예제를 실행하려면 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

Known Issues

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

스테이지의 이름을 변경해야 하는 경우 사용자는 프로시저를 수정할 수 있습니다.

2. LOCATION parameter is not used.

프로시저에 사용된 스테이지가 정적이므로 LOCATION 매개 변수는 현재 사용되지 않습니다. 이 매개 변수를 사용하여 열 파일이 있는 스테이지의 이름을 입력하여 확장성을 높일 수 있도록 프로시저의 업데이트 버전이 계획되어 있습니다.

3. MAX_LINESIZE parameter is not used

Oracle 내장 패키지 UTL_FILE.FOPEN 프로시저에는 MAX_LINESIZE 매개 변수가 있지만, Snowscripting 프로시저에서는 사용되지 않으므로 제거됩니다.

4. OPEN_MODE values supported

이 프로시저는 파일을 열기 위한 write (w), read (r) 및 append (a) 모드를 지원합니다.

5. Files supported

현재 이 프로시저는 .CSV 파일만 지원합니다.

관련 EWIs

  1. SSC-FDM-0015: 데이터 타입이 인식되지 않습니다.

  2. SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters

PUT_LINE 프로시저

설명

이 프로시저는 버퍼 매개 변수에 저장된 텍스트 문자열을 파일 핸들로 식별된 열린 파일에 씁니다. (Oracle PL/SQL UTL_FILE.PUT_LINE)

이 프로시저는 다음과 함께 사용해야 합니다.

구문
UTL_FILE.PUT_LINE(
    FILE VARCHAR,
    BUFFER VARCHAR,
    );
Copy
설정 데이터
  • UTL_FILE 스키마를 생성해야 합니다.

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

사용자 지정 UDF

UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)

매개 변수

  • FILE: UTL_FILE.FOPEN로의 호출에서 반환된 활성 파일 처리기

  • BUFFER: 파일에 기록할 텍스트가 들어있는 텍스트 버퍼**.**

기능

이 프로시저는 UTL_FILE.FOPEN 프로시저에서 만든 FOPEN_TABLES_LINES 테이블을 사용합니다.

파일의 OPEN_MODEwrite(w) 또는 append(a)인 경우 FOPEN_TABLES_LINES 에 버퍼를 삽입하지만 OPEN_MODE 가 읽기 (r)인 경우 File_is_read_only 예외를 throw합니다.

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

참고:

  • 이 프로시저를 사용하려면 UTL_FILE.FOPEN로 파일을 열어야 합니다

사용법 예

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

이 예제를 실행하려면 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

Known Issues

1. AUTOFLUSH parameter is not used.

Oracle 내장 패키지 UTL_FILE.PUT_LINE 프로시저에는 AUTOFLUSH 매개 변수가 있지만, Snowscripting 프로시저에서는 사용되지 않으므로 제거됩니다.

관련 EWIs

  1. SSC-FDM-0015: 데이터 타입이 인식되지 않습니다.

  2. SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters