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 a schema and the synonym is inside that schema, but it is being used without the schema, the converted code will add the schema.

Oracle

IN -> Oracle_01.sql

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; 

Snowflake

OUT -> Oracle_01.sql

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

--        --** SSC-FDM-OR0005 - 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 VARCHAR
        LANGUAGE SQL
        COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
        EXECUTE AS CALLER
        AS
        $$
        DECLARE
                returnval integer;
        BEGIN
                select col1 into
                    :returnval
                from
                    schema_one.TABLE_TEST1;
        END;
        $$;

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

IN -> Oracle_02.sql

--Additional Params: -t JavaScript
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

OUT -> Oracle_02.sql

----** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM MY_SYNONYM2 FOR schema_one.TABLE_TEST1
                                                                ;

CREATE OR REPLACE PROCEDURE procedure2 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.
    
    let RETURNVAL;
    [RETURNVAL] = EXEC(`SELECT
   col1
from
   schema_one.TABLE_TEST1`);
$$;

Related EWIs

1. SSC-FDM-OR0005: Synonyms are not supported in Snowflake but references to this synonym were changed by the original object name.

2. SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior.