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

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE PLS_INTEGER_EXAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		-- PLS_INTEGER AND BINARY INTEGER ALIASES
		PLS_INTEGER_VAR NUMBER;
		BINARY_INTEGER_VAR NUMBER;

		NUMBER_VAR NUMBER(38, 18);
	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|

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.