SnowConvert AI - Oracle - Oracle 기본 제공 데이터 타입¶

확장 데이터 타입¶

설명¶

Oracle 데이터베이스 12_c_부터 VARCHAR2, NVARCHAR2, RAW 데이터 타입에 대해 최대 32767바이트의 크기를 지정할 수 있습니다. 초기화 매개 변수 MAX_STRING_SIZE 를 설정하여 데이터베이스가 이 새로운 최대 크기를 지원하는지 여부를 제어할 수 있습니다.

선언된 크기가 4000바이트보다 큰 VARCHAR2 또는 NVARCHAR2 데이터 타입 또는 선언된 크기가 2000바이트보다 큰 RAW 데이터 타입은 확장 데이터 타입 입니다. (Oracle SQL Language Reference 확장 데이터 타입).

Oracle에서는 데이터베이스 최대 문자열 크기를 STANDARD 에서 EXTENDED 로 늘릴 수 있지만, Snowflake에는 이 기능에 해당하는 기능이 포함되어 있지 않습니다.

따라서 VARCHAR2, NVARCHAR2 및 RAW 확장 데이터 타입은 Snowflake에서 지원되지 않으며, 일반 VARCHAR2, NVARCHAR2 및 RAW 데이터 타입처럼 변환됩니다. 자세한 내용은 문자 데이터 타입 및 RAW 데이터 타입을 참조하세요.

Known Issues¶

1. MAX STRING SIZE not recognized¶

ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED';

SnowConvert 에 의해 구문 분석되지 않습니다.

JSON 데이터 타입¶

설명¶

Oracle 데이터베이스는 트랜잭션, 인덱스, 선언적 쿼리 및 뷰를 비롯한 관계형 데이터베이스 기능을 통해 JSON 을 기본적으로 지원합니다. 관계형 데이터와 달리 JSON 데이터는 데이터를 정의하는 스키마 없이도 데이터베이스에 저장하고, 인덱싱하고, 쿼리할 수 있습니다. (Oracle SQL Language Reference JSON 데이터 타입).

The JSON data types are transformed to VARIANT to emulate the Oracle behavior.

JSON

샘플 소스 패턴¶

Create Table의 JSON 데이터 타입을 열로 사용¶

Oracle¶

CREATE TABLE jsontable (
	json_column JSON
);

INSERT INTO jsontable VALUES('{"id": 1, "content":"json content"}');
INSERT INTO jsontable VALUES('{"stringdata": "this is a text","number": 1,"numberNeg": -1,"booleanT": true,"booleanGF": false,"nullvalue": null,"object": {"1": 1,"2": 2},"array": [1, 2, 3]}');
INSERT INTO jsontable VALUES(JSON('{"id": 4}'));
 
SELECT  * FROM jsontable;

결과¶


COL1
{“id”:1,”content”:”json content”}
{“stringdata”:”this is a text”,”number”:1,”numberNeg”:-1,”booleanT”:true,”booleanGF”:false,”nullvalue”:null,”object”:{“1”:1,”2”:2},”array”:[1,2,3]}
{“id”:4}

Snowflake¶

CREATE OR REPLACE TABLE jsontable (
	json_column VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO jsontable
VALUES('{"id": 1, "content":"json content"}');

INSERT INTO jsontable
VALUES('{"stringdata": "this is a text","number": 1,"numberNeg": -1,"booleanT": true,"booleanGF": false,"nullvalue": null,"object": {"1": 1,"2": 2},"array": [1, 2, 3]}');

INSERT INTO jsontable
VALUES(JSON('{"id": 4}') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'JSON' NODE ***/!!!);

SELECT  * FROM
	jsontable;

경고

JSON 데이터 삽입이 올바르게 처리되지 않습니다. 해결 방법은 권장 사항 섹션을 확인하세요.

Known Issues¶

1. JSON 데이터 삽입

SnowConvert 에서 JSON 데이터 타입이 올바르게 처리되지 않습니다.

2. JSON 오브젝트 조작

JSON 오브젝트(열, 변수 또는 매개 변수)의 사용법이 SnowConvert AI에 의해 올바르게 변환되지 않습니다. 해결 방법은 권장 사항 섹션을 확인하세요.

권장 사항¶

1. JSON Data Type translation workaround¶

JSON 데이터 타입은 _VARIANT_로 변환되므로 Snowflake PARSE_JSON 함수를 사용하여 정보를 형식화할 수 있습니다. 이 접근법을 사용하면 Oracle과 유사한 구문을 사용하여 Snowflake에서 JSON 데이터를 저장, 쿼리 및 작업할 수 있습니다.

Oracle¶

CREATE TABLE jsontable (
	json_column JSON
);

INSERT INTO jsontable VALUES('{"id": 1, "content":"json content"}');
INSERT INTO jsontable VALUES('{"id": 2, "content": {"header": "header text one", "content": "content text one"}}');
INSERT INTO jsontable VALUES('{"id": 3, "content": {"header": "header tex two", "content": "content text two"}}');

SELECT * FROM jsontable;
SELECT 'ID: ' || jt.json_column.id, 'HEADER: ' || UPPER(jt.json_column.content.header) FROM jsontable jt;

결과 1¶


JSON_SERIALIZE(JSON_COLUMN)
{“id”:1,”content”:”json content”}
{“id”:2,”content”:{“header”:”header text one”,”content”:”content text one”}}
{“id”:3,”content”:{“header”:”header tex two”,”content”:”content text two”}}

결과 2¶


‘ID:’ JT.JSON_COLUMN.ID	‘HEADER:’ UPPER(JT.JSON_COLUMN.CONTENT.HEADER)
ID: 1	HEADER:
ID: 2	HEADER: “HEADER TEXT ONE”
ID: 3	HEADER: “HEADER TEX TWO”

Snowflake¶

CREATE OR REPLACE TABLE jsontable (
	json_column VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO jsontable
VALUES('{"id": 1, "content":"json content"}');

INSERT INTO jsontable
VALUES('{"id": 2, "content": {"header": "header text one", "content": "content text one"}}');

INSERT INTO jsontable
VALUES('{"id": 3, "content": {"header": "header tex two", "content": "content text two"}}');

SELECT * FROM
	jsontable;

SELECT 'ID: ' || NVL(jt.json_column.id :: STRING, ''), 'HEADER: ' || NVL(UPPER(jt.json_column.content.header) :: STRING, '') FROM
	jsontable jt;

결과 1¶


JSON_COLUMN
{ “content”: “json content”, “id”: 1}
{ “content”: { “content”: “content text one”, “header”: “header text one” }, “id”: 2}
{ “content”: { “content”: “content text two”, “header”: “header tex two” }, “id”: 3}

결과 2¶


‘ID: ‘ JT.JSON_COLUMN:ID	‘HEADER: ‘ UPPER(JT.JSON_COLUMN:CONTENT:HEADER)
ID: 1
ID: 2	HEADER: HEADER TEXT ONE
ID: 3	HEADER: HEADER TEX TWO

참고

PARSE_JSON 함수를 사용하려면 VALUES 절 대신 _SELECT_를 INSERT INTO 인자로 사용해야 합니다.

참고

‘.’ 연산자 대신 ‘:’를 사용하여 JSON 오브젝트 속성에 액세스합니다. 두 엔진 모두에서 여러 수준의 중첩을 허용합니다.

LONG 데이터 타입¶

LONG 열은 최대 2기가바이트 -1 또는 231-1 바이트의 가변 길이 문자 문자열을 저장합니다. LONG 열은 VARCHAR2 열의 많은 특성을 가지고 있습니다. LONG 열을 사용하여 긴 텍스트 문자열을 저장할 수 있습니다. LONG 값의 길이는 컴퓨터에서 사용 가능한 메모리에 의해 제한될 수 있습니다. (Oracle SQL Language Reference long 데이터 타입)

LONG

샘플 소스 패턴¶

Create Table의 long¶

Oracle¶

CREATE TABLE long_table
( 
     id 	  NUMBER,
     long_column  LONG
);
     
 INSERT INTO long_table VALUES (1, 'this is a text');

Snowflake¶

CREATE OR REPLACE TABLE long_table
 (
      id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
      long_column VARCHAR
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

 INSERT INTO long_table
 VALUES (1, 'this is a text');

긴 열에서 데이터 검색하기¶

Oracle¶

SELECT long_column FROM long_table;

결과¶


LONG_COLUMN
텍스트입니다

Snowflake¶

SELECT long_column FROM
long_table;

결과¶


LONG_COLUMN
텍스트입니다

Known Issues¶

1. The max length of long (Oracle) and varchar (Snowflake) are different¶

Oracle 설명서 에 따르면, long 열은 최대 2기가바이트의 데이터를 저장할 수 있지만, Snowflake varchar 는 16Mb로 제한됩니다.

2. Cast of Long column¶

The Long data type can only be cast to a CLOB data type by using the TO_LOB function. This function only works when used in the select list of a subquery in an INSERT statement. Consider the following sample

Oracle¶

CREATE TABLE target_table (col CLOB);

INSERT INTO target_table (SELECT TO_LOB(long_column) FROM long_table);

경고

대상 테이블 열 데이터 타입이 CLOB 와 다른 경우 데이터를 삽입하려고 할 때 Oracle에서 null 값을 삽입하거나 오류를 표시할 수 있습니다.

RAW 및 LONG RAW 데이터 타입¶

설명¶

RAW 및 LONG RAW 데이터 타입은 서로 다른 시스템 간에 데이터를 이동할 때 Oracle 데이터베이스에서 명시적으로 변환하지 않는 데이터를 저장합니다. 이러한 데이터 타입은 이진 데이터 또는 바이트 문자열을 위한 것입니다. (Oracle SQL Language Reference 행 및 Long 원시 데이터 타입)

{ LONG RAW | RAW (size) }

샘플 소스 패턴¶

Create Table의 원시 및 Long 원시¶

Oracle¶

CREATE TABLE raw_table
(    
     id INTEGER,
     raw_column RAW(2000),
     long_raw_column LONG RAW
);
  
INSERT  INTO raw_table values(1, 'FF00FF00FF', 'FF00FF00FFAABAABABABABA917843210984237123ABABABABAABBAAABBACDFFD');
INSERT  INTO raw_table values(2, 'AAAAAAAAAA', 'ABABABABABABABABABABABABABABABAbABAbABAABABAAABABABABABABABABABABA');
--Insert with largest string posible (2000 HEX characters) 
INSERT INTO raw_table VALUES (3, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')

Snowflake CREATE OR REPLACE TABLE raw_table¶

CREATE OR REPLACE TABLE raw_table
     (
          id INTEGER,
          raw_column BINARY,
          long_raw_column BINARY
     )
     COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
     ;

     INSERT  INTO raw_table
     values(1, 'FF00FF00FF', 'FF00FF00FFAABAABABABABA917843210984237123ABABABABAABBAAABBACDFFD');

     INSERT  INTO raw_table
     values(2, 'AAAAAAAAAA', 'ABABABABABABABABABABABABABABABAbABAbABAABABAAABABABABABABABABABABA');

     --Insert with largest string posible (2000 HEX characters) 
INSERT INTO raw_table
     VALUES (3, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');

원시 및 Long 원시 열에서 데이터 검색하기¶

Oracle¶

SELECT * FROM raw_table ORDER BY id;

결과¶


ID	RAW_COLUMN	LONG_RAW_COLUMN
1		ªº««««© 2 B7 :ºººº«ºª»¬ßý
2	ªªªªª	«««««««««««««««««««ªººªºººººººººº
3	ªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªª	ªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªª

Snowflake¶

SELECT * FROM
raw_table
ORDER BY id;

결과¶


ID	RAW_COLUMN	LONG_RAW_COLUMN
1		ªº««««© 2 B7 :ºººº«ºª»¬ßý
2	ªªªªª	«««««««««««««««««««ªººªºººººººººº
3	ªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªª	ªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªª

Known Issues¶

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

숫자 데이터 타입¶

설명¶

Oracle 데이터베이스 숫자 데이터 타입은 “숫자가 아님” 또는 NAN 연산의 정의되지 않은 결과인 양수와 음수 고정 및 부동 소수점 숫자, 0, 무한대, 값을 저장합니다.(Oracle 언어 참조 숫자 데이터 타입)

산술 작업에 대한 참고 사항¶

숫자 데이터 타입에 대해 수행되는 모든 작업은 내부적으로 숫자로 저장됩니다. 또한, 수행된 작업에 따라 Snowflake 내에 중간 값이 저장되는 방식과 관련된 오류가 발생할 수 있습니다. 자세한 내용은 Snowflake의 중간 숫자에 대한 Snowflake 게시물을 참조하세요.

FLOAT 데이터 타입¶

설명¶

FLOAT 데이터 타입은 NUMBER 의 하위 유형입니다. 전체 자릿수를 포함하거나 포함하지 않고 지정할 수 있으며, NUMBER 의 정의와 동일하고 1~126 사이에서 지정할 수 있습니다. 스케일은 지정할 수 없으며 데이터에서 해석됩니다. (Oracle Language Reference 부동 소수점 데이터 타입)

경고

Notes on arithmetic operations

숫자 데이터 타입에서 수행되는 모든 작업은 내부적으로 숫자로 저장된다는 점에 유의하십시오. 또한, 수행되는 작업에 따라 중간값이 저장되는 방식과 관련된 오류가 발생할 수 있으며, 자세한 내용은 Snowflake의 중간값에 대한 게시글 을 참조하십시오.

샘플 소스 패턴¶

아래 예제에서 다음 테이블과 삽입 내용을 참조하십시오.

Create Table의 부동 소수점 데이터 타입¶

Oracle¶

CREATE TABLE float_data_type_table(
col1 FLOAT,
col2 FLOAT(5),
col3 FLOAT(126)
);

INSERT INTO float_data_type_table (col1) VALUES (100.55555);
INSERT INTO float_data_type_table (col1) VALUES (1.9);
INSERT INTO float_data_type_table (col2) VALUES (1.23);
INSERT INTO float_data_type_table (col2) VALUES (7.89);
INSERT INTO float_data_type_table (col2) VALUES (12.79);
INSERT INTO float_data_type_table (col2) VALUES (123.45);
INSERT INTO float_data_type_table (col3) VALUES (1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.99999999999999999999555555);

Snowflake¶

CREATE OR REPLACE TABLE float_data_type_table (
col1 FLOAT,
col2 FLOAT(5),
col3 FLOAT(126)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO float_data_type_table(col1) VALUES (100.55555);

INSERT INTO float_data_type_table(col1) VALUES (1.9);

INSERT INTO float_data_type_table(col2) VALUES (1.23);

INSERT INTO float_data_type_table(col2) VALUES (7.89);

INSERT INTO float_data_type_table(col2) VALUES (12.79);

INSERT INTO float_data_type_table(col2) VALUES (123.45);

INSERT INTO float_data_type_table(col3) VALUES (1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.99999999999999999999555555);

FLOAT¶

전체 자릿수가 없는 FLOAT 데이터 타입의 경우 Oracle과 Snowflake 간에 차이가 없습니다.

Oracle¶

SELECT col1 FROM float_data_type_table;

결과¶


col1
100.55555
1.9

Snowflake¶

SELECT col1 FROM
float_data_type_table;

결과¶


col1
100.55555
1.9

FLOAT ( p )¶

FLOAT 데이터 타입에 (p) 전체 자릿수가 지정되면 쿼리 결과가 동일하지 않을 수 있습니다. 작은 반올림 차이가 있습니다.

Oracle¶

SELECT col2 FROM float_data_type_table;

SELECT col3 FROM float_data_type_table;

결과¶


col2
1.2
7.9
13
120

col3
—————————————————————————————————-
1111111111111111111111111111111111111100000000000000000000000000000000000000000000000000000000000000

Snowflake¶

SELECT col2 FROM
float_data_type_table;

SELECT col3 FROM
float_data_type_table;

결과¶


col2
1.23
7.89
12.79
123.45

col3
—————————————————————————————————-
1111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Known Issues¶

1. FLOAT data type with precision¶

FLOAT 데이터 타입에 전체 자릿수가 있는 경우 쿼리 결과에 작은 반올림 차이가 있을 수 있습니다.

NUMBER 데이터 타입¶

설명¶

NUMBER 데이터 타입은 1.0 x 10-130에서 1.0 x 10126을 제외한 절대값을 가진 양수 및 음수의 고정 숫자와 0을 저장합니다. 값이 1.0 x 10126 이상의 절대값을 갖는 산술 식을 지정하면 Oracle은 오류를 반환합니다. 각 NUMBER 값은 1에서 22바이트까지 필요합니다. (Oracle Language Reference 숫자 데이터 타입).

NUMBER 데이터 타입은 다음 형식을 사용하여 지정할 수 있습니다. NUMBER(p, s) (두 매개 변수는 모두 선택 사항) 여기서:

p 는 전체 자릿수 또는 소수 자릿수의 최대값이며, 여기서 가장 큰 자릿수는 가장 왼쪽의 0이 아닌 자릿수이고 가장 작은 자릿수는 가장 오른쪽에 있는 자릿수입니다. 전체 자릿수 범위는 0~38입니다.
s 는 스케일 또는 소수점 이하 자릿수입니다. 스케일 범위는 -84에서 127까지입니다.

Oracle에서 전체 자릿수를 지정하지 않으면(NUMBER 또는 NUMBER(*) 사용) 열이 ‘정의되지 않은 전체 자릿수’로 생성됩니다. 즉, Oracle은 값을 동적으로 저장하여 해당 열 내에 모든 숫자를 저장할 수 있습니다. Snowflake는 이 기능을 지원하지 않으므로 가장 다양한 숫자를 저장할 수 있는 NUMBER(38, 18)로 변경됩니다.

경고

Notes on arithmetic operations

숫자 데이터 타입에 대해 수행되는 모든 작업은 내부적으로 숫자로 저장됩니다. 또한, 수행된 작업에 따라 Snowflake 내에 중간 값이 저장되는 방식과 관련된 오류가 발생할 수 있습니다. 자세한 내용은 Snowflake의 중간 숫자에 대한 Snowflake 게시물에서 해당 게시물을 확인하거나 함수 동등성 메시지 SSC-FDM-0006을 확인하세요.

샘플 소스 패턴¶

아래 예제에서 다음 테이블과 삽입 내용을 참조하십시오.

Create Table의 데이터 타입 번호 매기기¶

Oracle¶

CREATE TABLE number_data_type_table
(
col1 NUMBER,
col2 NUMBER(1),
col3 NUMBER(10, 5),
col4 NUMBER(5, -2),
col5 NUMBER(4, 5)
);

INSERT INTO number_data_type_table(COL1) VALUES(100);
INSERT INTO number_data_type_table(COL2) VALUES(1.99999);
INSERT INTO number_data_type_table(COL3) VALUES(12345.12345);
INSERT INTO number_data_type_table(COL4) VALUES(16430.55555);
INSERT INTO number_data_type_table (COL4) VALUES(17550.55555);
INSERT INTO number_data_type_table(COL5) VALUES(0.00009);
INSERT INTO number_data_type_table(COL5) VALUES(0.000021);
INSERT INTO number_data_type_table(COL5) VALUES(0.012678912);

Snowflake¶

CREATE OR REPLACE TABLE number_data_type_table
(
col1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col2 NUMBER(1) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col3 NUMBER(10, 5) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col4 NUMBER(5) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0092 - NUMBER DATATYPE NEGATIVE SCALE WAS REMOVED FROM OUTPUT ***/!!! /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col5 NUMBER(5, 5) /*** SSC-FDM-OR0010 - NUMBER DATATYPE SMALLER PRECISION WAS INCREASED TO MATCH SCALE ***/ /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO number_data_type_table(COL1) VALUES(100);

INSERT INTO number_data_type_table(COL2) VALUES(1.99999);

INSERT INTO number_data_type_table(COL3) VALUES(12345.12345);

INSERT INTO number_data_type_table(COL4) VALUES(16430.55555);

INSERT INTO number_data_type_table(COL4) VALUES(17550.55555);

INSERT INTO number_data_type_table(COL5) VALUES(0.00009);

INSERT INTO number_data_type_table(COL5) VALUES(0.000021);

INSERT INTO number_data_type_table(COL5) VALUES(0.012678912);

NUMBER (기본 케이스)¶

전체 자릿수와 스케일이 지정되지 않은 경우 기본값은 사용 가능한 최대 NUMBER(38, 127) 입니다. 기본 케이스의 현재 변환은 NUMBER(38,19) 입니다.

경고

Oracle에서는 전체 자릿수나 스케일을 정의하지 않으면 기본값이 “정의되지 않은 전체 자릿수 및 스케일”로 설정됩니다. 입력값을 ‘받은 대로’ 저장하는 방식으로 작동하므로 정수와 부동 소수점 숫자를 모두 처리할 수 있습니다. 38, 18 을 사용하여 정수에 20을 사용하고 부동 소수점 자릿수에 18을 남겨서 두 가지를 모두 처리하려고 시도합니다.

Oracle¶

SELECT col1 FROM number_data_type_table;

결과¶


col1
100

Snowflake¶

SELECT col1 FROM
number_data_type_table;

결과¶


col1
100.0000000000000000000

NUMBER ( p )¶

이 경우 전체 자릿수는 소수점 왼쪽에 숫자가 가질 수 있는 자릿수를 지정합니다.

Oracle¶

SELECT col2 FROM number_data_type_table;

결과¶


col2
2

Snowflake¶

SELECT col2 FROM
number_data_type_table;

결과¶


col2
2

NUMBER ( p, s ) p > s¶

s 가 p 보다 낮은 경우 전체 자릿수는 숫자가 가질 수 있는 자릿수를 지정합니다. 스케일은 소수점 오른쪽의 유효 자릿수를 지정하므로 소수점 왼쪽의 자릿수는 지정된 스케일에 따라 달라집니다.

Oracle¶

SELECT col3 FROM number_data_type_table;

결과¶


col3
12345.12345

Snowflake¶

SELECT col3 FROM
number_data_type_table;

결과¶


col3
12345.12345

NUMBER ( p, -s )¶

음수 스케일은 소수점 왼쪽의 소수 자릿수에서 가장 작은 자릿수를 제외한 나머지 자릿수입니다. 음수 스케일의 경우 실제 데이터가 소수점 왼쪽에서 지정된 소수점 이하 자리 수로 반올림되므로 소수점 이하 자릿수가 소수점 왼쪽에 있습니다. 현재 변환은 음수 스케일을 제거하는 것입니다.

Oracle¶

SELECT col4 FROM number_data_type_table;

결과¶


col4
16400
17600

Snowflake¶

SELECT col4 FROM
number_data_type_table;

결과¶


col4
16431
17551

NUMBER ( p, s ) s > p¶

스케일이 전체 자릿수보다 큰 경우 다음 측면을 고려하십시오.

삽입할 숫자의 소수점 왼쪽에는 소수 자릿수가 있을 수 없습니다. 0만 사용할 수 있습니다.
소수점 오른쪽의 첫 번째 자릿수는 0이어야 합니다.
전체 자릿수는 소수점 오른쪽에 있는 최대 유효 자릿수를 지정합니다.

Oracle¶

SELECT col5 FROM number_data_type_table;

결과¶


col5
0.00009
0.00002
0.01268

Snowflake¶

SELECT col5 FROM
number_data_type_table;

결과¶


col5
0.00009
0.00002
0.01268

Known Issues¶

1. Scale value exceeds the maximum allowed by Snowflake¶

Snowflake에서 허용되는 최대값(37)보다 큰 소수 자릿수를 지정하면 18로 변경됩니다. 이에 대한 자세한 내용을 보려면 SSC-FDM-0006 설명서를 참조하세요.

2. Negative scale¶

Snowflake는 음수 소수 자릿수를 허용하지 않으므로 제거 중입니다. 이로 인해 기능적 불일치가 발생할 수 있습니다. 이 문제에 대한 자세한 내용을 보려면 SSC-EWI-0R0092 설명서를 참조하세요.

권장 사항¶

1. UDF for NUMBER datatype Operations¶

산술 연산 작업을 수행할 때 다음 UDF 를 사용하여 이러한 작업을 수동으로 마이그레이션하면 언급된 문제가 발생하지 않습니다.

UDF¶

CREATE OR REPLACE FUNCTION fixed_divide(a NUMBER(38,19), b NUMBER(38,19))
RETURNS NUMBER(38,19)
LANGUAGE JAVA
CALLED ON NULL INPUT
HANDLER='TestFunc.divide'
AS
'
import java.math.BigDecimal;
import java.math.RoundingMode;
class TestFunc {
public static BigDecimal divide(BigDecimal a, BigDecimal b) {
return a.divide(b,RoundingMode.HALF_UP);
}
}';

부동 소수점 숫자¶

설명¶

부동 소수점 숫자는 첫 번째 자릿수부터 마지막 자릿수까지에서 임의의 위치에 소수점이 있을 수도 있고, 없을 수도 있습니다. 범위를 늘리기 위해 숫자 다음에 지수를 선택적으로 사용할 수 있습니다(예: 1.777 e-20). 소수점 뒤에 나타날 수 있는 자릿수가 제한되지 않으므로 소수 자릿수 값은 부동 소수점 숫자에 맞출 수 없습니다. 이진 부동 소수점 숫자는 이진 전체 자릿수(자릿수 0 및 1)를 사용하여 저장됩니다(Oracle 언어 참조 부동 소수점 숫자).

BINARY_DOUBLE¶

설명¶

BINARY_DOUBLE 은 64비트의 배정밀도 부동 소수점 숫자 데이터 타입입니다. 각 BINARY_DOUBLE 값에는 8바이트가 필요합니다. BINARY_DOUBLE 열에서 부동 소수점 숫자는 이진 전체 자릿수를 갖습니다. 이진 부동 소수점 숫자는 특수 값 무한대 및 NaN (숫자가 아님)를 지원합니다. (Oracle Language Reference 이진_더블 데이터 타입)

다음 제한 내에서 부동소수점 숫자를 지정할 수 있습니다.

최대 양의 유한값 = 1.79769313486231E+308
최소 양의 유한값 = 2.22507485850720E-308

샘플 소스 패턴¶

아래 예제에서 다음 테이블과 삽입 내용을 참고하십시오.

Create Table의 이진 더블¶

Oracle¶

CREATE TABLE binary_double_data_type_table
(
COL1 BINARY_DOUBLE
);

INSERT INTO binary_double_data_type_table VALUES(2.22507485850720E-308D);
INSERT INTO binary_double_data_type_table VALUES(1.79769313486231E+308D);
INSERT INTO binary_double_data_type_table VALUES('NaN');

Snowflake¶

CREATE OR REPLACE TABLE binary_double_data_type_table
(
COL1 FLOAT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO binary_double_data_type_table
VALUES(2.22507485850720E-308);

INSERT INTO binary_double_data_type_table
VALUES(1.79769313486231E+308);

INSERT INTO binary_double_data_type_table
VALUES('NaN');

참고

‘NaN’ 은 _ Not a Number _를 의미하며, 이 값은 Oracle의 BINARY_DOUBLE 데이터 타입과 Snowflake의 FLOAT 데이터 타입에서 허용됩니다.

BINARY_DOUBLE -> FLOAT¶

BINARY_DOUBLE 데이터 타입은 Snowflake에서 지원되지 않으므로 FLOAT 로 변환하는 중입니다.

Oracle¶

SELECT * FROM binary_double_data_type_table;

결과¶


col1
0
179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
NaN

Snowflake¶

SELECT * FROM
binary_double_data_type_table;

결과¶


col1
0
179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
NaN

Known Issues¶

1. The BINARY_DOUBLE data type is not supported by Snowflake¶

BINARY_DOUBLE 데이터 타입은 Snowflake에서 지원되지 않으므로 FLOAT 로 변환됩니다.

BINARY_FLOAT¶

설명¶

BINARY_FLOAT 는 32비트의 단정밀도 부동 소수점 숫자 데이터 타입입니다. 각 BINARY_FLOAT 값에는 4바이트가 필요합니다. BINARY_FLOAT 열에서 부동 소수점 숫자는 이진 전체 자릿수를 갖습니다. 이진 부동 소수점 숫자는 특수 값 무한대 및 NaN (숫자 아님)를 지원합니다. (Oracle Language Reference 이진_부동 소수점 데이터 타입)

다음 제한 내에서 부동소수점 숫자를 지정할 수 있습니다.

최대 양의 유한값 = 3.40282E+38F
최소 양의 유한값 = 1.17549E-38F

샘플 소스 패턴¶

아래 예제에서 다음 테이블과 삽입 내용을 참고하십시오.

Create Table의 이진 부동 소수점¶

Oracle¶

CREATE TABLE binary_float_data_type_table
(
col1 BINARY_FLOAT
);

INSERT INTO binary_float_data_type_table VALUES(1.17549E-38F);
INSERT INTO binary_float_data_type_table VALUES(3.40282E+38F);
INSERT INTO binary_float_data_type_table VALUES('NaN');

Snowflake¶

CREATE OR REPLACE TABLE binary_float_data_type_table
(
col1 FLOAT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO binary_float_data_type_table
VALUES(1.17549E-38);

INSERT INTO binary_float_data_type_table
VALUES(3.40282E+38);

INSERT INTO binary_float_data_type_table
VALUES('NaN');

참고

‘NaN’ 은 _ Not a Number _를 의미하며, 이 값은 Oracle의 BINARY_FLOAT 데이터 타입과 Snowflake의 FLOAT 데이터 타입에서 허용됩니다.

BINARY_FLOAT -> FLOAT¶

BINARY_FLOAT 데이터 타입은 Snowflake에서 지원되지 않으므로 FLOAT 로 변환하는 중입니다.

Oracle¶

SELECT * FROM binary_float_data_type_table;

결과¶


col1
0
340282001837565600000000000000000000000
NaN

Snowflake¶

SELECT * FROM binary_float_data_type_table;

결과¶


col1
0
340282000000000000000000000000000000000
NaN

Known Issues¶

1. The BINARY_FLOAT data type is not supported by Snowflake¶

BINARY_FLOAT 데이터 타입은 Snowflake에서 지원되지 않으므로 FLOAT 로 변환됩니다.

날짜/시간 및 간격 데이터 타입¶

날짜/시간 데이터 타입은 DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE입니다. 날짜/시간 데이터 타입의 값을 날짜/시간이라고도 합니다. 간격 데이터 타입은 INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND입니다. 간격 데이터 타입의 값을 간격이라고도 합니다(Oracle SQL 언어 참조 날짜/시간 및 간격 데이터 타입).

DATE 데이터 타입¶

설명¶

Oracle의 날짜 데이터 타입은 날짜와 시간 정보를 모두 저장하지만, Snowflake의 날짜 데이터 타입은 날짜 정보만 저장합니다. (Oracle SQL Language Reference 날짜 데이터 타입)

The default transformation for Oracle DATE is to Snowflake TIMESTAMP. You can add the disableDateAsTimestamp flag (SnowConvert AI Command Line Interface) or disable the Transform Date as Timestamp setting (SnowConvert AI desktop application) to transform the DATE type to TIMESTAMP. Keep in mind that Snowflake DATE only stores date information and Oracle stores date and time information, if you want to avoid losing information you should transform DATE to TIMESTAMP.

참고

중요한 반올림 동작 차이점: 날짜/타임스탬프 데이터 타입과 초가 포함된 간격 사이에서 연산을 수행할 때, Oracle은 초를 반올림하지 않고 지정된 대로 전체 자릿수를 유지하는 반면, Snowflake는 초를 가장 가까운 정수 초로 반올림합니다. 반올림 동작의 이러한 차이로 인해 다른 결과가 발생할 수 있습니다.

샘플 소스 패턴¶

Create Table의 날짜¶

Oracle¶

CREATE TABLE date_table
(
	date_col date
);

INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');

–disableDateAsTimestamp 플래그가 없거나 “날짜를 타임스탬프로 변환” 설정이 활성화된 Snowflake¶

CREATE OR REPLACE TABLE date_table
	(
		date_col TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
	;

	INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');

–disableDateAsTimestamp 플래그가 있거나 “날짜를 타임스탬프로 변환” 설정이 비활성화된 Snowflake¶

CREATE OR REPLACE TABLE date_table
	(
		date_col date
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');

날짜 열에서 데이터 검색하기¶

Oracle¶

SELECT date_col FROM date_table;

결과¶


DATE_COL
2010-10-10 00:00:00.000

Snowflake¶

SELECT date_col FROM
date_table;

결과¶


DATE_COL
2010-10-10 00:00:00.000

disableDateAsTimestamp 플래그를 사용한 결과¶


DATE_COL
2010-10-10

Known Issues¶

1. Input and output format may differ between languages¶

Snowflake에서 _ DATE_ 입력 및 출력 형식은 _ DATE_INPUT_FORMAT_ 및 _ DATE_OUTPUT_FORMAT_ 세션 변수에 따라 달라집니다. DATE_INPUT_FORMAT 에서 날짜를 텍스트로 추가할 때 사용자가 특정 형식을 사용하도록 강제하기 때문에 삽입이 실패할 수 있습니다. 다음 구문을 사용하여 이러한 변수를 수정할 수 있습니다.

ALTER SESSION SET DATE_INPUT_FORMAT = 'YYYY-DD-MM' DATE_OUTPUT_FORMAT = 'DD-MM-YYYY';

INTERVAL DAY TO SECOND 데이터 타입¶

설명¶

INTERVAL DAY TO SECOND 는 기간을 일, 시간, 분, 초 단위로 저장합니다. (Oracle SQL Language Reference INTERVAL DAY TO SECOND 데이터 타입)

By default, there is no equivalent for this data type in Snowflake and it is transformed to VARCHAR.

참고

Preview Feature: When the --UseIntervalDatatype preview flag is enabled, Oracle INTERVAL DAY TO SECOND columns are preserved as native Snowflake INTERVAL DAY TO SECOND types. See the Interval Data Types translation reference for complete transformation details.

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

샘플 소스 패턴¶

Create Table에서 일에서 초 사이의 간격¶

Oracle¶

CREATE TABLE interval_day_to_second_table
(
	interval_day_col1 interval day to second,
	interval_day_col2 interval day(1) to second(4)
);

INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ( INTERVAL '1 2:3:4.56' DAY TO SECOND ); 
INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ( INTERVAL '1 2:3:4.56' DAY(1) TO SECOND(4) );

Snowflake¶

CREATE OR REPLACE TABLE interval_day_to_second_table
	(
		interval_day_col1 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL day to second DATA TYPE CONVERTED TO VARCHAR ***/!!!,
		interval_day_col2 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL day(1) to second(4) DATA TYPE CONVERTED TO VARCHAR ***/!!!
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ('1d, 2h, 3m, 4s, 56ms');

	INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ('1d, 2h, 3m, 4s, 56ms');

The Interval value is transformed to a supported Snowflake format and then inserted as text inside the column. Since Snowflake does not support Interval as a data type, it is only supported in arithmetic operations. To use the value, it needs to be extracted and used as an Interval constant (if possible).

원본 Oracle 값: INTERVAL '1 2:3:4.567' DAY TO SECOND

Snowflake 열에 저장된 값: '1d, 2h, 3m, 4s, 567ms'

Snowflake 간격 상수 값: INTERVAL '1d, 2h, 3m, 4s, 567ms'

간격 일에서 두 번째 열로 데이터 검색하기¶

Oracle¶

SELECT * FROM interval_day_to_second_table;

결과¶


INTERVAL_DAY_COL1	INTERVAL_DAY_COL2
1 2:3:4.567
	1 2:3:4.567

Snowflake¶

SELECT * FROM
interval_day_to_second_table;

결과¶


INTERVAL_DAY_COL1	INTERVAL_DAY_COL2
1일, 2시간, 3분, 4초, 56밀리초
	1일, 2시간, 3분, 4초, 56밀리초

Known Issues¶

1. Only arithmetic operations are supported¶

Snowflake 간격에는 몇 가지 제한 사항이 있습니다. DATE 또는 TIMESTAMP 및 Interval Constants 간의 산술 연산만 지원되며, 다른 모든 시나리오는 지원되지 않습니다.

INTERVAL YEAR TO MONTH 데이터 타입¶

설명¶

INTERVAL YEAR TO MONTH 는 YEAR 및 MONTH 날짜/시간 필드를 사용하여 기간을 저장합니다. Snowflake에는 이에 상응하는 타입이 없으므로 Varchar로 변환됩니다. (Oracle SQL Language Reference INTERVAL YEAR TO MONTH 데이터 타입)

By default, there is no equivalent for this data type in Snowflake and it is transformed to VARCHAR.

참고

Preview Feature: When the --UseIntervalDatatype preview flag is enabled, Oracle INTERVAL YEAR TO MONTH columns are preserved as native Snowflake INTERVAL YEAR TO MONTH types. See the Interval Data Types translation reference for complete transformation details.

INTERVAL YEAR [(year_precision)] TO MONTH

샘플 소스 패턴¶

Create Table의 연도 간 월 간 간격¶

Oracle¶

CREATE TABLE interval_year_to_month_table
(
	interval_year_col1 interval year to month,
	interval_year_col2 interval year(4) to month
);

INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ( INTERVAL '1-2' YEAR TO MONTH ); 
INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ( INTERVAL '1000-11' YEAR(4) TO MONTH );

Snowflake¶

CREATE OR REPLACE TABLE interval_year_to_month_table
	(
		interval_year_col1 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL year to month DATA TYPE CONVERTED TO VARCHAR ***/!!!,
		interval_year_col2 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL year(4) to month DATA TYPE CONVERTED TO VARCHAR ***/!!!
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ('1y, 2mm');

	INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ('1000y, 11mm');

원본 Oracle 값: INTERVAL '1-2' YEAR TO MONTH

Snowflake 열에 저장된 값: '1y, 2m'

Snowflake 간격 상수 값: INTERVAL '1y, 2m'

연도 간에서 월 간 열에서 데이터 검색하기¶

Oracle¶

SELECT * FROM interval_year_to_month_table;

결과¶


INTERVAL_YEAR_COL1	INTERVAL_YEAR_COL2
1-2
	1000-11

Snowflake¶

SELECT * FROM
interval_year_to_month_table;

결과¶


INTERVAL_YEAR_COL1	INTERVAL_YEAR_COL2
1년, 2분

              |1000y, 11m        |

Known Issues¶

1. Only arithmetic operations are supported¶

TIMESTAMP 데이터 타입¶

설명¶

TIMESTAMP 데이터 타입은 DATE 데이터 타입의 확장입니다. DATE 데이터 타입의 연, 월, 일과 시, 분, 초 값을 저장합니다. (Oracle SQL Language Reference 타임스탬프 데이터 타입)

Oracle과 Snowflake TIMESTAMP 데이터 타입의 전체 자릿수 범위(0~9)는 같지만 기본값이 다릅니다. Oracle의 기본값은 6이고 Snowflake의 기본값은 9입니다.

그러나 삽입된 값이 설정된 전체 자릿수를 초과하는 경우 동작에 차이가 있습니다. Oracle은 초과하는 소수를 반올림하는 반면, Snowflake는 값을 자르기만 합니다.

TIMESTAMP [(fractional_seconds_precision)] 

샘플 소스 패턴¶

Create Table의 타임스탬프¶

Oracle¶

CREATE TABLE timestamp_table
(
	timestamp_col1 TIMESTAMP,
	timestamp_col2 TIMESTAMP(7)
);

INSERT INTO timestamp_table(timestamp_col1, timestamp_col2) VALUES (TIMESTAMP '2010-10-10 12:00:00', TIMESTAMP '2010-10-10 12:00:00');

Snowflake¶

CREATE OR REPLACE TABLE timestamp_table
	(
		timestamp_col1 TIMESTAMP(6),
		timestamp_col2 TIMESTAMP(7)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO timestamp_table(timestamp_col1, timestamp_col2) VALUES (TIMESTAMP '2010-10-10 12:00:00', TIMESTAMP '2010-10-10 12:00:00');

타임스탬프 열에서 데이터 검색하기¶

Oracle¶

SELECT * FROM timestamp_table;

결과¶


TIMESTAMP_COL1	TIMESTAMP_COL2
2010-10-10 12:00:00.000	2010-10-10 12:00:00.000

Snowflake¶

SELECT * FROM
timestamp_table;

결과¶


TIMESTAMP_COL1	TIMESTAMP_COL2
2010-10-10 12:00:00.000	2010-10-10 12:00:00.000

Known Issues¶

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

TIMESTAMP WITH LOCAL TIME ZONE 데이터 타입¶

설명¶

데이터베이스에 저장된 데이터가 데이터베이스 타임존으로 정규화되고 타임존 정보가 열 데이터의 일부로 저장되지 않는다는 점에서 TIMESTAMP WITH TIME ZONE 과 다릅니다. (Oracle SQL Language Reference 로컬 시간 존이 포함된 타임스탬프 데이터 타입)

Snowflake의 해당 기능은 TIMESTAMP_LTZ 입니다.

자세한 내용은 TIMESTAMP 섹션을 참조하십시오.

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

샘플 소스 패턴¶

Create Table의 타임존이 포함된 타임스탬프¶

Oracle¶

CREATE TABLE timestamp_with_local_time_zone_table
(
	timestamp_col1 TIMESTAMP(5) WITH LOCAL TIME ZONE
);

INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');
INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -08:00');

Snowflake¶

CREATE OR REPLACE TABLE timestamp_with_local_time_zone_table
	(
		timestamp_col1 TIMESTAMP_LTZ(5)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');

	INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -08:00');

현지 타임존 열이 있는 타임스탬프에서 데이터 검색하기¶

Oracle¶

SELECT * FROM timestamp_with_local_time_zone_table;

결과¶


TIMESTAMP_COL1
2010-10-10 18:00:00.000
2010-10-10 20:00:00.000

Snowflake¶

SELECT * FROM
timestamp_with_local_time_zone_table;

결과¶


TIMESTAMP_COL1
2010-10-10 12:00:00.000 -0700
2010-10-10 12:00:00.000 -0700

참고

각 데이터베이스가 다른 타임존으로 설정되어 있기 때문에 두 엔진에서 결과 세트가 다릅니다. Oracle 타임존은 ‘+00:00’이고 Snowflake 타임존은 ‘America/Los_Angeles’입니다.

데이터베이스의 기본 시간대를 변경하려면 다음 구문을 사용합니다.

ALTER account SET timezone = timezone_string;

Known Issues¶

1. Default database timezone¶

이러한 종류의 데이터 타입을 사용하는 작업은 데이터베이스 표준 시간대의 영향을 받으며 결과가 달라질 수 있습니다. 다음 쿼리를 사용하여 기본 타임존을 확인할 수 있습니다.

Oracle¶

SELECT dbtimezone FROM dual;

Snowflake¶

SELECT dbtimezone FROM dual;

2. Oracle Timestamp with local timezone behavior¶

When operating timestamps with local timezone data types, Oracle converts the timestamps to the default timezone of the database. To emulate this behavior in Snowflake, the TIMESTAMP_TYPE_MAPPING session parameter should be set to ‘TIMESTAMP_LTZ’.

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';

3. Timestamp formats may be different¶

Snow Convert는 날짜/타임스탬프 형식 문자열에 대한 변환 성능을 수행하지 않으므로 코드를 배포할 때 오류가 발생할 수 있습니다. 예:

Oracle¶

INSERT INTO timestamp_with_local_time_zone_table (timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');

Snowflake¶

INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');

경고

The query will fail in Snowflake because the default timestamp input format does not recognize ‘-8:00’ as a valid UTC offset. It should be replaced with ‘0800’ or ‘-08:00’ to get the same result.

TIMESTAMP WITH TIME ZONE 데이터 타입¶

설명¶

TIMESTAMP WITH TIME ZONE 은 값에 타임존 리전 이름 또는 타임존 오프셋이 포함된 TIMESTAMP 의 베리언트입니다. Snowflake의 해당 기능은 TIMESTAMP_TZ 입니다.(Oracle SQL Language Reference 타임존 데이터 타입 포함 타임스탬프)

Snowflake의 해당 기능은 TIMESTAMP_TZ 입니다.

자세한 내용은 TIMESTAMP 섹션을 참조하십시오.

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

샘플 소스 패턴¶

Create Table의 타임존이 포함된 타임스탬프¶

Oracle¶

CREATE TABLE timestamp_with_time_zone_table
(
	timestamp_col1 TIMESTAMP(5) WITH TIME ZONE
);


INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');

Snowflake¶

CREATE OR REPLACE TABLE timestamp_with_time_zone_table
	(
		timestamp_col1 TIMESTAMP_TZ(5)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');

타임스탬프가 있는 타임존 열에서 데이터 검색하기¶

Oracle¶

SELECT * FROM timestamp_with_time_zone_table;

결과¶


TIMESTAMP_COL1
2010-10-10 12:00:00.000 -0600

Snowflake¶

SELECT * FROM
timestamp_with_time_zone_table;

결과¶


TIMESTAMP_COL1
2010-10-10 12:00:00.000 -0700

참고

시간대를 지정하지 않으면 데이터베이스의 기본 시간대가 추가되므로 두 엔진에서 시간대가 다릅니다.

데이터베이스의 기본 시간대를 변경하려면 다음 구문을 사용합니다.

ALTER account SET sqtimezone = timezone_string;

Known Issues¶

1. Timestamp formats may be different¶

Snow Convert는 날짜/타임스탬프 형식 문자열에 대한 변환 성능을 수행하지 않으므로 코드를 배포할 때 오류가 발생할 수 있습니다. 예:

Oracle¶

INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');

Snowflake¶

INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');

경고

The query will fail in Snowflake because the default timestamp input format does not recognize ‘-8:00’ as a valid UTC offset. It should be replaced with ‘-0800’ or ‘-08:00’ to get the same result.

날짜/시간 산술¶

여기에서는 날짜/시간 유형 간의 일부 산술 연산에 대한 현재 변환을 설명합니다.

설명¶

Oracle에서는 더하기, 빼기, 곱하기, 나누기와 같은 DateTime 유형 간에 일부 산술 연산을 수행할 수 있었습니다. 현재, SnowConvert AI는 더하기 및 빼기의 일부 사례를 처리할 수 있습니다. 이러한 사례는 아래에 설명되어 있습니다.

샘플 소스 패턴¶

이는 날짜, 타임스탬프, 숫자, 알 수 없는 유형을 사용하는 더하기 및 빼기 연산의 다양한 조합에 대한 현재 변환의 요약입니다.

참고

아래 예는 다음 테이블을 참조하세요.

Oracle¶

CREATE OR REPLACE TABLE TIMES (
AsTimeStamp TIMESTAMP(6),
AsTimestampTwo TIMESTAMP(6),
AsDate TIMESTAMP,
AsDateTwo TIMESTAMP
);

INSERT INTO TIMES
VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));

Snowflake¶

CREATE OR REPLACE TABLE TIMES (
 AsTimeStamp TIMESTAMP(6),
 AsTimestampTwo TIMESTAMP(6),
 AsDate TIMESTAMP(6),
 AsDateTwo TIMESTAMP(6)
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

 INSERT INTO TIMES
 VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));

더하기¶

조합 행렬¶

다음은 마이그레이션 프로그램이 날짜, 타임스탬프, 숫자, 알 수 없는 유형을 사용하여 다양한 조합에 대한 더하기 연산을 해결하는 방법에 대한 요약입니다.


더하기	날짜	타임스탬프	숫자	간격	알 수 없음	Float
날짜	INVALID	INVALID	날짜 + 간격 일	날짜 + 간격 IntervalUnit	DATEADD_UDF	DATEADD_UDF
타임스탬프	INVALID	INVALID	타임스탬프 + 간격 일	타임스탬프 + 간격 IntervalUnit	DATEADD_UDF	DATEADD_UDF
숫자	날짜 + 간격 일	타임스탬프 + 간격 일	숫자 + 숫자	INVALID	SSC-EWI-OR0036	숫자 + 부동 소수점
간격	날짜 + 간격 IntervalUnit	타임스탬프 + 간격 IntervalUnit	INVALID	SSC-EWI-OR0036	알 수 없음 + 간격 IntervalUnit	INVALID
알 수 없음	DATEADD_UDF	DATEADD_UDF	알 수 없음 + 숫자	알 수 없음 + 간격 IntervalUnit	SSC-EWI-OR0036	SSC-EWI-OR0036
부동 소수점	DATEADD_UDF	DATEADD_UDF	부동 소수점 + 숫자	INVALID	SSC-EWI-OR0036	부동 소수점 + 부동 소수점

참고

An Unknown Type column is the result of the migrator being unable to establish the data type that the column contains. This can happen for many reasons, for example, missing DDLs for the tables being operated on, or columns resulting from operations on views, CTEs, or subqueries.

경고

By default, Snow Convert migrates operations of type Date/Timestamp + Interval to the native Snowflake operations, but in some cases may be useful to use UDF instead. For further details, see Interval UDFs vs. Snowflake native interval operation.

마이그레이터가 더하기 연산을 처리하는 데 사용할 수 있는 다양한 경로는 아래에 설명되어 있습니다.

유효하지 않음¶

특정 조합은 Oracle에서 더하기 연산을 수행하는 데 유효하지 않습니다.

Oracle¶

SELECT AsDate + AsDateTwo From TIMES;

SELECT AsDate + AsTimeStamp From TIMES;

결과¶

SQL Error [975] [42000]: ORA-00975: date + date not allowed

SQL Error [30087] [99999]: ORA-30087: Adding two datetime values is not allowed

날짜 + 간격 일¶

이는 날짜 유형과 숫자 사이의 더하기 연산에 대한 현재 변환입니다(그 반대도 마찬가지임). 예

Oracle¶

SELECT AsDate + 1 FROM TIMES;

SELECT 1 + AsDate FROM TIMES;

결과¶


ASDATE+1
2021-11-07 00:00:00.000


1+ASDATE
2021-11-07 00:00:00.000

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
 AsDate + 1 FROM
 TIMES;


SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Number AND unknown ***/!!! 1 + AsDate FROM
 TIMES;

결과¶


ASDATE + INTERVAL ‘1 DAY’
2021-11-07

타임스탬프 + 간격 일¶

이는 타임스탬프 유형과 숫자 사이의 더하기 연산에 대한 현재 변환입니다(그 반대도 마찬가지임). 예

Oracle¶

SELECT AsTimestamp + 1 FROM TIMES;

SELECT 1 + AsTimestamp FROM TIMES;

결과¶


ASTIMESTAMP+1
2021-11-06 11:00:00.000


1+ASTIMESTAMP
2021-11-06 11:00:00.000

참고

참고: Oracle에서는 DATE 및 TIMESTAMP 열 모두에 시간 구성 요소가 포함되어 있지만, Oracle은 NLS_DATE_FORMAT 매개 변수에 의해 지정된 형식 마스크를 사용하여 암시적으로 날짜를 문자열로 변환하는 방법을 결정했습니다. 이것이 TIMESTAMP 및 간격 사이에서 일부 연산을 수행하는 경우 NLS_DATE_FORMAT 매개 변수가 변경되지 않는 한, 시간 구성 요소가 숨겨지고 해당 결과는 DATE로 표시되는 이유입니다.

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
 AsTimestamp + 1 FROM
 TIMES;


SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Number AND unknown ***/!!! 1 + AsTimestamp FROM
 TIMES;

결과¶


ASTIMESTAMP + INTERVAL ‘1 DAY’
2021-11-06 11:00:00.000

DATEADD_UDF¶

For those cases where there is an addition operation between a date or timestamp type and an unknown type, a user-defined function (UDF) is added. See the DATEADD_UDF implementation for details. The UDF is located in the UDFs folder. For example:

참고

다음 예에서는 하위 쿼리가 사용되어 알 수 없는 유형 열을 시뮬레이션하려고 시도합니다.

Oracle¶

SELECT AsDate + (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM TIMES) FROM TIMES;

SELECT AsTimestamp + (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM TIMES) FROM TIMES;

결과¶


ASDATE+(SELECTEXTRACT(DAYFROMASTIMESTAMPTWO)FROMTIMES)
2021-11-11 00:00:00.000


ASTIMESTAMP+(SELECTEXTRACT(DAYFROMASTIMESTAMPTWO)FROMTIMES)
2021-11-10 11:00:00.000

Snowflake¶

SELECT AsDate + (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM
TIMES
) FROM
TIMES;

SELECT AsTimestamp + (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM
TIMES
) FROM
TIMES;

결과¶


PUBLIC.DATEADD_UDF( ASDATE, (SELECT EXTRACT(DAY FROM ASTIMESTAMPTWO) FROM PUBLIC.TIMES))
2021-11-11


PUBLIC.DATEADD_UDF( ASTIMESTAMP, (SELECT EXTRACT(DAY FROM ASTIMESTAMPTWO) FROM PUBLIC.TIMES))
2021-11-10 11:00:00.000

빼기¶

조합 행렬¶


빼기	날짜	타임스탬프	숫자	간격	알 수 없음	Float
날짜	DATEDIFF	TIMESTAMP_DIFF___UDF	날짜 - 간격 일	날짜 - 간격 IntervalUnit	DATEDIFF_UDF	DATEDIFF_UDF
타임스탬프	TIMESTAMP_DIFF___UDF	TIMESTAMP_DIFF___UDF	타임스탬프 - 간격 일	타임스탬프 - 간격 IntervalUnit	DATEDIFF_UDF	DATEDIFF_UDF
숫자	INVALID	INVALID	숫자 - 숫자	INVALID	SSC-EWI-OR0036	숫자 - 부동 소수점
간격	INVALID	INVALID	INVALID	SSC-EWI-OR0036	알 수 없음 - 간격 IntervalUnit	NOT SUPPORTED IN ORACLE
알 수 없음	DATEDIFF_UDF	DATEDIFF_UDF	SSC-EWI-OR0036	알 수 없음 - 간격 IntervalUnit	SSC-EWI-OR0036	SSC-EWI-OR0036
부동 소수점	DATEDIFF_UDF	DATEDIFF_UDF	부동 소수점 - 숫자	NOT SUPPORTED IN ORACLE	SSC-EWI-OR0036	부동 소수점 - 부동 소수점

참고

경고

마이그레이션터가 빼기 연산을 처리하는 데 사용할 수 있는 다양한 경로는 아래에 설명되어 있습니다.

유효하지 않음¶

특정 조합은 Oracle에서 빼기 연산을 수행하는 데 유효하지 않습니다.

Oracle¶

SELECT 1 - AsDate FROM TIMES;

SELECT 1 - AsTimestamp FROM TIMES;

결과¶

SQL Error [932] [42000]: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

SQL Error [932] [42000]: ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP

DATEDIFF¶

날짜 타입의 두 피연산자 간 빼기는 시간 단위(첫 번째 매개 변수) ‘일’을 사용하여 Snowflake DATEDIFF 함수로 변환됩니다. 예

Oracle¶

SELECT AsDate - AsDateTwo FROM TIMES;

결과¶


ASDATE-ASDATETWO
1

Snowflake¶

SELECT AsDate - AsDateTwo FROM
TIMES;

결과¶


DATEDIFF(DAY, ASDATETWO, ASDATE)
1

날짜 - 간격 일¶

이는 날짜 유형과 숫자 사이의 빼기 연산에 대한 현재 변환입니다. 예

Oracle¶

SELECT AsDate - 1 FROM TIMES;

SELECT AsDate + -1 FROM TIMES;

결과¶


ASDATE-1
2021-11-05 00:00:00.000


ASDATE+-1
2021-11-05 00:00:00.000

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
 AsDate - 1 FROM
 TIMES;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! AsDate + -1 FROM
 TIMES;

결과¶


ASDATE - INTERVAL ‘1 DAY’
2021-11-05


ASDATE + INTERVAL ‘-1 DAY’
2021-11-05

타임스탬프 - 간격 일¶

이는 타임스탬프 유형과 숫자 사이의 더하기 연산에 대한 현재 변환입니다. 예

Oracle¶

SELECT AsTimestamp - 1 FROM TIMES;

SELECT AsTimestamp + -1 FROM TIMES;

결과¶


ASTIMESTAMP-1
2021-11-04 11:00:00.000


ASTIMESTAMP+-1
2021-11-04 11:00:00.000

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
 AsTimestamp - 1 FROM
 TIMES;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! AsTimestamp + -1 FROM
 TIMES;

결과¶


ASTIMESTAMP - INTERVAL ‘1 DAY’
2021-11-04 11:00:00.000


ASTIMESTAMP + INTERVAL ‘-1 DAY’
2021-11-04 11:00:00.000

참고

For more information, see the Oracle NLS_DATE_FORMAT documentation.

TIMESTAMP_DIFF_UDF¶

The subtractions between timestamp types and dates with a timestamp and vice versa; are resolved by inserting the TIMESTAMP_DIFF_UDF user-defined function, (see the TIMESTAMP_DIFF_UDF implementation). For example

Oracle¶

SELECT AsTimeStamp - AsTimeStampTwo FROM TIMES;

SELECT AsTimeStamp - AsDateTwo FROM TIMES;

SELECT AsDateTwo - AsTimeStamp FROM TIMES;

결과¶


ASTIMESTAMP-ASTIMESTAMPTWO
+000000000 01:00:00.000000


ASTIMESTAMP-ASDATETWO
+000000000 11:00:00.000000


ASDATETWO-ASTIMESTAMP
-000000000 11:00:00.000000

Snowflake¶

SELECT AsTimeStamp - AsTimeStampTwo FROM
TIMES;

SELECT AsTimeStamp - AsDateTwo FROM
TIMES;

SELECT AsDateTwo - AsTimeStamp FROM
TIMES;

결과¶


PUBLIC.TIMESTAMP_DIFF_UDF( ASTIMESTAMP, ASTIMESTAMPTWO)
+000000000 01:00:00.00000000


PUBLIC.TIMESTAMP_DIFF_UDF( ASTIMESTAMP, ASDATETWO)
+000000000 11:00:00.00000000


PUBLIC.TIMESTAMP_DIFF_UDF( ASDATETWO, ASTIMESTAMP)
-000000000 -11:00:00.00000000

DATEDIFF_UDF¶

For those cases where there is an addition operation between a date or timestamp type and an unknown type, a user-defined function (UDF) is added. See the DATEDIFF_UDF implementation, which could be edited to perform what is required. The UDF is located in the UDFs folder. For example:

Oracle¶

SELECT ASDATE - (EXTRACT(DAY FROM ASDATE)) FROM TIMES;

SELECT ASTIMESTAMP - (EXTRACT(DAY FROM ASDATE)) FROM TIMES;

결과¶


ASDATE-(EXTRACT(DAYFROMASDATE))
2021-10-31 00:00:00.000


ASTIMESTAMP-(EXTRACT(DAYFROMASDATE))
2021-10-30 11:00:00.000

Snowflake¶

SELECT ASDATE - (EXTRACT(DAY FROM ASDATE)) FROM
TIMES;


SELECT ASTIMESTAMP - (EXTRACT(DAY FROM ASDATE)) FROM
TIMES;

결과¶


PUBLIC.DATEDIFF_UDF( ASDATE, (EXTRACT(DAY FROM ASDATE)))
2021-10-31


PUBLIC.DATEDIFF_UDF( ASTIMESTAMP, (EXTRACT(DAY FROM ASDATE)))
2021-10-30 11:00:00.000

일반적인 경우¶

경고: SSC-EWI-OR0036¶

이 경고는 피연산자 데이터 타입으로 인해 더하기 또는 빼기 연산이 올바르게 동작하지 않을 수 있는지 여부를 나타내는 데 사용됩니다. 이는 Snowflake에서의 연산 결과가 기능적으로 Oracle과 동일하지 않을 수 있음을 의미합니다. 날짜 또는 숫자 유형과 알 수 없는 유형 간의 더하기 및 빼기는 가장 일반적인 경우에 해당합니다. 예

Oracle¶

SELECT AsDate - (EXTRACT(DAY FROM ASDATE)) FROM TIMES;

Snowflake¶

SELECT AsDate - (EXTRACT(DAY FROM ASDATE)) FROM
TIMES;

이 EWI는 열 유형을 확인할 수 없는 작업에 추가됩니다. 열 유형이 INTERVAL이고 다른 간격으로만 작동하는 경우, EWI가 추가되지만 코드는 주석 처리되지 않습니다. 다음 예에서는 이 동작을 설명합니다.

Oracle¶

SELECT INTERVAL '1' DAY + interval_column FROM UNKNOWN_TABLE;

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
interval_column + INTERVAL '1 day' FROM
UNKNOWN_TABLE;

Known Issues¶

1. TIMESTAMP DIFF UDF improvement¶

The TIMESTAMP_DIFF_UDF must be improved to be able to specify the return type. It means adding a third parameter where it is possible to specify the time part, such as day, hour, or month.

2. Built-in functions as operators¶

현재, 날짜 유형을 반환하는 기본 제공 함수 사이의 날짜 작업에 대한 관리 기능은 없습니다.

3. Multiple operands¶

현재, 3개 이상의 피연산자를 사용하는 날짜 연산에 대한 관리 기능은 없으며, 작동할 수도 있지만 문제가 발생할 수 있습니다.

4. Comparison operators¶

Currently, there is no management for date operations with comparison operators, such as greater than or less than.

5. Output format¶

산술 연산의 결과 형식은 Snowflake의 ALTER SESSION SET DATE_OUTPUT_FORMAT = 'DESIRED-FORMAT'; 명령을 사용하여 변경할 수 있습니다.

6. Issues in interval operations with seconds precision¶

Some operations may differ in precision, specifically those that include intervals with seconds precision, this is because Oracle rounds depending on the precision, Snowflake’s interval does not support seconds with decimal places, to have the same result, it is necessary to change the second decimal places by milliseconds in intervals considering the rounding that Oracle performs. The following example shows this issue

Oracle¶

SELECT AsTimeStamp+INTERVAL '15.6789' SECOND(2,3) FROM times;

SELECT AsTimeStamp+INTERVAL '15.6783' SECOND(2,3) FROM times;

결과¶


ASTIMESTAMP+INTERVAL’15.6789’SECOND(2,3)
2021-11-05 11:00:15.679


ASTIMESTAMP+INTERVAL’15.6783’SECOND(2,3)
2021-11-05 11:00:15.678

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 AsTimeStamp + INTERVAL '15.6789 second'
FROM
 times;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!! AsTimeStamp + INTERVAL '15.6783 second'
FROM
 times;

결과¶


ASTIMESTAMP + INTERVAL ‘15.6789 SECOND’
2021-11-05 11:00:16.000


ASTIMESTAMP + INTERVAL ‘15.6783 SECOND’
2021-11-05 11:00:16.000


ASTIMESTAMP + INTERVAL ‘15 SECOND, 679 MILLISECOND’
2021-11-05 11:00:15.679


ASTIMESTAMP + INTERVAL ‘15 SECOND, 678 MILLISECOND’
2021-11-05 11:00:15.678

Interval UDFs 및 Snowflake 기본 간격 작업 비교¶

설명¶

다음 테이블은 간격 산술을 위한 DATEADD_UDFINTERVAL 및 DATEDIFF_UDFINTERVAL 대 Snowflake 네이티브 작업을 비교한 것입니다.

필요한 코드¶

비교 테이블의 쿼리를 실행하려면 다음 코드를 실행해야 합니다.

CREATE OR REPLACE TABLE TIMES(
AsTimeStamp TIMESTAMP,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);

INSERT INTO TIMES VALUES (
  TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
  TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
  TO_DATE('06/11/21', 'dd/mm/yy'), 
  TO_DATE('05/11/21', 'dd/mm/yy'));

CREATE TABLE UNKNOWN_TABLE(
  Unknown timestamp
);

INSERT INTO UNKNOWN_TABLE VALUES (
  TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.')
);

CREATE OR REPLACE TABLE TIMES (
  AsTimeStamp TIMESTAMP(6),
  AsTimestampTwo TIMESTAMP(6),
  AsDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
  AsDateTwo TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
  )
  COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
  ;

  INSERT INTO TIMES
  VALUES (
  TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
  TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
  TO_DATE('06/11/21', 'dd/mm/yy'),
  TO_DATE('05/11/21', 'dd/mm/yy'));

  CREATE OR REPLACE TABLE UNKNOWN_TABLE (
  Unknown TIMESTAMP(6)
  )
  COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
  ;

  INSERT INTO UNKNOWN_TABLE
  VALUES (
  TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.')
);

비교 테이블¶

Oracle¶

SELECT AsTimeStamp+INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1' MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1' MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '2' MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '2' MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsTimeStamp+INTERVAL '10' DAY FROM TIMES;
SELECT AsTimeStamp-INTERVAL '10' DAY FROM TIMES;
SELECT AsTimeStamp+INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '5' HOUR FROM TIMES;
SELECT AsTimeStamp-INTERVAL '5' HOUR FROM TIMES;
SELECT AsTimeStamp+INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsTimeStamp-INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsTimeStamp+INTERVAL '30' MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '30' MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsTimeStamp+INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsDate+INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsDate-INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsDate+INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsDate-INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsDate+INTERVAL '1' MONTH FROM TIMES;
SELECT AsDate-INTERVAL '1' MONTH FROM TIMES;
SELECT AsDate+INTERVAL '2' MONTH FROM TIMES;
SELECT AsDate-INTERVAL '2' MONTH FROM TIMES;
SELECT AsDate+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsDate-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsDate+INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsDate-INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsDate+INTERVAL '10' DAY FROM TIMES;
SELECT AsDate-INTERVAL '10' DAY FROM TIMES;
SELECT AsDate+INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '5' HOUR FROM TIMES;
SELECT AsDate-INTERVAL '5' HOUR FROM TIMES;
SELECT AsDate+INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsDate-INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsDate+INTERVAL '30' MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '30' MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsDate-INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsDate+INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsDate-INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT Unknown+INTERVAL '1-1' YEAR(2) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1-1' YEAR(2) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '2-1' YEAR(4) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '2-1' YEAR(4) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '2' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '2' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 01:10' DAY TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 01:10' DAY TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 1' DAY TO HOUR FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 1' DAY TO HOUR FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '10' DAY FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '10' DAY FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '3:05' HOUR TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '3:05' HOUR TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '5' HOUR FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '5' HOUR FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '5:10' MINUTE TO SECOND FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '5:10' MINUTE TO SECOND FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '30' MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '30' MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '333' HOUR(3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '333' HOUR(3) FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '15.6789' SECOND(2,3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '15.6789' SECOND(2,3) FROM UNKNOWN_TABLE;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+ AsTimeStamp FROM TIMES;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+AsDate FROM TIMES;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+AsDate FROM TIMES;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1' MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1' MONTH+AsDate FROM TIMES;
SELECT INTERVAL '1' MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '2' MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '2' MONTH+AsDate FROM TIMES;
SELECT INTERVAL '2' MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+AsDate FROM TIMES;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 1' DAY TO HOUR+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 1' DAY TO HOUR+AsDate FROM TIMES;
SELECT INTERVAL '1 1' DAY TO HOUR+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '10' DAY+AsTimeStamp FROM TIMES;
SELECT INTERVAL '10' DAY+AsDate FROM TIMES;
SELECT INTERVAL '10' DAY+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '3:05' HOUR TO MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '3:05' HOUR TO MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '3:05' HOUR TO MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '5' HOUR+AsTimeStamp FROM TIMES;
SELECT INTERVAL '5' HOUR+AsDate FROM TIMES;
SELECT INTERVAL '5' HOUR+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '5:10' MINUTE TO SECOND+AsTimeStamp FROM TIMES;
SELECT INTERVAL '5:10' MINUTE TO SECOND+AsDate FROM TIMES;
SELECT INTERVAL '5:10' MINUTE TO SECOND+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '30' MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '30' MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '30' MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '333' HOUR(3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '333' HOUR(3)+AsDate FROM TIMES;
SELECT INTERVAL '333' HOUR(3)+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '15.6789' SECOND(2,3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '15.6789' SECOND(2,3)+AsDate FROM TIMES;
SELECT INTERVAL '15.6789' SECOND(2,3)+Unknown FROM UNKNOWN_TABLE;

Snowflake¶

SELECT AsTimeStamp + INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT Unknown + INTERVAL '1y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '2y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '2y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '2 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '2 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 01h, 10m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 01h, 10m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 1h' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 1h' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '10 day' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '10 day' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '3h, 05m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '3h, 05m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '5 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '5 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '5m, 10s' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '5m, 10s' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '30 minute' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '30 minute' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '333 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '333 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '15.6789 second' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '15.6789 second' FROM PUBLIC.UNKNOWN_TABLE;

Snowflake UDF¶

SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''2'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''2'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''10'' DAY') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''10'' DAY') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''5'' HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''5'' HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''30'' MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''30'' MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.UNKNOWN_TABLE;

결과¶


Oracle	Snowflake 작업	UDF
2022-12-05 11:00:00.000	2022-12-05 11:00:00.000	2022-12-05 11:00:00.000
2020-10-05 11:00:00.000	2020-10-05 11:00:00.000	2020-10-05 11:00:00.000
2023-12-05 11:00:00.000	2023-12-05 11:00:00.000	2023-12-05 11:00:00.000
2019-10-05 11:00:00.000	2019-10-05 11:00:00.000	2019-10-05 11:00:00.000
2021-12-05 11:00:00.000	2021-12-05 11:00:00.000	2021-12-05 11:00:00.000
2021-10-05 11:00:00.000	2021-10-05 11:00:00.000	2021-10-05 11:00:00.000
2022-01-05 11:00:00.000	2022-01-05 11:00:00.000	2022-01-05 11:00:00.000
2021-09-05 11:00:00.000	2021-09-05 11:00:00.000	2021-09-05 11:00:00.000
2021-11-06 12:00:00.222	2021-11-06 12:00:00.222	2021-11-06 12:00:00.222
2021-11-04 09:59:59.778	2021-11-04 09:59:59.778	2021-11-04 09:59:59.778
2021-11-06 12:10:00.000	2021-11-06 12:10:00.000	2021-11-06 12:10:00.000
2021-11-04 09:50:00.000	2021-11-04 09:50:00.000	2021-11-04 09:50:00.000
2021-11-06 12:00:00.000	2021-11-06 12:00:00.000	2021-11-06 12:00:00.000
2021-11-04 10:00:00.000	2021-11-04 10:00:00.000	2021-11-04 10:00:00.000
2021-11-15 11:00:00.000	2021-11-15 11:00:00.000	2021-11-15 11:00:00.000
2021-10-26 11:00:00.000	2021-10-26 11:00:00.000	2021-10-26 11:00:00.000
2021-11-05 14:05:00.000	2021-11-05 14:05:00.000	2021-11-05 14:05:00.000
2021-11-05 07:55:00.000	2021-11-05 07:55:00.000	2021-11-05 07:55:00.000
2021-11-05 16:00:00.000	2021-11-05 16:00:00.000	2021-11-05 16:00:00.000
2021-11-05 06:00:00.000	2021-11-05 06:00:00.000	2021-11-05 06:00:00.000
2021-11-05 11:05:10.000	2021-11-05 11:05:10.000	2021-11-05 11:05:10.000
2021-11-05 10:54:50.000	2021-11-05 10:54:50.000	2021-11-05 10:54:50.000
2021-11-05 11:30:00.000	2021-11-05 11:30:00.000	2021-11-05 11:30:00.000
2021-11-05 10:30:00.000	2021-11-05 10:30:00.000	2021-11-05 10:30:00.000
2021-11-19 08:00:00.000	2021-11-19 08:00:00.000	2021-11-19 08:00:00.000
2021-10-22 14:00:00.000	2021-10-22 14:00:00.000	2021-10-22 14:00:00.000
2021-11-05 11:00:15.679	2021-11-05 11:00:16.000	2021-11-05 11:00:15.678
2021-11-05 10:59:44.321	2021-11-05 10:59:44.000	2021-11-05 11:00:15.678
2022-12-06 00:00:00.000	2022-12-06	2022-12-06
2020-10-06 00:00:00.000	2020-10-06	2020-10-06
2023-12-06 00:00:00.000	2023-12-06	2023-12-06
2019-10-06 00:00:00.000	2019-10-06	2019-10-06
2021-12-06 00:00:00.000	2021-12-06	2021-12-06
2021-12-06 00:00:00.000	2021-10-06	2021-10-06
2022-01-06 00:00:00.000	2022-01-06	2022-01-06
2021-09-06 00:00:00.000	2021-09-06	2021-09-06
2021-11-07 01:00:00.000	2021-11-07 01:00:00.222	2021-11-07
2021-11-04 22:59:59.000	2021-11-04 22:59:59.778	2021-11-04
2021-11-07 01:10:00.000	2021-11-07 01:10:00.000	2021-11-07
2021-11-04 22:50:00.000	2021-11-04 22:50:00.000	2021-11-04
2021-11-07 01:00:00.000	2021-11-07 01:00:00.000	2021-11-07
2021-11-04 23:00:00.000	2021-11-04 23:00:00.000	2021-11-04
2021-11-16 00:00:00.000	2021-11-16	2021-11-16
2021-10-27 00:00:00.000	2021-10-27	2021-10-27
2021-11-06 03:05:00.000	2021-11-06 03:05:00.000	2021-11-06
2021-11-05 20:55:00.000	2021-11-05 20:55:00.000	2021-11-05
2021-11-06 05:00:00.000	2021-11-06 05:00:00.000	2021-11-06
2021-11-05 19:00:00.000	2021-11-05 19:00:00.000	2021-11-05
2021-11-06 00:05:10.000	2021-11-06 00:05:10.000	2021-11-06
2021-11-05 23:54:50.000	2021-11-05 23:54:50.000	2021-11-05
2021-11-06 00:30:00.000	2021-11-06 00:30:00.000	2021-11-06
2021-11-05 23:30:00.000	2021-11-05 23:30:00.000	2021-11-05
2021-11-19 21:00:00.000	2021-11-19 21:00:00.000	2021-11-19
2021-10-23 03:00:00.000	2021-10-23 03:00:00.000	2021-10-23
2021-11-06 00:00:15.000	2021-11-06 00:00:16.000	2021-11-06
2021-11-05 23:59:44.000	2021-11-05 23:59:44.000	2021-11-05
2010-11-01 12:00:00.000	2010-11-01 12:00:00.000	2010-11-01 12:00:00.000
2008-09-01 12:00:00.000	2008-09-01 12:00:00.000	2008-09-01 12:00:00.000
2011-11-01 12:00:00.000	2011-11-01 12:00:00.000	2011-11-01 12:00:00.000
2007-09-01 12:00:00.000	2007-09-01 12:00:00.000	2007-09-01 12:00:00.000
2009-11-01 12:00:00.000	2009-11-01 12:00:00.000	2009-11-01 12:00:00.000
2009-09-01 12:00:00.000	2009-09-01 12:00:00.000	2009-09-01 12:00:00.000
2009-12-01 12:00:00.000	2009-12-01 12:00:00.000	2009-12-01 12:00:00.000
2009-08-01 12:00:00.000	2009-08-01 12:00:00.000	2009-08-01 12:00:00.000
2009-10-02 13:00:00.222	2009-10-02 13:00:00.222	2009-10-02 13:00:00.222
2009-09-30 10:59:59.778	2009-09-30 10:59:59.778	2009-09-30 10:59:59.778
2009-10-02 13:10:00.000	2009-10-02 13:10:00.000	2009-10-02 13:10:00.000
2009-09-30 10:50:00.000	2009-09-30 10:50:00.000	2009-09-30 10:50:00.000
2009-10-02 13:00:00.000	2009-10-02 13:00:00.000	2009-10-02 13:00:00.000
2009-09-30 11:00:00.000	2009-09-30 11:00:00.000	2009-09-30 11:00:00.000
2009-10-11 12:00:00.000	2009-10-11 12:00:00.000	2009-10-11 12:00:00.000
2009-09-21 12:00:00.000	2009-09-21 12:00:00.000	2009-09-21 12:00:00.000
2009-10-01 15:05:00.000	2009-10-01 15:05:00.000	2009-10-01 15:05:00.000
2009-10-01 08:55:00.000	2009-10-01 08:55:00.000	2009-10-01 08:55:00.000
2009-10-01 17:00:00.000	2009-10-01 17:00:00.000	2009-10-01 17:00:00.000
2009-10-01 07:00:00.000	2009-10-01 07:00:00.000	2009-10-01 07:00:00.000
2009-10-01 12:05:10.000	2009-10-01 12:05:10.000	2009-10-01 12:05:10.000
2009-10-01 11:54:50.000	2009-10-01 11:54:50.000	2009-10-01 11:54:50.000
2009-10-01 12:30:00.000	2009-10-01 12:30:00.000	2009-10-01 12:30:00.000
2009-10-01 11:30:00.000	2009-10-01 11:30:00.000	2009-10-01 11:30:00.000
2009-10-15 09:00:00.000	2009-10-15 09:00:00.000	2009-10-15 09:00:00.000
2009-09-17 15:00:00.000	2009-09-17 15:00:00.000	2009-09-17 15:00:00.000
2009-10-01 12:00:15.679	2009-10-01 12:00:16.000	2009-10-01 12:00:15.678
2009-10-01 11:59:44.321	2009-10-01 11:59:44.000	2009-10-01 11:59:44.321

Known Issues¶

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

PL SQL 데이터 타입¶

BINARY_INTEGER 데이터 타입¶

이 데이터 타입은 PLS_INTEGER 데이터 타입과 동일합니다.

PLS_INTEGER 데이터 타입¶

설명¶

PLS_INTEGER 데이터 타입은 32비트로 표현되는 -2,147,483,648 ~ 2,147,483,647 범위의 부호 있는 정수를 저장합니다. (Oracle Language Reference PLS_INTEGER 데이터 타입)

PLS_INTEGER 데이터 타입은 NUMBER 로 변환됩니다. 이 변환은 각 PLS_INTEGER 하위 유형에도 적용됩니다.

NATURAL
NATURALN
POSITIVE
POSITIVEN
SIGNTYPE
SIMPLE_INTEGER

경고

이러한 하위 유형 중 일부는 현재 SnowConvert AI에서 인식되지 않으므로 VARIANT로 변환되고 사용자 정의 유형으로 간주됩니다. 문제를 해결하기 위한 작업 항목이 이미 있습니다.

샘플 소스 패턴¶

아래 예제에서 다음 테이블과 삽입 내용을 참조하십시오.

코드¶

CREATE TABLE PLS_INTEGER_TABLE(
	COL NUMBER
);

프로시저 블록에서 PLS_INTEGER 사용법¶

Oracle¶

CREATE OR REPLACE PROCEDURE PLS_INTEGER_EXAMPLE
IS
-- PLS_INTEGER AND BINARY INTEGER ALIASES
PLS_INTEGER_VAR PLS_INTEGER;
BINARY_INTEGER_VAR BINARY_INTEGER;

NUMBER_VAR NUMBER;
BEGIN
	NUMBER_VAR := 2;

	-- maximum possible value
	PLS_INTEGER_VAR := 2147483647;
	
	-- implicit cast to number
	INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR);
	PLS_INTEGER_VAR := 2147483647;
	
	-- operations with other numeric expressions
	INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR + 1);
	INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR + NUMBER_VAR);
END;

CALL PLS_INTEGER_EXAMPLE();
SELECT * FROM PLS_INTEGER_TABLE;

결과¶


COL
2147483647
2147483648
2147483649

Snowflake¶

CREATE OR REPLACE PROCEDURE PLS_INTEGER_EXAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		-- PLS_INTEGER AND BINARY INTEGER ALIASES
		PLS_INTEGER_VAR NUMBER;
		BINARY_INTEGER_VAR NUMBER;

		NUMBER_VAR NUMBER(38, 18);
	BEGIN
		NUMBER_VAR := 2;
	-- maximum possible value
		PLS_INTEGER_VAR := 2147483647;

		-- implicit cast to number
		INSERT INTO PLS_INTEGER_TABLE(COL) VALUES (:PLS_INTEGER_VAR);
		PLS_INTEGER_VAR := 2147483647;

	-- operations with other numeric expressions
	INSERT INTO PLS_INTEGER_TABLE(COL) VALUES (:PLS_INTEGER_VAR + 1);
	INSERT INTO PLS_INTEGER_TABLE(COL) VALUES (:PLS_INTEGER_VAR + :NUMBER_VAR);
	END;
$$;

CALL PLS_INTEGER_EXAMPLE();

SELECT * FROM
	PLS_INTEGER_TABLE;

결과¶


COL
2147483647
2147483648
2147483649

Known Issues¶

1. Storage and performance features were not preserved¶

Oracle PLS_INTEGER 는 저장소 크기와 산술 작업의 성능 측면에서 몇 가지 장점이 있습니다. 이러한 기능은 Snowflake NUMBER 에 없기 때문에 에뮬레이션하지 않았습니다. 자세한 내용은 PLS_INTEGER 설명서를 참조하십시오.

문자 데이터 타입¶

문자 데이터 타입은 데이터베이스 문자 세트 또는 국가별 문자 세트에 단어와 자유 형식 텍스트인 문자(영숫자) 데이터를 저장합니다. (Oracle SQL 언어 참조 문자 데이터 타입)

CHAR 데이터 타입¶

설명¶

CHAR 데이터 타입은 데이터베이스 문자 세트에서 수정된 길이 문자 문자열을 지정합니다. (Oracle SQL Language Reference CHAR 데이터 타입)

Oracle 설명서의 설명과 같이 CHAR 데이터 타입의 크기는 길이 제약 조건이며 용량과 혼동해서는 안 됩니다. CHAR 에 저장할 수 있는 총 문자는 데이터베이스 문자 세트 및 구성에 따라 다를 수 있지만 일반적으로 허용되는 최대 크기는 2000입니다.

다음에서 확인할 수 있듯이 Snowflake에서 CHAR 유형은 VARCHAR 과 동의어입니다.

Snowflake SQL Language Reference 텍스트 데이터 타입

표준 최대 크기는 상당히 큽니다. 그러나 설명서의 설명과 같이 Snowflake VARCHAR 이 더 많은 저장소를 소비한다는 의미는 아닙니다.

VARCHAR(16777216) 열의 1문자 문자열은 한 문자만 소비합니다.

CHAR [ (size [ BYTE | CHAR ]) ]

샘플 소스 패턴¶

Create Table의 문자 데이터 타입¶

Oracle¶

CREATE TABLE char_data_types
(
	char_column1 CHAR,
	char_column2 CHAR(15),
	char_column3 CHAR(15 BYTE),
	char_column4 CHAR(15 CHAR)
);

INSERT INTO char_data_types VALUES ('H', 'Hello world', 'Hello world', 'Hello world');

Snowflake¶

CREATE OR REPLACE TABLE char_data_types
(
	char_column1 CHAR,
	char_column2 CHAR(15),
	char_column3 CHAR(15),
	char_column4 CHAR(15)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO char_data_types
VALUES ('H', 'Hello world', 'Hello world', 'Hello world');

문자 열에서 데이터 검색하기¶

Oracle¶

SELECT * FROM char_data_types;

결과¶


CHAR_COLUMN1	CHAR_COLUMN2	CHAR_COLUMN3	CHAR_COLUMN4
H	Hello world	Hello world	Hello world

Snowflake¶

SELECT * FROM
char_data_types;

결과¶


CHAR_COLUMN1	CHAR_COLUMN2	CHAR_COLUMN3	CHAR_COLUMN4
H	Hello world	Hello world	Hello world

참고

Oracle에서는 열 정의에서 결정된 수정된 크기에 맞게 값이 빈 공간으로 채워집니다. 반면에 Snowflakes는 동적 크기(길이 제한 유지)를 사용하여 값을 저장합니다.

CHAR 의 내부 데이터 타입 확인¶

서두에서 언급했듯이 Snowflake는 내부적으로 CHAR 유형 열에 VARCHAR 를 사용하며, 테이블을 설명하면 이를 확인할 수 있습니다.

Oracle¶

Snowflake¶

참고

길이 제한은 유지되지만, 각 DBMS 에서 열이 사용하는 메모리 양은 다릅니다.

각 열의 크기를 바이트 단위로 검색하기:¶

Oracle¶

SELECT 
LENGTHB(char_column1),
LENGTHB(char_column2),
LENGTHB(char_column3),
LENGTHB(char_column4)  
FROM char_data_types;

결과¶


LENGTHB(CHAR_COLUMN1)	LENGTHB(CHAR_COLUMN2)	LENGTHB(CHAR_COLUMN3)	LENGTHB(CHAR_COLUMN4)
1	15	15	15

Snowflake¶

SELECT
OCTET_LENGTH(char_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column3) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column4) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
char_data_types;

결과¶


OCTET_LENGTH(CHAR_COLUMN1)	OCTET_LENGTH(CHAR_COLUMN2)	OCTET_LENGTH(CHAR_COLUMN3)	OCTET_LENGTH(CHAR_COLUMN4)
1	11	11	11

참고

이러한 미세한 차이 외에도 데이터의 무결성은 그대로 유지됩니다.

Known Issues¶

1. 일부 기본 제공 함수에서 얻은 결과는 다를 수 있습니다

이전 섹션에서 설명한 것처럼 열에 기본 제공 함수를 사용하면 다른 결과가 검색되는 경우가 있을 수 있습니다. 예를 들어, 열의 길이를 구합니다.

NCHAR 데이터 타입¶

설명¶

NCHAR 데이터 타입은 국가 문자 세트에서 고정 길이 문자 문자열을 지정합니다. (Oracle SQL Language Reference NCHAR)

NCHAR 을 사용하면 특수 문자를 Unicode와 함께 저장할 수 있으므로 이러한 특수 문자를 저장하는 데 더 많은 비트가 필요할 수 있으므로 기본적으로 NCHAR 문자 세트는 AL16UTF16 이며, 일반적으로 CHAR 에 대한 일반적인 문자 데이터 세트는 AL32UTF8 입니다.

Snowflake에서 NCHAR 은 NCHAR 로 유지되지만, 백그라운드에서 Snowflake는 VARCHAR 을 사용합니다. CHAR 과 관련된 변환 정보는 NCHAR 에도 유효합니다.

NCHAR [ (size) ]

샘플 소스 패턴¶

Create Table의 Nchar 데이터 타입¶

Oracle¶

CREATE TABLE nchar_data_types
(
	nchar_column1 NCHAR,
	nchar_column2 NCHAR(5)
);

INSERT INTO nchar_data_types VALUES ('ភ', 'ភាសាខ');

Snowflake¶

CREATE OR REPLACE TABLE nchar_data_types
(
	nchar_column1 NCHAR,
	nchar_column2 NCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO nchar_data_types
VALUES ('ភ', 'ភាសាខ');

참고

Oracle에서 이러한 값을 CHAR 열에 같은 크기로 삽입하려고 하면 오류가 트리거됩니다. 값이 열에 비해 너무 큽니다.

Nchar 열에서 정보 검색하기¶

Oracle¶

SELECT * FROM nchar_data_types;

결과¶


NCHAR_COLUMN1	NCHAR_COLUMN2
ភ	ភាសាខ

Snowflake¶

SELECT * FROM
nchar_data_types;

결과¶


NCHAR_COLUMN1	NCHAR_COLUMN2
ភ	ភាសាខ

각 열의 바이트 단위 크기 검색하기¶

Oracle¶

SELECT 
LENGTHB(nchar_column1),
LENGTHB(nchar_column2)
FROM nchar_data_types;

결과¶


LENGTHB(NCHAR_COLUMN1)	LENGTHB(NCHAR_COLUMN2)

                 2|                    10|

Snowflake¶

SELECT
OCTET_LENGTH(nchar_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(nchar_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
nchar_data_types;

결과¶


OCTET_LENGTH(NCHAR_COLUMN1)	OCTET_LENGTH(NCHAR_COLUMN2)

                      3|                         15|

열 선언에 지정된 숫자는 바이트 단위가 아닌 문자 단위의 크기이므로 특수 문자를 저장하는 데 더 많은 공간이 사용되는 것을 볼 수 있습니다.

참고

Snowflake에서 VARCHAR 은 UTF-8 을 사용하며, 크기는 1, 2, 3 또는 4 바이트 단위로 표현할 수 있는 Unicode 문자에 따라 달라질 수 있습니다. 이 경우 캄보디아어 문자는 3바이트를 사용하여 저장됩니다.

참고

이러한 미세한 차이 외에도 데이터의 무결성은 그대로 유지됩니다.

Known Issues¶

1. 일부 기본 제공 함수에서 얻은 결과는 다를 수 있습니다

이전 섹션에서 설명한 것처럼 열에 기본 제공 함수를 사용하면 다른 결과가 검색되는 경우가 있을 수 있습니다. 예를 들어, 열의 길이를 구합니다.

NVARCHAR2 데이터 타입¶

설명¶

NVARCHAR2 데이터 타입은 국가 문자 세트에서 가변 길이의 문자열을 지정합니다. (Oracle SQL Language Reference NVARCHAR2)

NVARCHAR2 (size)

NVARCHAR2 를 사용하면 특수 문자를 Unicode와 함께 저장할 수 있으므로 이러한 특수 문자를 저장하는 데 더 많은 비트가 필요할 수 있으므로 기본적으로 NVARCHAR2 문자 세트는 AL16UTF16 이며, 일반적으로 VARCHAR2 에 대한 일반적인 문자 데이터 세트는 AL32UTF8 입니다.

NVARCHAR에서 Snowflake VARCHAR로의 변환 VARCHAR2와 관련된 변환 정보도 NVARCHAR2에 유효합니다.

NVARCHAR2 (size)

샘플 소스 패턴¶

Create Table의 Nvarchar2 데이터 타입¶

Oracle¶

CREATE TABLE nvarchar2_data_types
(
	nvarchar2_column NVARCHAR2 (5)
);

INSERT INTO nvarchar2_data_types VALUES ('ភាសាខ');

Snowflake¶

CREATE OR REPLACE TABLE nvarchar2_data_types
	(
		nvarchar2_column VARCHAR(5)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO nvarchar2_data_types
	VALUES ('ភាសាខ');

참고

Oracle에서 이러한 값을 VARCHAR2 열에 같은 크기로 삽입하려고 하면 오류가 트리거됩니다. 값이 열에 비해 너무 큽니다.

Nchar 열에서 정보 검색하기¶

Oracle¶

SELECT * FROM nvarchar2_data_types;

결과¶


NVARCHAR2_COLUMN
ភាសាខ

Snowflake¶

SELECT * FROM
nvarchar2_data_types;

결과¶


NVARCHAR2_COLUMN
ភាសាខ

각 열의 바이트 단위 크기 검색하기¶

Oracle¶

SELECT 
LENGTHB(nvarchar2_column)
FROM nvarchar2_data_types;

결과¶


LENGTHB(NVARCHAR2_COLUMN)
10

Snowflake¶

SELECT
OCTET_LENGTH(nvarchar2_column) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
nvarchar2_data_types;

결과¶


OCTET_LENGTH(NVARCHAR2_COLUMN)
15

열 선언에 지정된 숫자는 바이트 단위가 아닌 문자 단위의 크기이므로 특수 문자를 저장하는 데 더 많은 공간이 사용되는 것을 볼 수 있습니다.

참고

이러한 미세한 차이 외에도 데이터의 무결성은 그대로 유지됩니다.

Known Issues¶

1. 일부 기본 제공 함수에서 얻은 결과는 다를 수 있습니다

이전 섹션에서 설명한 것처럼 열에 기본 제공 함수를 사용하면 다른 결과가 검색되는 경우가 있을 수 있습니다. 예를 들어, 열의 길이를 구합니다.

VARCHAR 데이터 타입¶

설명¶

Oracle은 설명서의 설명과 같이 VARCHAR 대신 VARCHAR2 을 사용할 것을 권장합니다.

Oracle SQL Language reference Varchar

그럼에도, 구문은 ANSI, DB2 및 SQL/DS 데이터 타입을 사용하여 구문 분석되고 변환됩니다.

VARCHAR2 데이터 타입¶

설명¶

VARCHAR2 데이터 타입은 데이터베이스 문자 세트에 변수 길이 문자열을 지정합니다. (Oracle SQL Language Reference VARCHAR2)

Oracle 설명서의 설명과 같이 VARCHAR2 데이터 타입의 크기는 길이 제약 조건이며 용량과 혼동해서는 안 됩니다. VARCHAR2 에 저장할 수 있는 총 문자는 데이터베이스 문자 세트 및 구성에 따라 다를 수 있지만 일반적으로 허용되는 최대 크기는 4000자입니다.

VARCHAR2 는 기본적으로 더 많은 바이트/문자를 저장할 수 있는 Snowflake VARCHAR 로 변환됩니다. 어느 쪽이든 사용되는 메모리는 Oracle에서와 마찬가지로 열에 저장된 값의 크기를 사용하여 가변적입니다.

VARCHAR2 (size [ BYTE | CHAR ])

샘플 소스 패턴¶

Create Table의 Varchar2 데이터 타입¶

Oracle¶

CREATE TABLE varchar2_data_types
(
	varchar2_column1 VARCHAR2(5),
	varchar2_column2 VARCHAR2(5 BYTE),
	varchar2_column3 VARCHAR2(5 CHAR)
);

INSERT INTO varchar2_data_types VALUES ('H', 'Hello', 'Hell');

Snowflake¶

CREATE OR REPLACE TABLE varchar2_data_types
	(
		varchar2_column1 VARCHAR(5),
		varchar2_column2 VARCHAR(5),
		varchar2_column3 VARCHAR(5)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO varchar2_data_types
	VALUES ('H', 'Hello', 'Hell');

varchar 열에서 데이터 검색하기¶

Oracle¶

SELECT * FROM varchar2_data_types;

결과¶


VARCHAR2_COLUMN1	VARCHAR2_COLUMN2	VARCHAR2_COLUMN3
H	Hello	Hell

Snowflake¶

SELECT * FROM
varchar2_data_types;

결과¶


VARCHAR2_COLUMN1	VARCHAR2_COLUMN2	VARCHAR2_COLUMN3
H	Hello	Hell

열의 변수 크기 검토하기¶

Oracle¶

SELECT
LENGTHB(varchar2_column1),
LENGTHB(varchar2_column2),
LENGTHB(varchar2_column3) 
FROM VARCHAR2_DATA_TYPES;

결과¶


LENGTHB(VARCHAR2_COLUMN1)	LENGTHB(VARCHAR2_COLUMN2)	LENGTHB(VARCHAR2_COLUMN3)
1	5	4

Snowflake¶

SELECT
OCTET_LENGTH(varchar2_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(varchar2_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(varchar2_column3) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
VARCHAR2_DATA_TYPES;

결과¶


OCTET_LENGTH(VARCHAR2_COLUMN1)	OCTET_LENGTH(VARCHAR2_COLUMN2)	OCTET_LENGTH(VARCHAR2_COLUMN3)
1	5	4

Known Issues¶

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

LOB 데이터 타입¶

설명¶

기본 제공 LOB 데이터 타입 BLOB, CLOB, NCLOB(내부에 저장됨) 및 BFILE(외부에 저장됨)은 텍스트, 이미지, 비디오, 공간 데이터와 같은 대규모 비정형 데이터를 저장할 수 있습니다(Oracle SQL 언어 참조 LOB 데이터 타입).

BFILE
BLOB
CLOB
NCLOB

경고

LOB 데이터 타입은 Snowflake에서 지원되지 않습니다. Snowflake 설명서에 따라 CLOB을 VARCHAR로, BLOB을 BINARY로 변환하는 것이 좋지만 몇 가지 제한 사항이 있습니다. {% endhint %}

경고

테이블에 대한 LOB 속성도 Snowflake에서 지원되지 않습니다. {% endhint %}

BFILE 데이터 타입

Description

데이터베이스 외부에 저장된 큰 이진 파일에 대한 로케이터가 포함되어 있습니다. 데이터베이스 서버에 있는 외부 LOBs 에 대한 바이트 스트림 I/O 액세스를 활성화합니다. BFILE 열 또는 특성은 서버 파일 시스템의 이진 파일에 대한 포인터 역할을 하는 BFILE 로케이터를 저장합니다. 로케이터는 디렉터리 이름과 파일 이름을 유지합니다. (Oracle SQL Language Reference BFILE 데이터 타입).

경고

BFILE 데이터 타입은 Snowflake에서 지원되지 않습니다. VARCHAR 이 대신 사용됩니다.

샘플 소스 패턴¶

Create Table의 Bfile 데이터 타입¶

경고

Oracle BFILE columns are used to store a locator with the directory and filename. They are changed to Snowflake VARCHAR to store the directory and filename into the column. However, loading the content of the file must be done manually.

Oracle¶

--Create Table
CREATE TABLE bfile_table 
(
    col1 BFILE 
);

--Insert Bfilename
INSERT INTO bfile_table VALUES (
    BFILENAME('mydirectory', 'myfile.png')
);

--Select
SELECT * FROM bfile_table;

결과¶


COL1
[BFILE:myfile.png]

Snowflake¶

--Create Table
CREATE OR REPLACE TABLE bfile_table
    (
        col1
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/!!!
    VARCHAR
    )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

--Insert Bfilename
INSERT INTO bfile_table
VALUES (PUBLIC.BFILENAME_UDF('mydirectory', 'myfile.png')
);

--Select
SELECT * FROM
    bfile_table;

결과¶


COL1
mydirectory\myfile.png

경고

UDF 는 BFILENAME() 을 대체하기 위해 추가되었습니다.

UDF 추가됨

CREATE OR REPLACE FUNCTION PUBLIC.BFILENAME_UDF (DIRECTORYNAME STRING, FILENAME STRING)
RETURNS STRING
LANGUAGE SQL
IMMUTABLE 
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DIRECTORYNAME || '\\' || FILENAME
$$;

Known Issues¶

1. No access to the DBMS_LOB built-in package¶

LOB 데이터 타입은 Snowflake에서 지원되지 않으므로 DBMS_LOB 함수에 상응하는 기능이 없으며 아직 구현된 해결 방법이 없습니다.

BLOB 데이터 타입¶

설명¶

BLOB 데이터 타입은 비정형 이진 대형 오브젝트를 저장합니다. BLOB 오브젝트는 문자 집합 의미 체계가 없는 비트스트림으로 생각할 수 있습니다. (Oracle SQL Language Reference BLOB 데이터 타입).

경고

BLOB 데이터 타입은 Snowflake에서 지원되지 않습니다. BINARY 가 대신 사용됩니다.

샘플 소스 패턴¶

Create Table의 BLOB¶

Oracle¶

CREATE TABLE blobtable( blob_column BLOB, empty_column BLOB );

INSERT INTO blobtable VALUES (NULL, EMPTY_BLOB());

Snowflake¶

CREATE OR REPLACE TABLE blobtable ( blob_column BINARY,
empty_column BINARY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO blobtable
VALUES (NULL, TO_BINARY(' '));

데이터 검색하기¶

Oracle¶

SELECT * FROM blobtable;

결과¶


BLOB_COLUMN	EMPTY_COLUMN
[NULL]	[BLOB]

Snowflake¶

SELECT * FROM
blobtable;

결과¶


BLOB_COLUMN	EMPTY_COLUMN
NULL

함수 예제¶

경고

이 예는 SnowConvert AI의 변환이 아니며, Oracle BLOB과 Snowflake BINARY 간의 기능적 동등성을 보여주는 데만 사용됩니다.

경고

Snowflake는 “utl_raw.cast_to_raw” 및 “DBMS_LOB.SUBSTR” 함수를 사용합니다. 이러한 함수에 대한 변환은 현재 SnowConvert 에서 지원되지 않습니다.

Oracle¶

INSERT INTO blobtable VALUES(
utl_raw.cast_to_raw('hello world'), EMPTY_BLOB());

SELECT DBMS_LOB.SUBSTR(blob_column) AS result
FROM blobtable;

결과¶


RESULT
[NULL]
hello world

Snowflake¶

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, TO_BINARY(' '));


SELECT
SUBSTR(blob_column, 1) AS result
FROM
blobtable;

결과¶


RESULT
[NULL]
hello world

Known Issues¶

1. The difference in max length BLOB (Oracle) and BINARY (Snowflake)¶

Oracle BLOB 열의 최대 크기는 (4기가바이트-1) * (데이터베이스 블록 크기) 이지만, Snowflake BINARY 은 8MB 로 제한됩니다.

2. Empty value with EMPTY_BLOB¶

EMPTY_BLOB() 을 사용하여 열을 초기화하면 빈 LOB 위치가 반환됩니다. 변환 후 열은 ‘ ‘ 문자열을 반환합니다.

3. No access to the DBMS_LOB built-in package¶

LOB 데이터 타입은 Snowflake에서 지원되지 않으므로 DBMS_LOB 함수에 상응하는 기능이 없으며 아직 구현된 해결 방법이 없습니다.

CLOB 데이터 타입¶

설명¶

단일 바이트 또는 멀티바이트 문자를 포함하는 문자 크기 오브젝트입니다. 데이터베이스 문자 세트를 사용하여 고정 너비 및 가변 너비 문자 세트가 모두 지원됩니다. (Oracle SQL Language Reference CLOB 데이터 타입).

경고

CLOB 데이터 타입은 Snowflake에서 지원되지 않습니다. VARCHAR 이 대신 사용됩니다.

샘플 소스 패턴¶

Create Table의 CLOB¶

Oracle¶

CREATE TABLE clobtable ( clob_column CLOB, empty_column CLOB );

INSERT INTO clobtable VALUES ( 'THIS IS A TEST', EMPTY_CLOB() );

Snowflake¶

CREATE OR REPLACE TABLE clobtable ( clob_column VARCHAR,
empty_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO clobtable
VALUES ( 'THIS IS A TEST', TO_VARCHAR(' - '));

데이터 검색하기¶

Oracle¶

SELECT * FROM clobtable;

결과¶


CLOB_COLUMN	EMPTY_COLUMN
THIS IS A TEST

Snowflake¶

SELECT * FROM
clobtable;

결과¶


CLOB_COLUMN	EMPTY_COLUMN
THIS IS A TEST	-

Known Issues¶

1. The difference in max length CLOB (Oracle) and VARCHAR (Snowflake)¶

Oracle CLOB 열의 최대 크기는 (4기가바이트-1) * (데이터베이스 블록 크기) 이지만, Snowflake VARCHAR 은 16MB 로 제한됩니다.

2. Empty value with EMPTY_CLOB¶

EMPTY_CLOB() 을 사용하여 열을 초기화하면 빈 LOB 위치가 반환됩니다. Snowflake에서 변환 후 열은 ‘-’ 문자열을 반환합니다.

3. No access to the DBMS_LOB built-in package¶

LOB 데이터 타입은 Snowflake에서 지원되지 않으므로 DBMS_LOB 함수에 상응하는 기능이 없으며 아직 구현된 해결 방법이 없습니다.

NCLOB 데이터 타입¶

설명¶

유니코드 문자를 포함하는 문자 대형 오브젝트입니다. 데이터베이스 국가 문자 세트를 사용하여 고정 너비 및 가변 너비 문자 세트가 모두 지원됩니다. (Oracle SQL Language Reference NCLOB 데이터 타입).

경고

NCLOB 데이터 타입은 Snowflake에서 지원되지 않습니다. VARCHAR 이 대신 사용됩니다.

샘플 소스 패턴¶

Create Table의 NCLOB¶

Oracle¶

CREATE TABLE nclobtable ( nclob_column NCLOB, empty_column NCLOB );

INSERT INTO nclobtable VALUES ( 'THIS IS A TEST', EMPTY_CLOB() );

Snowflake¶

CREATE OR REPLACE TABLE nclobtable ( nclob_column VARCHAR,
empty_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO nclobtable
VALUES ( 'THIS IS A TEST', TO_VARCHAR(' - '));

데이터 검색하기¶

Oracle¶

SELECT * FROM nclobtable;

결과¶


NCLOB_COLUMN	EMPTY_COLUMN
THIS IS A TEST

Snowflake¶

SELECT * FROM
nclobtable;

결과¶


NCLOB_COLUMN	EMPTY_COLUMN
THIS IS A TEST	-

Known Issues¶

1. The difference in max length CLOB (Oracle) and VARCHAR (Snowflake)¶

Oracle NCLOB 열의 최대 크기는 (4기가바이트 - 1) * (데이터베이스 블록 크기) 이지만, Snowflake VARCHAR 은 16MB 로 제한됩니다.

2. Empty value with EMPTY_CLOB¶

EMPTY_CLOB() 을 사용하여 열을 초기화하면 빈 LOB 위치가 반환됩니다. 변환 후 열은 ‘-’ 문자열을 반환합니다.

3. No access to the DBMS_LOB built-in package¶

LOB 데이터 타입은 Snowflake에서 지원되지 않으므로 DBMS_LOB 함수에 상응하는 기능이 없으며 아직 구현된 해결 방법이 없습니다.

SnowConvert AI - Oracle - Oracle 기본 제공 데이터 타입¶

확장 데이터 타입¶

설명¶

Known Issues¶

1. MAX STRING SIZE not recognized¶

관련 EWIs¶

JSON 데이터 타입¶

설명¶

샘플 소스 패턴¶

Create Table의 JSON 데이터 타입을 열로 사용¶

Oracle¶

결과¶

Snowflake¶

Known Issues¶

권장 사항¶

1. JSON Data Type translation workaround¶

Oracle¶

결과 1¶

결과 2¶

Snowflake¶

결과 1¶

결과 2¶

관련 EWIs¶

LONG 데이터 타입¶

샘플 소스 패턴¶

Create Table의 long¶

Oracle¶

Snowflake¶

긴 열에서 데이터 검색하기¶

Oracle¶

결과¶

Snowflake¶

결과¶

Known Issues¶

1. The max length of long (Oracle) and varchar (Snowflake) are different¶

2. Cast of Long column¶

Oracle¶

관련 EWIs¶

RAW 및 LONG RAW 데이터 타입¶

설명¶

샘플 소스 패턴¶

Create Table의 원시 및 Long 원시¶

Oracle¶

Snowflake CREATE OR REPLACE TABLE raw_table¶

원시 및 Long 원시 열에서 데이터 검색하기¶

Oracle¶

결과¶

Snowflake¶

결과¶

Known Issues¶

관련 EWIs¶

숫자 데이터 타입¶

설명¶

산술 작업에 대한 참고 사항¶

FLOAT 데이터 타입¶

설명¶

Notes on arithmetic operations

샘플 소스 패턴¶

Create Table의 부동 소수점 데이터 타입¶

Oracle¶

Snowflake¶

FLOAT¶

Oracle¶

결과¶

Snowflake¶

결과¶

FLOAT ( p )¶

Oracle¶

결과¶

Snowflake¶

결과¶

Known Issues¶

1. FLOAT data type with precision¶

관련 EWIs¶

NUMBER 데이터 타입¶

설명¶

Notes on arithmetic operations

샘플 소스 패턴¶

Create Table의 데이터 타입 번호 매기기¶

Oracle¶