OraFAQ Forum: SQL & PL/SQL » ORA-22160: element at index [2] does not exist

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22160: element at index [2] does not exist (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-22160: element at index [2] does not exist [message #321194]

Mon, 19 May 2008 06:58

NewLife
Messages: 170
Registered: April 2008

Senior Member

When i run the following procedure, i get the error shown below the code, can anyone advise me why this is happening ?

PROCEDURE load_dup_tables(load_status OUT varchar2) is

load_error_msg      varchar2(2000);
local_check_status  PLS_INTEGER := 1;

sif_link            DUP_DOCBASE_REQUESTS.DREQ_OUT2%TYPE := 'sif_link';
coa_link            varchar2(2000) :=   'coa_link';

sif_link_error_msg  varchar2(2000);
coa_link_error_msg  varchar2(2000);
oracle_error_msg    varchar2(2000);

memo_gate           varchar2(2) := 11;

TYPE memo_type_tt IS TABLE OF adm.new_dup_memo_alerts.memo_type%TYPE
    INDEX BY BINARY_INTEGER;
memo_types    memo_type_tt;

TYPE memo_alert_id_tt IS TABLE OF adm.new_dup_memo_alerts.memo_alert_id%TYPE
    INDEX BY BINARY_INTEGER;
memo_alert_ids    memo_alert_id_tt;

TYPE Project_Name_tt IS TABLE OF ADM.DUP_MEMO_STATUS_10.Project_Name%TYPE
    INDEX BY BINARY_INTEGER;
Project_Names    Project_Name_tt;

TYPE Project_Description_tt IS TABLE OF ADM.DUP_MEMO_STATUS_10.Project_Description%TYPE
    INDEX BY BINARY_INTEGER;
Project_Descriptions    Project_Description_tt;

TYPE Activity_Name_tt IS TABLE OF ADM.DUP_MEMO_STATUS_10.Activity_Name%TYPE
    INDEX BY BINARY_INTEGER;
Activity_Names    Activity_Name_tt;

TYPE Activity_Description_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Activity_Description%TYPE
    INDEX BY BINARY_INTEGER;
Activity_Descriptions    Activity_Description_tt;

TYPE Report_or_Study_No_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Report_or_Study_No%TYPE
    INDEX BY BINARY_INTEGER;
Report_or_Study_Nos    Report_or_Study_No_tt;

TYPE Start_Date_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Start_Date%TYPE
    INDEX BY BINARY_INTEGER;
Start_Dates    Start_Date_tt;

TYPE End_Date_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.End_Date%TYPE
    INDEX BY BINARY_INTEGER;
End_Dates    End_Date_tt;

TYPE Reg_Due_Date_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Reg_Due_Date%TYPE
    INDEX BY BINARY_INTEGER;
Reg_Due_Dates    Reg_Due_Date_tt;

TYPE Activity_status_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Activity_status%TYPE
    INDEX BY BINARY_INTEGER;
Activity_statuss    Activity_status_tt;

TYPE Activity_OBS_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Activity_OBS%TYPE
    INDEX BY BINARY_INTEGER;
Activity_OBSs    Activity_OBS_tt;

TYPE Memo_sent_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Memo_sent%TYPE
    INDEX BY BINARY_INTEGER;
Memo_sents    Memo_sent_tt;

TYPE ver_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.ver%TYPE
    INDEX BY BINARY_INTEGER;
vers    ver_tt;

TYPE resp1_tt IS TABLE OF av_element.resp1%TYPE
    INDEX BY BINARY_INTEGER;
resp1s    resp1_tt;

TYPE Responsible_person_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Responsible_person%TYPE
    INDEX BY BINARY_INTEGER;
Responsible_persons    Responsible_person_tt;

TYPE rn_tt IS TABLE OF av_resreq.rn%TYPE
    INDEX BY BINARY_INTEGER;
rns    rn_tt;

TYPE Tst_Subst_code_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Tst_Subst_code%TYPE
    INDEX BY BINARY_INTEGER;
Tst_Subst_codes    Tst_Subst_code_tt;

TYPE DREQ_OUT2_tt IS TABLE OF adm.dup_docbase_requests.DREQ_OUT2%TYPE
    INDEX BY BINARY_INTEGER;
sif_links    DREQ_OUT2_tt;

TYPE coa_link_tt IS TABLE OF VARCHAR2(2000)
    INDEX BY BINARY_INTEGER;
coa_links    coa_link_tt;

bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );

BEGIN
SAVEPOINT A1;

    begin

    begin
    select distinct
                dma.memo_type,
                dma.memo_alert_id,
                dup.Project_Name,
                dup.Project_Description,
                dup.Activity_Name,
                dup.Activity_Description,
                dup.Report_or_Study_No,
                dup.Start_Date,
                dup.End_Date,
                dup.Reg_Due_Date,
                dup.Activity_status,
                dup.Activity_OBS,
                dup.Memo_sent,
                dup.ver,
                e2.resp1 as Functional_mgr,
                dup.Responsible_person as Responsible_person,
                rq.rn as Resource_name,
                dup.Tst_Subst_code,
                nvl(doc.DREQ_OUT2,'NO SIF_LINK')
   BULK COLLECT INTO    memo_types, memo_alert_ids, Project_Names, Project_Descriptions,
                        Activity_Names, Activity_Descriptions, Report_or_Study_Nos, Start_Dates,
                        End_Dates, Reg_Due_Dates, Activity_statuss, Activity_OBSs, Memo_sents,
                        vers, resp1s, Responsible_persons, rns, Tst_Subst_codes,
                        sif_links--, coa_links
         from   ADM.DUP_MEMO_STATUS_10 dup,
                av_element e1,
                av_element e2,
                av_resreq rq,
                adm.new_dup_memo_alerts dma,
                adm.dup_docbase_requests doc

       where       dup.Project_Name    = dma.project
            and     dup.Activity_Name   = dma.activity
            and     dup.Project_Name    = rq.proj
            and     dup.Activity_Name   = rq.act
            and     rq.rn not like 'CONT%'
            and     rq.ver in ( 0,97 )
            and     e1.resp1<>e2.resp1
            and     (e2.category = 'GT OBS' AND e2.element = dup.Activity_OBS)
            and     nvl(dma.sent_flag,'N') = 'N'
            and     doc.DREQ_IN7(+) = dup.Project_Name
            and     doc.DREQ_IN8(+) = dup.Activity_Name
            and     dma.memo_type = memo_gate
            and     dup.memo_sent = memo_gate;

        if memo_alert_ids.count > 0 then
        forall av in memo_alert_ids.FIRST..memo_alert_ids.LAST
        insert into ADM.dup_memo_gate(
            memo_type, memo_alert_id, project_name, project_description, activity_name, activity_description, ver, study_no, start_date, end_date, reg_due_date, act_res_status, obs, func_mgr, uc04, rn, uc06, memo_sent, sif_link, time_stamp)
        values
        (   memo_types(av), memo_alert_ids(av), Project_Names(av), Project_Descriptions(av),
            Activity_Names(av), Activity_Descriptions(av), vers(av), Report_or_Study_Nos(av),
            start_dates(av), End_Dates(av), Reg_Due_Dates(av), Activity_statuss(av),
            Activity_OBSs(av), resp1s(av), Responsible_persons(av),
            rns(av), Tst_Subst_codes(av), memo_sents(av), sif_links(av), /*coa_links(av),*/
            to_date(to_char(sysdate,'DD-MON-YYYY'), 'DD-MON-YYYY'));
        end if;
    end;
        begin
        for av in (select distinct OBS, memo_alert_id from ADM.dup_memo_gate)
        loop
                select distinct nvl(coa_url_new(av.OBS, coa.expiration_date),'NO COA_LINK')
                bulk collect into coa_links
                from    dup_testsub_coaexp_nrm coa;
                if coa_links.count > 0 then
                forall av1 in memo_alert_ids.FIRST..memo_alert_ids.LAST
                SAVE EXCEPTIONS
                    update ADM.dup_memo_gate
                    set coa_link = coa_links(av1)
                    where memo_alert_id = memo_alert_ids(av1);
                end if;
        End loop;
            EXCEPTION
            WHEN bulk_errors THEN
                FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
                LOOP
                coa_link_error_msg :='Error ' || indx || ' occurred during iteration '
                    || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX || ' updating coa_link to '
                    || memo_alert_ids (SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
                oracle_error_msg := 'Oracle error is '
                                    ||SQLERRM (-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
                END LOOP;
        end;
    exception
        When bulk_errors1 Then
            load_error_msg := 'Error while inserting to dup_memo_gate '||SQLERRM;
            insert into adm.memo_log(error_text,log_date,entry_id)
            values(load_error_msg,
            to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
            memo_entry_id.nextval);
        when others then
        local_check_status := 0;
        load_error_msg := 'Error while inserting to dup_memo_gate '||SQLERRM;
                insert into adm.memo_log(error_text,log_date,entry_id)
                values(load_error_msg,
                to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
                memo_entry_id.nextval);

    end;

This is the error that is being logged.

Error while inserting to dup_memo_gate ORA-22160: element at index [2] does not exist

Report message to a moderator

Re: ORA-22160: element at index [2] does not exist [message #321197 is a reply to message #321194]

Mon, 19 May 2008 07:05

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Remove the "exception", reexecute and give us the line number of the error.
Of course, post the code WITH line numbers and REMOVE SPACES at the end of the lines.

Regards
Michel

Report message to a moderator

Re: ORA-22160: element at index [2] does not exist [message #321348 is a reply to message #321194]

Tue, 20 May 2008 00:39

NewLife
Messages: 170
Registered: April 2008

Senior Member

declare
load_error_msg      varchar2(2000);
local_check_status  PLS_INTEGER := 1;

sif_link            DUP_DOCBASE_REQUESTS.DREQ_OUT2%TYPE := 'sif_link';
coa_link            varchar2(2000) :=   'coa_link';

sif_link_error_msg  varchar2(2000);
coa_link_error_msg  varchar2(2000);
oracle_error_msg    varchar2(2000);

memo_gate           varchar2(2) := 11;

TYPE memo_type_tt IS TABLE OF adm.new_dup_memo_alerts.memo_type%TYPE
    INDEX BY BINARY_INTEGER;
memo_types    memo_type_tt;

TYPE memo_alert_id_tt IS TABLE OF adm.new_dup_memo_alerts.memo_alert_id%TYPE
    INDEX BY BINARY_INTEGER;
memo_alert_ids    memo_alert_id_tt;

TYPE Project_Name_tt IS TABLE OF ADM.DUP_MEMO_STATUS_10.Project_Name%TYPE
    INDEX BY BINARY_INTEGER;
Project_Names    Project_Name_tt;

TYPE Project_Description_tt IS TABLE OF ADM.DUP_MEMO_STATUS_10.Project_Description%TYPE
    INDEX BY BINARY_INTEGER;
Project_Descriptions    Project_Description_tt;

TYPE Activity_Name_tt IS TABLE OF ADM.DUP_MEMO_STATUS_10.Activity_Name%TYPE
    INDEX BY BINARY_INTEGER;
Activity_Names    Activity_Name_tt;

TYPE Activity_Description_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Activity_Description%TYPE
    INDEX BY BINARY_INTEGER;
Activity_Descriptions    Activity_Description_tt;

TYPE Report_or_Study_No_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Report_or_Study_No%TYPE
    INDEX BY BINARY_INTEGER;
Report_or_Study_Nos    Report_or_Study_No_tt;

TYPE Start_Date_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Start_Date%TYPE
    INDEX BY BINARY_INTEGER;
Start_Dates    Start_Date_tt;

TYPE End_Date_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.End_Date%TYPE
    INDEX BY BINARY_INTEGER;
End_Dates    End_Date_tt;

TYPE Reg_Due_Date_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Reg_Due_Date%TYPE
    INDEX BY BINARY_INTEGER;
Reg_Due_Dates    Reg_Due_Date_tt;

TYPE Activity_status_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Activity_status%TYPE
    INDEX BY BINARY_INTEGER;
Activity_statuss    Activity_status_tt;

TYPE Activity_OBS_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Activity_OBS%TYPE
    INDEX BY BINARY_INTEGER;
Activity_OBSs    Activity_OBS_tt;

TYPE Memo_sent_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Memo_sent%TYPE
    INDEX BY BINARY_INTEGER;
Memo_sents    Memo_sent_tt;

TYPE ver_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.ver%TYPE
    INDEX BY BINARY_INTEGER;
vers    ver_tt;

TYPE resp1_tt IS TABLE OF av_element.resp1%TYPE
    INDEX BY BINARY_INTEGER;
resp1s    resp1_tt;

TYPE Responsible_person_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Responsible_person%TYPE
    INDEX BY BINARY_INTEGER;
Responsible_persons    Responsible_person_tt;

TYPE rn_tt IS TABLE OF av_resreq.rn%TYPE
    INDEX BY BINARY_INTEGER;
rns    rn_tt;

TYPE Tst_Subst_code_tt IS TABLE OF adm.DUP_MEMO_STATUS_10.Tst_Subst_code%TYPE
    INDEX BY BINARY_INTEGER;
Tst_Subst_codes    Tst_Subst_code_tt;

TYPE DREQ_OUT2_tt IS TABLE OF adm.dup_docbase_requests.DREQ_OUT2%TYPE
    INDEX BY BINARY_INTEGER;
sif_links    DREQ_OUT2_tt;

TYPE coa_link_tt IS TABLE OF VARCHAR2(2000)
    INDEX BY BINARY_INTEGER;
coa_links    coa_link_tt;

Begin
    begin
    select distinct
                dma.memo_type,
                dma.memo_alert_id,
                dup.Project_Name,
                dup.Project_Description,
                dup.Activity_Name,
                dup.Activity_Description,
                dup.Report_or_Study_No,
                dup.Start_Date,
                dup.End_Date,
                dup.Reg_Due_Date,
                dup.Activity_status,
                dup.Activity_OBS,
                dup.Memo_sent,
                dup.ver,
                e2.resp1 as Functional_mgr,
                dup.Responsible_person as Responsible_person,
                rq.rn as Resource_name,
                dup.Tst_Subst_code,
                nvl(doc.DREQ_OUT2,'NO SIF_LINK')

   BULK COLLECT INTO    memo_types, memo_alert_ids, Project_Names, Project_Descriptions,
                        Activity_Names, Activity_Descriptions, Report_or_Study_Nos, Start_Dates,
                        End_Dates, Reg_Due_Dates, Activity_statuss, Activity_OBSs, Memo_sents,
                        vers, resp1s, Responsible_persons, rns, Tst_Subst_codes,
                        sif_links--, coa_links
         from   ADM.DUP_MEMO_STATUS_10 dup,
                av_element e1,
                av_element e2,
                av_resreq rq,
                adm.new_dup_memo_alerts dma,
                adm.dup_docbase_requests doc

        where       dup.Project_Name    = dma.project
            and     dup.Activity_Name   = dma.activity
            and     dup.Project_Name    = rq.proj
            and     dup.Activity_Name   = rq.act
            and     rq.ver in ( 0,97 )
            and     e1.resp1<>e2.resp1
            and     (e2.category = 'GT OBS' AND e2.element = dup.Activity_OBS)
            and     nvl(dma.sent_flag,'N') = 'N'
            and     doc.DREQ_IN7(+) = dup.Project_Name
            and     doc.DREQ_IN8(+) = dup.Activity_Name
            and     dma.memo_type = memo_gate
            and     dup.memo_sent = memo_gate;

        if memo_alert_ids.count > 0 then
        forall av in memo_alert_ids.FIRST..memo_alert_ids.LAST
        insert into ADM.dup_memo_gate(
            memo_type, memo_alert_id, project_name, project_description, activity_name,
            activity_description, ver, study_no, start_date, end_date, reg_due_date,
            act_res_status, obs, func_mgr, uc04, rn, uc06, memo_sent, sif_link,
            /*coa_link,*/ time_stamp)
        values
        (   memo_types(av), memo_alert_ids(av), Project_Names(av), Project_Descriptions(av),
            Activity_Names(av), Activity_Descriptions(av), vers(av), Report_or_Study_Nos(av),
            start_dates(av), End_Dates(av), Reg_Due_Dates(av), Activity_statuss(av),
            Activity_OBSs(av), resp1s(av), Responsible_persons(av),
            rns(av), Tst_Subst_codes(av), memo_sents(av), sif_links(av), /*coa_links(av),*/
            to_date(to_char(sysdate,'DD-MON-YYYY'), 'DD-MON-YYYY'));
        end if;
    end;
    
        begin
        for av in (select distinct uc06, memo_alert_id from ADM.dup_memo_gate)
        loop
                select distinct nvl(adm.coa_url_new(av.uc06, coa.expiration_date),'NO COA_LINK')
                bulk collect into coa_links
                from    dup_testsub_coaexp_nrm coa;
                if coa_links.count > 0 then
                forall av1 in memo_alert_ids.FIRST..memo_alert_ids.LAST
                SAVE EXCEPTIONS
                    update ADM.dup_memo_gate
                    set coa_link = coa_links(av1)
                    where memo_alert_id = memo_alert_ids(av1);
                end if;
        End loop;
        end;

    end;

11:04:40 AM  ORA-22160: element at index [2] does not exist
11:04:40 AM  ORA-06512: at line 166

Line 166 starts from this line

forall av1 in memo_alert_ids.FIRST..memo_alert_ids.LAST
                SAVE EXCEPTIONS
                    update ADM.dup_memo_gate
                    set coa_link = coa_links(av1)
                    where memo_alert_id = memo_alert_ids(av1);
                end if;

Report message to a moderator

Re: ORA-22160: element at index [2] does not exist [message #321365 is a reply to message #321348]

Tue, 20 May 2008 01:19

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think you wanted to write:
forall av1 in coa_links.FIRST..coa_links.LAST

Regards
Michel

Report message to a moderator

Re: ORA-22160: element at index [2] does not exist [message #321366 is a reply to message #321365]

Tue, 20 May 2008 01:23

NewLife
Messages: 170
Registered: April 2008

Senior Member

I need to basically update the records with the value of coa_links , I will try your above sugesstion and get back with the result as soon as possible

Report message to a moderator

Previous Topic:	Information regarding dml operations
Next Topic:	DBMS_JOBS - Executed time report.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 05:04:37 CST 2024