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;
사용자 정의 UDF 오버로드¶
설정 데이터¶
DBMS_RANDOM
스키마를 생성해야 합니다.
CREATE SCHEMA IF NOT EXISTS DBMS_RANDOM
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
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();
$$;
참고: UDF 는 소수점 이하 9자리~10자리의 숫자만 지원합니다(전체 자릿수 9자리 또는 10자리)
사용법 예¶
Oracle
SELECT DBMS_RANDOM.VALUE() FROM DUAL;
|DBMS_RANDOM.VALUE() |
|--------------------------------------------|
|0.47337471168356406022193430290380483126 |
참고: 이 함수는 _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;
|DBMS_RANDOM.VALUE() |
|--------------------|
|0.1014560867 |
참고: 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);
$$;
참고:
Oracle DBMS_RANDOM.VALUE(low, high) 함수는 매개 변수에 특정 순서가 필요하지 않으므로 항상 가장 높은 수와 가장 낮은 수를 꺼내서 이 함수를 지원하기 위해 Snowflake UDF 를 구현했습니다.
UDF 는 소수점 이하 9자리~10자리의 숫자만 지원합니다(전체 자릿수 9자리 또는 10자리).
사용법 예¶
Oracle
SELECT DBMS_RANDOM.VALUE(-10,30) FROM DUAL;
|DBMS_RANDOM.VALUE(-10,30) |
|--------------------------------------------|
|16.0298681859960167648070354679783928085 |
Snowflake
SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF(-10,30) FROM DUAL;
|DBMS_RANDOM.VALUE(-10,30) |
|----------------------------|
|-6.346055187 |
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);
사용자 지정 프로시저¶
설정 데이터¶
DBMS_OUTPUT
스키마를 생성해야 합니다.
CREATE SCHEMA IF NOT EXISTS DBMS_OUTPUT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
DBMS_OUTPUT.PUT_LINE(VARCHAR)¶
매개 변수
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;
$$;
참고:
임시 테이블을 사용하고 있으므로 세션이 종료된 후에도 데이터를 유지하려면 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();
|DBMS_OUTPUT.PUT_LINE('test') |
|-----------------------------|
|test |
Snowflake
CREATE OR REPLACE PROCEDURE PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF('Test');
END;
$$;
CALL PROC();
|ROW |WHEN |DATABASE |LOG |
|----|------------------------|------------|---------|
| 1 |2022-04-25 11:16:23.844 |CODETEST |test |
Known Issues¶
UDF 코드는 성능에 영향을 줄 수 있으므로 주석 처리된 상태로 유지되며, 사용자가 사용하기로 결정하면 코드의 주석 처리를 해제하면 됩니다.
사용자는 DBMS_OUTPUT.PUT_LINE 테이블에 필요한 정보가 삽입되도록 UDF 를 수정할 수 있습니다.
관련 EWIs¶
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 함수 로 대체됩니다. 하지만 몇 가지 차이점이 있습니다.
참고: amount 및 offset 매개 변수는 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;
함수 오버로드¶
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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Snowflake
SELECT
-- 1. "some magic here"
SUBSTR('some magic here', 1, 15) "1",
-- 2. "some"
SUBSTR('some magic here', 1, 4) "2",
-- 3. "me magic here"
SUBSTR('some magic here', 3, 15) "3",
-- 4. "magic"
SUBSTR('some magic here', 6, 5) "4",
-- 5. "here"
SUBSTR('some magic here', 12, 20) "5",
-- 6. " "
SUBSTR('some magic here', 16, 250) "6"
FROM DUAL;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
DBMS_LOB.SUBSTR(B LOB, amount, offset)¶
사용법 예
참고: 함수를 더 쉽게 이해할 수 있도록 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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Snowflake
-- Create Table
CREATE OR REPLACE TABLE blobtable ( blob_column BINARY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
-- Insert sample value
INSERT INTO blobtable
VALUES (
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'utl_raw.cast_to_raw' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS cast_to_raw);
-- Select different examples
SELECT
-- 1. "some magic here"
SUBSTR(blob_column, 1, 15) "1",
-- 2. "some"
SUBSTR(blob_column, 1, 4) "2",
-- 3. "me magic here"
SUBSTR(blob_column, 3, 15) "3",
-- 4. "magic"
SUBSTR(blob_column, 6, 5) "4",
-- 5. "here"
SUBSTR(blob_column, 12, 20) "5",
-- 6. " "
SUBSTR(blob_column, 16, 250) "6"
FROM
BLOBTABLE;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
참고: 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;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
Snowflake
-- Create Table
CREATE OR REPLACE TABLE clobtable (clob_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
-- Insert sample value
INSERT INTO clobtable
VALUES ('some magic here');
-- Select
SELECT
-- 1. "some magic here"
SUBSTR(clob_column, 1, 15) "1",
-- 2. "some"
SUBSTR(clob_column, 1, 4) "2",
-- 3. "me magic here"
SUBSTR(clob_column, 3, 15) "3",
-- 4. "magic"
SUBSTR(clob_column, 6, 5) "4",
-- 5. "here"
SUBSTR(clob_column, 12, 20) "5",
-- 6. " "
SUBSTR(clob_column, 16, 250) "6"
FROM
clobtable;
1 |2 |3 |4 |5 |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |
참고: 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
Oracle
CREATE OR REPLACE PROCEDURE bfile_substr_procedure
IS
fil BFILE := BFILENAME('MY_DIR', 'file.txt');
BEGIN
DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(fil,9,1)));
--Console Output:
-- "some magi"
DBMS_LOB.FILECLOSE(fil);
END;
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(fil,4,1))) |
-------------------------------------------------------------------------|
some magic |
Snowflake
BFILE 열은 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;
$$;
SUBSTR(bfile_column, 1, 9) |
---------------------------|
MY_DIR\fi |
Known Issues¶
1. Using DBMS_LOB.SUBSTR with BFILE columns¶
열의 BFILE 데이터 타입에 대한 현재 변환은 파일 이름이 문자열로 저장되는 VARCHAR 입니다. 따라서 변환 후 BFILE 열에 SUBSTR 함수를 적용하면 파일 이름의 하위 문자열이 반환되는 반면, Oracle은 파일 내용의 하위 문자열을 반환합니다.
관련 EWIs¶
SSC-EWI-OR0076: 기본 제공 패키지는 지원되지 않습니다.
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.FOPEN
프로시저
구문¶
UTL_FILE.FCLOSE(
FILE VARCHAR
);
설정 데이터¶
UTL_FILE
스키마를 생성해야 합니다.
CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
파일을 다운로드하려면 다음 명령을 실행하십시오.
GET @UTL_FILE.utlfile_local_directory/<filename> file://<path_to_file>/<filename>;
GET 명령은 Snowflake CLI 에서 실행됩니다.
사용자 지정 프로시저 오버로드¶
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 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;
이 예제를 실행하려면 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;
Known Issues¶
1. Modify the procedure for changing the name of the stage.¶
스테이지의 이름을 변경해야 하는 경우 사용자는 프로시저를 수정할 수 있습니다.
2. Location static.¶
이 프로시저에 쓰는 데 사용되는 위치는 정적입니다. 새 버전의 프로시저는 FILE
매개 변수가 있는 위치를 사용하여 확장성을 높일 것으로 예상됩니다.
5. Files supported.¶
현재 이 프로시저는 .CSV 파일만 씁니다.
관련 EWIs¶
SSC-FDM-0015: 데이터 타입이 인식되지 않습니다.
SSC-FDM-OR0036: 불필요한 기본 제공 패키지 매개 변수.
FOPEN 프로시저¶
설명¶
이 프로시저는 파일을 엽니다. (Oracle PL/SQL UTL_FILE.FOPEN)
이 프로시저는 Snowflake STAGE 를 사용하여 텍스트 파일을 저장합니다.
사용자는 프로시저에서 사용할 로컬 파일을 STAGE 에 업로드해야 합니다.
이 프로시저는 다음과 함께 사용해야 합니다.
UTL_FILE.FCLOSE
프로시저
구문¶
UTL_FILE.FOPEN(
LOCATION VARCHAR,
FILENAME VARCHAR,
OPEN_MODE VARCHAR,
MAX_LINESIZE NUMBER,
);
설정 데이터¶
UTL_FILE
스키마를 생성해야 합니다.
CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
스테이지
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;
OPEN_MODE
매개 변수의 값이 w 또는 r 인 경우utlfile_local_directory
에 파일을 업로드해야 합니다.
PUT file://<path_to_file>/<filename> @UTL_FILE.utlfile_local_directory auto_compress=false;
PUT 명령은 Snowflake CLI 에서 실행됩니다.
사용자 지정 프로시저 오버로드¶
UTL_FILE.FOPEN( VARCHAR, VARCHAR)¶
매개 변수
FILENAME: 확장자**.** 를 포함한 파일 이름
OPEN_MODE: 파일을 여는 방법을 지정합니다.
오픈 모드
Oracle 내장 패키지 UTL_FILE.FOPEN
프로시저는 파일을 여는 방법의 6가지 모드를 지원하지만, Snowscripting 프로시저는 그 중 3가지 모드만 지원합니다.
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 |
기능
이 프로시저는 파일 열기 작업을 에뮬레이션할 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 INTO
의 내부 스테이지에 대해 구현됩니다.
사용법 예¶
Oracle
DECLARE
w_file UTL_FILE.FILE_TYPE;
BEGIN
w_file:= UTL_FILE.FOPEN('MY_DIR','test.csv','w',1024);
END;
이 예제를 실행하려면 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;
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¶
SSC-FDM-0015: 데이터 타입이 인식되지 않습니다.
SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters
PUT_LINE 프로시저¶
설명¶
이 프로시저는 버퍼 매개 변수에 저장된 텍스트 문자열을 파일 핸들로 식별된 열린 파일에 씁니다. (Oracle PL/SQL UTL_FILE.PUT_LINE)
이 프로시저는 다음과 함께 사용해야 합니다.
UTL_FILE.FOPEN
프로시저UTL_FILE.FCLOSE
프로시저
구문¶
UTL_FILE.PUT_LINE(
FILE VARCHAR,
BUFFER VARCHAR,
);
설정 데이터¶
UTL_FILE
스키마를 생성해야 합니다.
CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
사용자 지정 UDF¶
UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)¶
매개 변수
FILE:
UTL_FILE.FOPEN
로의 호출에서 반환된 활성 파일 처리기BUFFER: 파일에 기록할 텍스트가 들어있는 텍스트 버퍼**.**
기능
이 프로시저는 UTL_FILE.FOPEN
프로시저에서 만든 FOPEN_TABLES_LINES
테이블을 사용합니다.
파일의 OPEN_MODE
가 write(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;
참고:
이 프로시저를 사용하려면 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;
이 예제를 실행하려면 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;
Known Issues¶
1. AUTOFLUSH
parameter is not used.¶
Oracle 내장 패키지 UTL_FILE.PUT_LINE
프로시저에는 AUTOFLUSH
매개 변수가 있지만, Snowscripting 프로시저에서는 사용되지 않으므로 제거됩니다.
관련 EWIs¶
SSC-FDM-0015: 데이터 타입이 인식되지 않습니다.
SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters