ora-22160 element at index 1 does not exist oracle

ora-22160 element at index 1 does not exist oracle

Case :- When we are using PL/SQL collection with FORALL statement it's process collection using an index number. When one of index number are missing you can get this error.

Solve :- In addition from oracle 10g Oracle added two new clauses to the FORALL statement: INDICES OF and VALUES OF. They allow you to avoid the restriction on using densely filled collections. Instead of using an IN range of values, you can point to a collection (usually, but not necessarily, the same collection that is referenced inside the FORALL's DML statement) and say, in effect, "Use only those index values defined in that other collection" (INDICES OF) or "Use only index values that are found in the elements of that other collection" (VALUES OF).

CREATE OR REPLACE PROCEDURE DER_element_at_index_1_ERROR
IS
   TYPE REC_DEP IS RECORD
    (
      DEPARTMENT_ID    NUMBER(4),
      DEPARTMENT_NAME  VARCHAR2(30),
      MANAGER_ID       NUMBER(6),
      LOCATION_ID      NUMBER(4)
    );

    TYPE TABLE_DEP IS TABLE OF REC_DEP INDEX BY PLS_INTEGER;

    T_DEP TABLE_DEP;
   
   TYPE TT_DEPARTMENT_ID IS TABLE OF NUMBER(4) INDEX BY PLS_INTEGER;
   TYPE TT_DEPARTMENT_NAME IS TABLE OF VARCHAR2(300) INDEX BY PLS_INTEGER;
   TYPE TT_MANAGER_ID  IS TABLE OF NUMBER(18,3) INDEX BY PLS_INTEGER;
   TYPE TT_LOCATION_ID IS TABLE OF NUMBER(18,3) INDEX BY PLS_INTEGER;

    T_DEPARTMENT_ID    TT_DEPARTMENT_ID;
    T_DEPARTMENT_NAME    TT_DEPARTMENT_NAME;
    T_MANAGER_ID    TT_MANAGER_ID;
    T_LOCATION_ID    TT_LOCATION_ID;
   
    V_INDEX NUMBER(10):=0;
   
    V_SQL   VARCHAR2(3000);
    TYPE RC IS REF CURSOR;
   
    C1 RC;
BEGIN

    V_SQL:='SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID FROM DEPARTMENTS';
   
    OPEN C1 FOR V_SQL;
   
    LOOP
    FETCH C1 BULK COLLECT INTO T_DEP LIMIT 200;

     FOR I IN T_DEP.FIRST .. T_DEP.LAST LOOP

    V_INDEX:=V_INDEX+1;

    T_DEPARTMENT_ID(V_INDEX):=T_DEP(I).DEPARTMENT_ID;
    T_DEPARTMENT_NAME(V_INDEX):=T_DEP(I).DEPARTMENT_NAME;
    T_MANAGER_ID(V_INDEX):=T_DEP(I).MANAGER_ID;
    T_LOCATION_ID(V_INDEX):=T_DEP(I).LOCATION_ID;

    END LOOP;

    EXIT WHEN C1%NOTFOUND;

    END LOOP;
   

    FORALL INDX IN T_DEPARTMENT_ID.FIRST .. T_DEPARTMENT_ID.LAST
    INSERT INTO D(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
                VALUES(T_DEPARTMENT_ID(INDX),T_DEPARTMENT_NAME(INDX),T_MANAGER_ID(INDX),T_LOCATION_ID(INDX));
    
    COMMIT;
      T_DEPARTMENT_ID.DELETE;
      T_DEPARTMENT_NAME.DELETE;
      T_MANAGER_ID.DELETE;
      T_LOCATION_ID.DELETE;
               
    EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

BEGIN
    FOR I IN 1 .. 5 LOOP
   
    DER_element_at_index_1_ERROR;
   
    END LOOP;
   
END;

ORA-22160: element at index [1] does not exist10

CREATE OR REPLACE PROCEDURE DER_element_at_index_1_ERROR
IS
   TYPE REC_DEP IS RECORD
    (
      DEPARTMENT_ID    NUMBER(4),
      DEPARTMENT_NAME  VARCHAR2(30),
      MANAGER_ID       NUMBER(6),
      LOCATION_ID      NUMBER(4)
    );

    TYPE TABLE_DEP IS TABLE OF REC_DEP INDEX BY PLS_INTEGER;

    T_DEP TABLE_DEP;
   
   TYPE TT_DEPARTMENT_ID IS TABLE OF NUMBER(4) INDEX BY PLS_INTEGER;
   TYPE TT_DEPARTMENT_NAME IS TABLE OF VARCHAR2(300) INDEX BY PLS_INTEGER;
   TYPE TT_MANAGER_ID  IS TABLE OF NUMBER(18,3) INDEX BY PLS_INTEGER;
   TYPE TT_LOCATION_ID IS TABLE OF NUMBER(18,3) INDEX BY PLS_INTEGER;

    T_DEPARTMENT_ID    TT_DEPARTMENT_ID;
    T_DEPARTMENT_NAME    TT_DEPARTMENT_NAME;
    T_MANAGER_ID    TT_MANAGER_ID;
    T_LOCATION_ID    TT_LOCATION_ID;
   
    V_INDEX NUMBER(10):=0;
   
    V_SQL   VARCHAR2(3000);
    TYPE RC IS REF CURSOR;
   
    C1 RC;
BEGIN

    V_SQL:='SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID FROM DEPARTMENTS';
   
    OPEN C1 FOR V_SQL;
   
    LOOP
    FETCH C1 BULK COLLECT INTO T_DEP LIMIT 200;

     FOR I IN T_DEP.FIRST .. T_DEP.LAST LOOP

    V_INDEX:=V_INDEX+1;

    T_DEPARTMENT_ID(V_INDEX):=T_DEP(I).DEPARTMENT_ID;
    T_DEPARTMENT_NAME(V_INDEX):=T_DEP(I).DEPARTMENT_NAME;
    T_MANAGER_ID(V_INDEX):=T_DEP(I).MANAGER_ID;
    T_LOCATION_ID(V_INDEX):=T_DEP(I).LOCATION_ID;

    END LOOP;

    EXIT WHEN C1%NOTFOUND;

    END LOOP;
   

    FORALL INDX IN T_DEPARTMENT_ID.FIRST .. T_DEPARTMENT_ID.LAST
    INSERT INTO D(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
                VALUES(T_DEPARTMENT_ID(INDX),T_DEPARTMENT_NAME(INDX),T_MANAGER_ID(INDX),T_LOCATION_ID(INDX));
    
    COMMIT;
      T_DEPARTMENT_ID.DELETE;
      T_DEPARTMENT_NAME.DELETE;
      T_MANAGER_ID.DELETE;
      T_LOCATION_ID.DELETE;
     
      V_INDEX:=0;
               
    EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;


SQL>  BEGIN
  2      FOR I IN 1 .. 5 LOOP
  3
  4      DER_element_at_index_1_ERROR;
  5
  6      END LOOP;
  7
  8   END;
  9   /

PL/SQL procedure successfully completed.

How Oracle Database Works

The following example describes Oracle Database operations at the most basic level. 
This illustrates an Oracle Database configuration where the user and associated server 
process are on separate computers, connected through a network.

1.   An instance has started on the computer running Oracle Database, often called the 
     host or database server. 

2.   A computer running an application (a local computer or client workstation) runs 
     an application in a user process. The client application attempts to establish a 
     connection to the server using the proper Oracle Net Services driver.

3.   The server is running the proper Oracle Net Services driver. The server detects the 
     connection request from the application and creates a dedicated server process on 
     behalf of the user process. 

4.   The user runs a SQL statement and commits the transaction. For example, the user 
     changes a name in a row of a table. 

5.   The server process receives the statement and checks the shared pool (an SGA 
     component) for any shared SQL area that contains a similar SQL statement. If a 
     shared SQL area is found, then the server process checks the user's access 
     privileges to the requested data, and the existing shared SQL area is used to 
     process the statement. If not, then a new shared SQL area is allocated for the 
     statement, so it can be parsed and processed. 

6.   The server process retrieves any necessary data values, either from the actual 
     datafile (table) or those stored in the SGA. 

7.   The server process modifies data in the system global area. The database writer 
     process (DBWn) writes modified blocks permanently to disk when doing so is 
     efficient. Because the transaction is committed, the log writer process (LGWR) 
     immediately records the transaction in the redo log file. 

8.   If the transaction is successful, then the server process sends a message across the 
     network to the application. If it is not successful, then an error message is 
     transmitted. 

9.   Throughout this entire procedure, the other background processes run, watching 
     for conditions that require intervention. In addition, the database server manages 
     other users' transactions and prevents contention between transactions that 
     request the same data. 

Find Top 10 SQL In Oracle Database

Using this you can find out TOP SQL Statement from your database.


SELECT *
  FROM (  SELECT username,
                 LAST_LOAD_TIME,
                 sql_text,
                 sql_id,
                 elapsed_time,
                 cpu_time,
                 user_io_wait_time
            FROM sys.v_$sqlarea s, dba_users u
           WHERE     u.USER_ID = s.PARSING_USER_ID
                 AND TRUNC (LAST_LOAD_TIME) = TRUNC (SYSDATE)
                 AND u.username =user
        ORDER BY 6 DESC)
 WHERE ROWNUM < 10;

Filter data from Oracle PL/SQL Collection.

This is very interesting for me. I have fine a solution for filter data form collection. 

CREATE TYPE dept_t AS OBJECT
(
DEPARTMENT_ID NUMBER (10) ,
DEPARTMENT_NAME VARCHAR2 (300) ,
MANAGER_ID NUMBER (10) ,
LOCATION_ID NUMBER (10)) ;

CREATE TYPE dept_tab IS TABLE OF dept_t;

create or replace PROCEDURE DPR_COLLECTION_FILTER IS

T_RC dept_tab:=dept_tab();
T_DATA dept_tab:=dept_tab();

BEGIN

SELECT dept_t (DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID) bulk collect INTO T_RC FROM DEPARTMENTS;

SELECT dept_t (DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID) bulk collect INTO T_DATA FROM TABLE(T_RC) t WHERE t.DEPARTMENT_ID > 30;

FOR I IN T_DATA.FIRST .. T_DATA.LAST LOOP

DBMS_OUTPUT.PUT_LINE(T_DATA(I).DEPARTMENT_ID|| ' >> '||T_DATA(I).DEPARTMENT_NAME);

END LOOP;

END;