What's the difference between pls_integer and binary_integer?

Question

I've inherited some code which is going to be the base for some additional work. Looking at the stored procs, I see quite a lot of associative-arrays.

Some of these are indexed by binary_integers, some by pls_integers. Are there any differences between the two?

I had a look at the documentation, but apart from this line:

The PL/SQL data types PLS_INTEGER and BINARY_INTEGER are identical. For simplicity, this document uses PLS_INTEGER to mean both PLS_INTEGER and BINARY_INTEGER.

I couldn't find any difference between the two. So what's the difference? Are both around for historical/compatibility reasons?

I'm using Oracle 10gR2

Community · Accepted Answer · 2020-06-20 09:12:55Z

44

Historical reasons. They used to be different before 10g:

On 8i and 9i, PLS_INTEGER was noticeably faster than BINARY_INTEGER.

When it comes to declaring and manipulating integers, Oracle offers lots of options, including:

INTEGER - defined in the STANDARD package as a subtype of NUMBER, this datatype is implemented in a completely platform-independent fashion, which means that anything you do with NUMBER or INTEGER variables should work the same regardless of the hardware on which the database is installed.

BINARY_INTEGER - defined in the STANDARD package as a subtype of INTEGER. Variables declared as BINARY_INTEGER can be assigned values between -2³¹+1 .. 2³¹-1, aka -2,147,483,647 to 2,147,483,647. Prior to Oracle9i Database Release 2, BINARY_INTEGER was the only indexing datatype allowed for associative arrays (aka, index-by tables), as in:

  TYPE my_array_t IS TABLE OF VARCHAR2(100) 
  INDEX BY BINARY_INTEGER

PLS_INTEGER - defined in the STANDARD package as a subtype of BINARY_INTEGER. Variables declared as PLS_INTEGER can be assigned values between -2³¹+1 .. 2³¹-1, aka -2,147,483,647 to 2,147,483,647. PLS_INTEGER operations use machine arithmetic, so they are generally faster than NUMBER and INTEGER operations. Also, prior to Oracle Database 10g, they are faster than BINARY_INTEGER. In Oracle Database 10g, however, BINARY_INTEGER and PLS_INTEGER are now identical and can be used interchangeably.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 14, 2011 at 7:37

Thilo

264k107 gold badges527 silver badges674 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thilo Over a year ago

Here is the 9i docs, which mention the difference, but without going into much detail: download.oracle.com/docs/cd/B10500_01/appdev.920/a96624/… Anyway, it's obsolete now.

logeekal Over a year ago

But look at the link below : oracle.com/technetwork/database/features/plsql/documentation/… Binary_Integer implementation is based on Hash like data structure so should it be faster for searching and PLS_INTEGER for ordering as they are implemented on B*-Trees

Sathyajith Bhat · Accepted Answer · 2011-12-20 14:19:53Z

9

binary_integer and pls_integer both are same. Both are PL/SQL datatypes with range -2,147,648,467 to 2,147,648,467.

Compared to integer and binary_integer pls_integer very fast in excution. Because pls_intger operates on machine arithmetic and binary_integer operes on library arithmetic.

pls_integer comes from oracle10g.

binary_integer allows indexing integer for assocative arrays prior to oracle9i.

Clear example:

SET TIMING ON

declare
  num   integer := 0;
  incr  integer := 1;
  limit integer := 100000000;
begin
  while num < limit loop
    num := num + incr;
  end loop;
end;
PL/SQL procedure successfully completed.

Elapsed: 00:00:20.23
ex:2
declare
  num   binary_integer := 0;
  incr  binary_integer := 1;
  limit binary_integer := 100000000;
begin
  while num < limit loop
    num := num + incr;
  end loop;
end;
/ 

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.81
ex:3
declare
  num   pls_integer := 0;
  incr  pls_integer := 1;
  limit pls_integer := 100000000;
begin
  while num < limit loop
    num := num + incr;
  end loop;
end;
/

edited Dec 20, 2011 at 14:19

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

answered Dec 20, 2011 at 14:10

nagu

911 silver badge1 bronze badge

1 Comment

ocarlsen Over a year ago

Range is actually -2,147,483,647 to 2,147,483,647.

Ben · Accepted Answer · 2012-10-26 21:03:06Z

4

Another difference between pls_integer and binary_integer is that when calculations involving a pls_integer overflow the PL/SQL engine will raise a run time exception. But, calculations involving a binary_integer will not raise an exception even if there is an overflow.

edited Oct 26, 2012 at 21:03

Ben

53.1k36 gold badges133 silver badges156 bronze badges

answered Apr 29, 2012 at 15:24

Gebru Welay

3774 silver badges8 bronze badges

1 Comment

alexeionin Over a year ago

Isn't true since oracle 11.2g. See example, which throws ORA-01426 error: declare limit binary_integer := 2147483647; begin limit := limit + 1; end; /

Collectives™ on Stack Overflow

What's the difference between pls_integer and binary_integer?

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related