Home » SQL & PL/SQL » SQL & PL/SQL » Bulk insert problems (10g)  () 1 Vote
Bulk insert problems [message #352188] Mon, 06 October 2008 09:41 Go to next message
krish3104
Messages: 1
Registered: October 2008
Junior Member
Hi All,

I am getting the following error 'ORA-22160: element at index [1] does not exist.' My code is given below. Any help is greatly appreciated.

CREATE OR REPLACE PROCEDURE CLR_PRD.clr_translation (
ip_from_date VARCHAR2,
ip_to_date VARCHAR2
)
AS
TYPE calling_party_name_t IS TABLE OF USAGE.calling_party_name%TYPE
INDEX BY PLS_INTEGER;

TYPE call_date_t IS TABLE OF USAGE.call_date%TYPE
INDEX BY PLS_INTEGER;

TYPE call_direction_t IS TABLE OF USAGE.call_direction%TYPE
INDEX BY PLS_INTEGER;

TYPE connect_time_t IS TABLE OF USAGE.CONNECT_TIME%TYPE
INDEX BY PLS_INTEGER;

TYPE data_source_ind_t IS TABLE OF USAGE.data_source_ind%TYPE
INDEX BY PLS_INTEGER;

TYPE disposition_ind_t IS TABLE OF USAGE.disposition_ind%TYPE
INDEX BY PLS_INTEGER;

TYPE int_dom_ind_t IS TABLE OF USAGE.int_dom_ind%TYPE
INDEX BY PLS_INTEGER;

TYPE jurisdiction_t IS TABLE OF USAGE.jurisdiction%TYPE
INDEX BY PLS_INTEGER;

TYPE mou_t IS TABLE OF USAGE.mou%TYPE
INDEX BY PLS_INTEGER;

TYPE offhk_ind_t IS TABLE OF USAGE.offhk_ind%TYPE
INDEX BY PLS_INTEGER;

TYPE operational_tn_t IS TABLE OF USAGE.operational_tn%TYPE
INDEX BY PLS_INTEGER;

TYPE opposing_node_tn_t IS TABLE OF USAGE.opposing_node_tn%TYPE
INDEX BY PLS_INTEGER;

TYPE orig_country_t IS TABLE OF USAGE.orig_country%TYPE
INDEX BY PLS_INTEGER;

TYPE orig_place_t IS TABLE OF USAGE.orig_place%TYPE
INDEX BY PLS_INTEGER;

TYPE orig_st_t IS TABLE OF USAGE.orig_st%TYPE
INDEX BY PLS_INTEGER;

TYPE priv_ind_t IS TABLE OF USAGE.priv_ind%TYPE
INDEX BY PLS_INTEGER;

TYPE record_unq_key_t IS TABLE OF USAGE.record_unq_key%TYPE
INDEX BY PLS_INTEGER;

TYPE srvc_ftr_1_t IS TABLE OF USAGE.srvc_ftr_1%TYPE
INDEX BY PLS_INTEGER;

TYPE srvc_ftr_2_t IS TABLE OF USAGE.srvc_ftr_2%TYPE
INDEX BY PLS_INTEGER;

TYPE srvc_ftr_3_t IS TABLE OF USAGE.srvc_ftr_3%TYPE
INDEX BY PLS_INTEGER;

TYPE term_country_t IS TABLE OF USAGE.term_country%TYPE
INDEX BY PLS_INTEGER;

TYPE term_place_t IS TABLE OF USAGE.term_place%TYPE
INDEX BY PLS_INTEGER;

TYPE term_st_t IS TABLE OF USAGE.term_st%TYPE
INDEX BY PLS_INTEGER;

TYPE type_of_feature_t IS TABLE OF USAGE.type_of_feature%TYPE
INDEX BY PLS_INTEGER;

TYPE tz_offset_t IS TABLE OF USAGE.TZ_OFFSET%TYPE
INDEX BY PLS_INTEGER;

TYPE user_fld_1_t IS TABLE OF USAGE.user_fld_1%TYPE
INDEX BY PLS_INTEGER;

TYPE user_fld_2_t IS TABLE OF USAGE.user_fld_2%TYPE
INDEX BY PLS_INTEGER;

TYPE user_fld_3_t IS TABLE OF USAGE.user_fld_3%TYPE
INDEX BY PLS_INTEGER;

TYPE user_fld_4_t IS TABLE OF USAGE.user_fld_4%TYPE
INDEX BY PLS_INTEGER;

TYPE user_fld_5_t IS TABLE OF USAGE.user_fld_5%TYPE
INDEX BY PLS_INTEGER;

TYPE wirels_wireln_t IS TABLE OF USAGE.wirels_wireln%TYPE
INDEX BY PLS_INTEGER;

calling_party_name calling_party_name_t;
call_date call_date_t;
call_direction call_direction_t;
connecttime connect_time_t;
data_source_ind data_source_ind_t;
disposition_ind disposition_ind_t;
int_dom_ind int_dom_ind_t;
jurisdiction jurisdiction_t;
mou mou_t;
offhk_ind offhk_ind_t;
operational_tn operational_tn_t;
opposing_node_tn opposing_node_tn_t;
orig_country orig_country_t;
orig_place orig_place_t;
orig_st orig_st_t;
priv_ind priv_ind_t;
record_unq_key record_unq_key_t;
srvc_ftr_1 srvc_ftr_1_t;
srvc_ftr_2 srvc_ftr_2_t;
srvc_ftr_3 srvc_ftr_3_t;
term_country term_country_t;
term_place term_place_t;
term_st term_st_t;
type_of_feature type_of_feature_t;
tzoffset tz_offset_t;
user_fld_1 user_fld_1_t;
user_fld_2 user_fld_2_t;
user_fld_3 user_fld_3_t;
user_fld_4 user_fld_4_t;
user_fld_5 user_fld_5_t;
wirels_wireln wirels_wireln_t;

-- baa_adj_code_c baa_adj_code_t;
CURSOR cutm_bad_cur
IS
SELECT *
FROM cutm_bad
WHERE reason_cd IN ('0', 'E')
AND call_date BETWEEN TO_DATE (ip_from_date || ' 00:00:00',
'mm/dd/yy HH24:MI:SS'
)
AND TO_DATE (ip_to_date || ' 23:59:59',
'mm/dd/yy HH24:MI:SS'
);

TYPE cutm_bad_cur_t IS TABLE OF cutm_bad_cur%ROWTYPE
INDEX BY PLS_INTEGER;

m_cutm_bad_cur cutm_bad_cur_t;

CURSOR dvc_trans_cur
IS
SELECT DISTINCT dt_id, dt_field_name, dt_translation_type,
dt_cutm_field_id, cutm_bad_field, dt_cutm_field_value,
dt_field_value
FROM dvc_translation, cutm_map
WHERE dt_source = 'CABS-CUTM' AND cutm_pos(+) =
dt_cutm_field_id
ORDER BY dt_id;

m_dvc_trans_cur dvc_trans_cur%ROWTYPE;
bulk_error VARCHAR2 (500);
counter NUMBER (10) := 0;
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
-- m_counter NUMBER (10) := 0;
-- stmt VARCHAR2 (256);
-- stmt1 VARCHAR2 (256);
BEGIN
OPEN cutm_bad_cur;

LOOP
m_cutm_bad_cur.DELETE;
calling_party_name.DELETE;
call_date.DELETE;
call_direction.DELETE;
connecttime.DELETE;
data_source_ind.DELETE;
disposition_ind.DELETE;
int_dom_ind.DELETE;
jurisdiction.DELETE;
mou.DELETE;
offhk_ind.DELETE;
operational_tn.DELETE;
opposing_node_tn.DELETE;
orig_country.DELETE;
orig_place.DELETE;
orig_st.DELETE;
priv_ind.DELETE;
record_unq_key.DELETE;
srvc_ftr_1.DELETE;
srvc_ftr_2.DELETE;
srvc_ftr_3.DELETE;
term_country.DELETE;
term_place.DELETE;
term_st.DELETE;
type_of_feature.DELETE;
tzoffset.DELETE;
user_fld_1.DELETE;
user_fld_2.DELETE;
user_fld_3.DELETE;
user_fld_4.DELETE;
user_fld_5.DELETE;
wirels_wireln.DELETE;

FETCH cutm_bad_cur
BULK COLLECT INTO m_cutm_bad_cur LIMIT 10;

--DBMS_OUTPUT.PUT_LINE ( 'HERE ' );
EXIT WHEN m_cutm_bad_cur.COUNT = 0;

FOR j IN m_cutm_bad_cur.FIRST .. m_cutm_bad_cur.LAST
LOOP
--m_cutm_bad_cur.extend();
FOR m_dvc_trans_cur IN dvc_trans_cur
LOOP
--Apply the trnaslation logic here
IF m_dvc_trans_cur.dt_field_name = 'OPERATIONAL_TN'
THEN
IF m_dvc_trans_cur.dt_translation_type = 'M'
AND m_dvc_trans_cur.cutm_bad_field IS NOT NULL
THEN
operational_tn (j) := m_cutm_bad_cur (j).term_tn;
DBMS_OUTPUT.put_line ( 'operational_tn (j) = '
|| operational_tn (j)
);
END IF;

IF m_dvc_trans_cur.dt_translation_type = 'D'
THEN
operational_tn (j) := m_dvc_trans_cur.dt_field_value;
END IF;

IF m_dvc_trans_cur.dt_translation_type = 'T'
AND m_dvc_trans_cur.cutm_bad_field IS NOT NULL
THEN
IF m_dvc_trans_cur.cutm_bad_field =
m_dvc_trans_cur.dt_cutm_field_value
THEN
operational_tn (j) := m_dvc_trans_cur.dt_field_value;
END IF;
END IF;
END IF;
/* Logi for translating other fields .
.
.
.
.
.
.
.
.
.*/
END LOOP;
--trns cursor
END LOOP;

BEGIN
DBMS_OUTPUT.put_line ('HERE-insert ');
FORALL i IN m_cutm_bad_cur.FIRST .. m_cutm_bad_cur.LAST SAVE EXCEPTIONS
-- FORALL i IN INDICES OF m_cutm_bad_cur SAVE EXCEPTIONS
INSERT INTO USAGE
(operational_tn, disposition_ind,
call_direction, opposing_node_tn,
record_unq_key, call_date, CONNECT_TIME,
mou, TZ_OFFSET, type_of_feature,
orig_place, orig_st, orig_country,
term_place, term_st, term_country,
data_source_ind, int_dom_ind,
wirels_wireln, priv_ind, srvc_ftr_1,
srvc_ftr_2, srvc_ftr_3, jurisdiction,
offhk_ind, calling_party_name,
user_fld_1, user_fld_2, user_fld_3,
user_fld_4, user_fld_5
)
VALUES (operational_tn (i), disposition_ind (i),
call_direction (i), opposing_node_tn (i),
record_unq_key (i), call_date (i), connecttime (i),
mou (i), tzoffset (i), type_of_feature (i),
orig_place (i), orig_st (i), orig_country (i),
term_place (i), term_st (i), term_country (i),
data_source_ind (i), int_dom_ind (i),
wirels_wireln (i), priv_ind (i), srvc_ftr_1 (i),
srvc_ftr_2 (i), srvc_ftr_3 (i), jurisdiction (i),
offhk_ind (i), calling_party_name (i),
user_fld_1 (i), user_fld_2 (i), user_fld_3 (i),
user_fld_4 (i), user_fld_5 (i)
);
COMMIT;
EXCEPTION
WHEN bulk_errors
THEN
FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
bulk_error :=
'BULK - '
|| SQL%BULK_EXCEPTIONS (j).ERROR_INDEX
|| ', '
|| SQLERRM (-SQL%BULK_EXCEPTIONS (j).ERROR_CODE);
DBMS_OUTPUT.put_line ('bulk_error = ' || bulk_error);
END LOOP;
END;
EXIT WHEN cutm_bad_cur%NOTFOUND;
END LOOP;

CLOSE cutm_bad_cur;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
IF cutm_bad_cur%ISOPEN
THEN
CLOSE cutm_bad_cur;
END IF;

DBMS_OUTPUT.put_line (SQLERRM);
END;
/
Re: Bulk insert problems [message #352198 is a reply to message #352188] Mon, 06 October 2008 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Bulk insert problems [message #352261 is a reply to message #352188] Mon, 06 October 2008 22:25 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

ORA-22160: element at index [string] does not exist
Cause: Collection element at the given index does not exist.
Action: Specify the index of an element which exists.


Btw ,

Why cant you use %ROWTYPE construct instead of %TYPE

TYPE calling_party_name_t IS TABLE OF USAGE.calling_party_name%TYPE
INDEX BY PLS_INTEGER;



Thumbs Up
Rajuvan.
Previous Topic: ORA-00932 when converting column_expression from user_ind_expressions using to_lob
Next Topic: How to update CLOB column having more than 4000 chars
Goto Forum:
  


Current Time: Fri Dec 09 08:02:35 CST 2016

Total time taken to generate the page: 0.26262 seconds