Problem solve Get help with specific problems with your technologies, process and projects.

Binary_integers and indexes

My toolset has generated this Oracle statement:

  type Taccnts is table of accnts%rowtype index by binary_integer;
My queries are:
  1. Indexing means fast access to rows... what is the function of binary_integer in the above statement?
  2. If it creates an index, on which column it is going to create it, and why on that particular column?

This is a PL/SQL table type definition. I think of it as the PL/SQL equivalent of an array. Once this type is defined it can be used to subsequently declare a variable of that type (for example: accnts_array Taccnts; ). The INDEX BY BINARY_INTEGER is basically defining the bounds of the array (which goes from -32 billion or thereabouts to +32 billion). You can then use the array variable in code and store values in any position in the array. Since the type has been created using %rowtype, you're basically creating an array of records. There is no true index created. The syntax simply uses the words INDEX BY to represent how the array is bounded (or subscripted).

Here's an example:

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> declare
  2    type temps is table of emp%rowtype index by binary_integer ;
  3    emp_array  temps ;
  4  begin
  5    emp_array(1).empno := 999;
  6    emp_array(1).ename := 'JONES' ;
  7    dbms_output.put_line(emp_array(1).empno) ;
  8    dbms_output.put_line(emp_array(1).ename) ;
  9  end ;
 10  .
SQL> set serveroutput on 
SQL> /

PL/SQL procedure successfully completed.


For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.