Error Ora 22160
Contents |
May 2007 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 As Published In May/June 2007 TECHNOLOGY: PL/SQL Practices On the Old, the New, and ORA-22160 By Steven Feuerstein Best practices for
Element At Index 1 Does Not Exist Oracle
managing old and new information and preventing FORALL errors I want to audit old and new values in trigger oracle updates to a table, including the before and after values of each column in my table. To do this, I oracle :old would love to pass :NEW and :OLD as arguments to procedures, but that doesn't seem to work. I'd really like to avoid hard-coding column names, because then I run into compilation issues when
Oracle Trigger For Each Row
columns are added, dropped, or renamed. Is this possible? This is how I am currently doing things: CREATE TABLE load_a ( a1 VARCHAR2 (10), a2 VARCHAR2 (10) ) / CREATE OR REPLACE TRIGGER ins_load_a AFTER UPDATE ON scott.load_a FOR EACH ROW DECLARE a_rec scott.load_a%ROWTYPE; BEGIN a_rec.a1 := :OLD.a1; a_rec.a2 := :OLD.a2; save_old_values (a_rec); END; / The bad news is that up through Oracle Database 10g, you
Oracle Triggers
cannot pass :OLD and :NEW as arguments to procedures. The good news is that at least you don't have to write all the code needed to accomplish your goal. Let's take a step back: you want to pass the old and new values available inside a trigger to a stored procedure to process the data (write the information to a log table, execute validation logic, and so on). You cannot pass :OLD and :NEW as records; instead, you must copy the individual fields of those pseudorecords to a real record that can be passed to the procedure. It certainly is tedious and time-consuming to write that code yourself, especially for tables with many columns. Fortunately, it is quite easy to generate the code you desire. I offer you the gen_audit_trigger_text procedure shown in Listing 1 to help you achieve your goal. I ran this program for the employees table and, after some formatting, had the resulting code shown in Listing 2. Code Listing 1: gen_audit_trigger_text CREATE OR REPLACE PROCEDURE gen_audit_trigger_text ( table_in IN VARCHAR2 , owner_in IN VARCHAR2 := USER , program_name_in IN VARCHAR2 := 'process_data' ) IS c_rowtype CONSTANT VARCHAR2 (100) := table_in || '%ROWT
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 ora-06512 using an index number. When one of index number are missing you can
Oracle News
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 http://www.oracle.com/technetwork/issue-archive/2007/07-may/o37plsql-096409.html 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), http://rajiboracle.blogspot.com/2014/04/ora-22160-element-at-index-1-does-not.html 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_elemen
learning requires not only hearing and applying but also forgetting and then remembering again. - John Gray Sending data back Using the bulk capabilities you can not http://allthingsoracle.com/bulk-processing-in-oracle-part-2/ only fetch data in one roundtrip to the database, but you can also http://www.allinterview.com/showanswers/41317/ora-22160-element-at-index-string-does-not-exist.html use it to send data back to the database in a single roundtrip. Using the FORALL statement. You can provide the FORALL statement with one or more collections and it will send the data back to the database in one pass. The syntax for the FORALL statement is: FORALL index IN [ lower_bound oracle trigger .. upper_bound | INDICES OF indexing_collection | VALUES OF indexing_collection ] [ SAVE EXCEPTIONS] sql_statement; If you are using lower_bound .. upper_bound you collections needs to be dense, because the SQL statement will be applied to every index of the collection. If the is no element defined at a certain index, an exception will be raised. ORA-22160: element at index [N] does not exist If you are error ora 22160 working with a sparse collection, you can still use FORALL, but you should use either INDICES OF or VALUES OF. INDICES OF uses all the index values defined in the collection. FORALL indx IN INDICES OF sparse_collection INSERT INTO the_table VALUES sparse_collection (indx); VALUES OF uses the values defined in the collection as index values. This way you can use one (indexing) collection as a driver, to insert values from another (binding) collection. FORALL indx IN VALUES OF pointer_collection INSERT INTO the_table VALUES binding_collection (indx); Instead of using the indices of the first collection, we are using the values of this collection as pointers to the second collection. If you want to use the returning clause in a SQL statement be sure you BULK COLLECT into a collection because you get more than one result back. One result for every row in your collection. The following statement takes a collection of departments to delete all the employees in those departments returning the employee numbers which have been deleted. FORALL indx IN depts.FIRST .. depts.LAST DELETE FROM emp WHERE deptno IN depts (indx) RETURNING empno BULK COLLECT INTO empnos; Save exceptions You might encounter an error when performing DML u
Oracle Errors Suggest New Category ORA-22160: element at index [string] does not exist Question Posted / guest 1 Answers 7196 Views ICICI, I also Faced E-Mail Answers Answer / guest Cause: Collection element at the given index does not exist. Action: Specify the index of an element which exists. Please add more information about this Error Is This Answer Correct ? 3 Yes 3 No Post New Answer Categories Oracle Errors (16400)SQLServer Errors (34)MySQL Errors (10) More Oracle Errors Interview Questions PCC-01007: WORKING-STORAGE SECTION not found 1 Answers ORA-31688: Worker process string failed during startup. 1 Answers ORA-31668: Timeout before worker process string finished initialization. 1 Answers ORA-08210: Requested I/O error 1 Answers SQL*Loader-00283: file processing string "string" ignored for INFILE * 1 Answers ORA-31621: error creating master process 1 Answers DRG-10841: invalid SCORE argument supplied to FUZZY 1 Answers PLS-00412: list of values not allowed as argument to this function or procedure 1 Answers TNS-12158: TNS:could not initialize parameter subsystem 1 Answers SQL*Loader-00254: Cannot have DISCARDFILE specs here when multiple data files 1 Answers ORA-19951: cannot modify control file until DBNEWID is completed 1 Answers ORA-07628: smsfre: sga not mapped 1 Answers For more Oracle Errors Interview Questions Click Here Copyright Policy | Terms of Service | Site Map | Contact Us Copyright © 2016 ALLInterview.com. All Rights Reserved.