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 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-0007: Element with missing dependencies.

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