Synonyms

For more information check the Synonym translation reference.

Synonyms used inside PL/SQL blocks are changed to the referenced object and the Schema will be added if necessary.

Implicit Schema added

When the procedure or function is inside an schema and the synonym is inside that schema but it is being used without the schema, the converted code will add the schema.

Oracle

CREATE TABLE schema_one.TABLE_TEST1(
    COL1 INTEGER,
    COL2 DATE DEFAULT SYSDATE
    );

CREATE OR REPLACE SYNONYM schema_one.MY_SYNONYM1 FOR schema_one.TABLE_TEST1;

create or replace procedure schema_one.procedure1  as
returnval integer;
begin
    select col1 into returnval from my_synonym1;
end; Some code

Snowflake

-- **** CASE 1: Should add implicit schema for this synonym (schema_one) ****
CREATE OR REPLACE TABLE schema_one.TABLE_TEST1 (
COL1 INTEGER,
COL2 DATE DEFAULT CURRENT_DATE);

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

CREATE OR REPLACE PROCEDURE schema_one.procedure1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // Helper Code
   let RETURNVAL;
   [RETURNVAL] = EXEC(`SELECT col1 from schema_one.TABLE_TEST1`);
$$;

Schema of referenced object added

When the synonym references an object that is in a specific schema, the schema name will be added to the referenced object.

Oracle

CREATE OR REPLACE SYNONYM MY_SYNONYM2 FOR schema_one.TABLE_TEST1;

create or replace procedure procedure2  as
returnval integer;
begin
    select col1 into returnval from my_synonym2;
end;

Snowflake

-- ** MSC-WARNING - MSCEWI3025 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
---- **** CASE 2: Should add the schema of the referenced object

--CREATE OR REPLACE SYNONYM MY_SYNONYM2 FOR schema_one.TABLE_TEST1
                                                                ;

CREATE OR REPLACE PROCEDURE PUBLIC.procedure2 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // Helper Code

   let RETURNVAL;
   [RETURNVAL] = EXEC(`SELECT col1 from schema_one.TABLE_TEST1`);
$$;

Related EWIs

1. MSCEWI3025: Synonyms changed to original object name.