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

create table table1(id number);

Snowflake

CREATE OR REPLACE TABLE PUBLIC.table1 (
id NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/
);

Oracle

Query

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

CREATE SCHEMA IF NOT EXISTS PKG1;

/*** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE ***/
  /*	PROCEDURE procedure1;*/

/*** MSC-WARNING - MSCEWI3054 - PACKAGE CONSTANTS IN STATEFUL PACKAGE ARE DECLARED INSIDE THE PROCEDURE OR FUNCTIONS THAT USE THEM ***/
  /*	package_constant CONSTANT NUMBER(38, 18) := 9999;*/

CREATE OR REPLACE PROCEDURE PKG1.procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	DECLARE
		PACKAGE_CONSTANT NUMBER:= 9999;
	BEGIN
		INSERT INTO PUBLIC.TABLE01(ID) VALUES(:PACKAGE_CONSTANT);
	END;
$$;

CALL PKG1.procedure1();

SELECT * FROM
	PUBLIC.TABLE01;

Result

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

Known Issues

No issues were found.

Related EWIs

No related EWIs.