PLS_INTEGER Data Type

Description

The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits. (Oracle Language Reference PLS_INTEGER Data Type)

The PLS_INTEGER datatype is transformed to NUMBER. This transformation also applies for each PLS_INTEGER subtype:

• NATURAL

• NATURALN

• POSITIVE

• POSITIVEN

• SIGNTYPE

• SIMPLE_INTEGER

Some of these subtypes are currently not recognized by SnowConvert so they are converted to VARIANT and considered user-defined types. There is already a work item to fix the issue.

Sample Source Patterns

Please, consider the following table and its inserts for the examples below:

Code

CREATE TABLE PLS_INTEGER_TABLE(
	COL NUMBER
);

PLS_INTEGER usage in procedural blocks

Oracle

Query

CREATE OR REPLACE PROCEDURE PLS_INTEGER_EXAMPLE
IS
-- PLS_INTEGER AND BINARY INTEGER ALIASES
PLS_INTEGER_VAR PLS_INTEGER;
BINARY_INTEGER_VAR BINARY_INTEGER;

NUMBER_VAR NUMBER;
BEGIN
	NUMBER_VAR := 2;

	-- maximum possible value
	PLS_INTEGER_VAR := 2147483647;
	
	-- implicit cast to number
	INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR);
	PLS_INTEGER_VAR := 2147483647;
	
	-- operations with other numeric expressions
	INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR + 1);
	INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR + NUMBER_VAR);
END;

CALL PLS_INTEGER_EXAMPLE();
SELECT * FROM PLS_INTEGER_TABLE;

Result

|COL       |
|----------|
|2147483647|
|2147483648|
|2147483649|

Snowflake

Query

CREATE OR REPLACE PROCEDURE PUBLIC.PLS_INTEGER_EXAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      -- PLS_INTEGER AND BINARY INTEGER ALIASES
      PLS_INTEGER_VAR PLS_INTEGER;
      BINARY_INTEGER_VAR BINARY_INTEGER;

      NUMBER_VAR NUMBER (38,18);
   BEGIN
      NUMBER_VAR := 2;

      -- maximum possible value
      PLS_INTEGER_VAR := 2147483647;

      -- implicit cast to number
      INSERT INTO PUBLIC.PLS_INTEGER_TABLE (COL) VALUES (:PLS_INTEGER_VAR);
      PLS_INTEGER_VAR := 2147483647;

      -- operations with other numeric expressions
      INSERT INTO PUBLIC.PLS_INTEGER_TABLE (COL) VALUES (/*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/
:PLS_INTEGER_VAR + 1);
INSERT INTO PUBLIC.PLS_INTEGER_TABLE (COL) VALUES (:PLS_INTEGER_VAR + :NUMBER_VAR);
   END;
$$;

CALL PUBLIC.PLS_INTEGER_EXAMPLE();

SELECT * FROM PUBLIC.PLS_INTEGER_TABLE;

Result

|COL       |
|----------|
|2147483647|
|2147483648|
|2147483649|

Known Issues

1. Storage and performance features were not preserved

Oracle PLS_INTEGER has some advantages in terms of storage size and performance in arithmetic operations. These features were not emulated because Snowflake NUMBER does not have them. For more information, check the PLS_INTEGER documentation.

Related EWIs

1. MSCEWI1036: Data type converted to another data type.