Create Synonym

In this page you could find information about Synonyms.

Create Synonym

Synonyms are not supported in Snowflake. The references to the Synonyms will be changed for the original Object.

-- Oracle
CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;

-- Snowlake
-- ** MSC-WARNING - MSCEWI3025 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;

Example 1: Synonym that refers to a table.

Oracle source code:

CREATE TABLE TABLITA
(
    COLUMN1 NUMBER
);

CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;

SELECT * FROM B.TABLITA_SYNONYM WHERE B.TABLITA_SYNONYM.COLUMN1 = 20;

Snowflake migrated code: you'll notice that the SELECT originally refers to a synonym, but now it refers to the table that points the synonym.

CREATE OR REPLACE TABLE PUBLIC.TABLITA (
COLUMN1 NUMBER (38,18));

-- ** MSC-WARNING - MSCEWI3025 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA
                                                       ;
SELECT * FROM PUBLIC.TABLITA WHERE PUBLIC.TABLITA.COLUMN1 = 20;

Example 2: Synonym that refers to another synonym.

Oracle source code:

-- Oracle
CREATE TABLE TABLITA
(
    COLUMN1 NUMBER
);

CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;
CREATE OR REPLACE SYNONYM C.TABLITA_SYNONYM2 FOR B.TABLITA_SYNONYM;

SELECT * FROM C.TABLITA_SYNONYM2 WHERE C.TABLITA_SYNONYM2.COLUMN1 = 20;

UPDATE C.TABLITA_SYNONYM2 SET COLUMN1 = 10;

INSERT INTO C.TABLITA_SYNONYM2 VALUES (1);

Snowflake migrated code: you'll notice that originally the SELECT , UPDATE, INSERT refers to a synonym, and now it refers to the atomic object, which is a table.

-- Oracle
CREATE OR REPLACE TABLE PUBLIC.TABLITA (
COLUMN1 NUMBER (38,18));

-- ** MSC-WARNING - MSCEWI3025 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA
                                                       ;

-- ** MSC-WARNING - MSCEWI3025 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM C.TABLITA_SYNONYM2 FOR B.TABLITA_SYNONYM
                                                                  ;

SELECT * FROM PUBLIC.TABLITA WHERE PUBLIC.TABLITA.COLUMN1 = 20;

UPDATE PUBLIC.TABLITA SET COLUMN1 = 10;

INSERT INTO PUBLIC.TABLITA VALUES (1);

Example 3: Synonym that refers to a view

Oracle Source Code

CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;

CREATE OR REPLACE SYNONYM C.TABLITA_SYNONYM2 FOR B.TABLITA_SYNONYM;

CREATE VIEW VIEW_ORGINAL AS SELECT * FROM C.TABLITA_SYNONYM2;

CREATE OR REPLACE SYNONYM VIEW_SYNONYM FOR VIEW_ORGINAL;

SELECT * FROM VIEW_SYNONYM;

Snowflake migrated code: you'll notice that the SELECT originally refers to a synonym, and now it refers to the atomic objects, which is a view.

-- ** MSC-WARNING - MSCEWI3025 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA
                                                       ;

-- ** MSC-WARNING - MSCEWI3025 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM C.TABLITA_SYNONYM2 FOR B.TABLITA_SYNONYM
                                                                  ;

CREATE OR REPLACE VIEW PUBLIC.VIEW_ORGINAL
AS
SELECT * FROM PUBLIC.TABLITA;

-- ** MSC-WARNING - MSCEWI3025 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM VIEW_SYNONYM FOR VIEW_ORGINAL
                                                       ;

SELECT * FROM PUBLIC.VIEW_ORGINAL;

Synonyms transformation can be enabled or disabled from the UI settings or through a flag in the CLI.