My toolset has generated this Oracle statement:
type Taccnts is table of accnts%rowtype index by binary_integer;My queries are:
- Indexing means fast access to rows... what is the function of binary_integer in the above statement?
- 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> / 999 JONES PL/SQL procedure successfully completed. SQL>
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.