Constants

Translation spec for Package Constants

Description

PACKAGE CONSTANTS can be declared either in the package declaration or in the PACKAGE BODY. When a package constant is used in a procedure, a new variable is declared with the same name and value as the constant, so the resulting code is pretty similar to the input.

Oracle Constant declaration Syntax

constant CONSTANT datatype [NOT NULL] { := | DEFAULT } expression ;

Sample Source Patterns

Sample auxiliary code

Oracle

IN -> Oracle_01.sql

create table table1(id number);

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE table1 (id NUMBER(38, 18) /*** 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"}}'
;

Oracle

Query

IN -> Oracle_02.sql

CREATE OR REPLACE PACKAGE PKG1 AS
    PROCEDURE procedure1;
    package_constant CONSTANT NUMBER:= 9999;
END PKG1;

CREATE OR REPLACE PACKAGE BODY PKG1 AS
    PROCEDURE procedure1 AS
    BEGIN
        INSERT INTO TABLE1(ID) VALUES(package_constant);
    END;
END PKG1;

CALL PKG1.procedure1();

SELECT * FROM TABLE1;

Result

|ID  |
|----|
|9999|

Snowflake

Query

OUT -> Oracle_02.sql

CREATE SCHEMA IF NOT EXISTS PKG1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE PKG1.procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        PACKAGE_CONSTANT NUMBER := 9999;
    BEGIN
        INSERT INTO TABLE1(ID) VALUES(:PACKAGE_CONSTANT);
    END;
$$;

CALL PKG1.procedure1();

SELECT * FROM
    TABLE1;

Result

|ID  |
|----|
|9999|

Note that thePROCEDURE definition is being removed since it is not required in Snowflake.

Known Issues

No issues were found.

Related EWIs

1. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.