Всем привет, ситуация следующая, если курсор пустой PersAccOrgInfo, то выдает ошибку
ora-06502 ----ошибка числа или значения
ora-06512 line 131
ora-06512 line 233
Ошибку бьет из-за того, что приходит пустое значение сюда p_in(i):=t_obj(p_contid(i).mtsaccountid,p_contid(i).mtscontractid,null,null);


begin
--List of values for where condition
for i in p_contid.first..p_contid.count loop
p_in.extend();

p_in(i):=t_obj(p_contid(i).mtsaccountid,p_contid(i).mtscontractid,null,null);
end loop;

Есть идеи как обойти это?
--------------------------------------------

declare

--to declare %rowtype for lookups
cursor lookups is
select "Id" cid,"MTSForisId" cfid
from "Job" where 1=2;

--to declare %rowtype for
cursor contracts is
select "Id" contractid,"ContactId" clientid,"MTSContractId" forisid
from "Contract" where 1=2;

cursor PersAccOrgInfo is
select
mtsname,
mtsnotes,
mtsaccountid,
mtscontractid,
mtsstatusid,
mtsclosingreasonid,
mtsblocktypeid,
mtsmethodcalculationid,
mtscodeword,
--mtsmethodinvdeliveryid,
--mtsinvoicedeliveryaddress,
--mtsdocumentformatid,
mtscreditlimit,
mtsnotdisconnectcrlim,
mtsbillinggroupid,
mtspaymentday,
mtscreditcalctypeid,
mtspersonalaccountid,
wstrustcathegoryid
from ocrm.mtsinvoicepersorgtmp@crm_ocrm
where exists (select 1 from "Contract" inner join "Account" on "Contract"."AccountId" = "Account"."Id" where "MTSContractId" = mtscontractid and "Account"."MTSForisID" = mtsaccountid)
and not exists (select 1 from "MTSInvoicePers" INNER JOIN "Account" ON "MTSInvoicePers"."MTSAccountId" = "Account"."Id" where "MTSPersonalAccId"=mtspersonalaccountid and "Account"."MTSForisID" = mtsaccountid and "MTSForisContractId" = mtscontractid);
--AND ROWNUM < 2;

type t_rec is table of lookups%rowtype index by varchar2(30);
type t_contracts is table of contracts%rowtype index by varchar2(30);

l_contract t_contracts;
l_contractid t_rec;
--l_persaccnoid t_rec;
l_status t_rec;
l_calcmethod t_rec;
l_invoicedelivery t_rec;
l_documentformat t_rec;
l_billinggroup t_rec;
l_persacctype t_rec;
l_trustcateg t_rec;


type t_PersAccOrgInfo is table of PersAccOrgInfo%rowtype index by binary_integer;
l_persaccno t_PersAccOrgInfo;
l_SuperVisor varchar2(100):='{410006E1-CA4E-4502-A9EC-E54D922D2C00}';

dml_errors exception;
pragma exception_init(dml_errors, -24381);
l_errors number;
l_errno number;
l_msg varchar2(4000);
l_idx number;
l_contr_err varchar2(1000);

v_accountid "SysLanguage"."Id"%type;
v_contractid "SysLanguage"."Id"%type;
v_status "SysLanguage"."Id"%type;
v_calcmethod "SysLanguage"."Id"%type;
v_invoicedelivery "SysLanguage"."Id"%type;
v_documentformat "SysLanguage"."Id"%type;
v_billinggroup "SysLanguage"."Id"%type;
v_persacctype "SysLanguage"."Id"%type;
v_trustcateg "SysLanguage"."Id"%type;


/*Function of getting Uid by Foris value*/
function fn_GetId(p_contype in t_contracts,v_idx in varchar2,p int)
return "ContactType"."Id"%type is
begin
if p=1 then --contract
if p_contype.exists(v_idx) then
return p_contype(v_idx).contractid;

else return null;
end if;
elsif p=2 then

if p_contype.exists(v_idx) then
return p_contype(v_idx).clientid;
else return null;
end if;

end if;

end;

/*Function of getting Uid by Foris value*/
function fn_GetId(p_contype in t_rec,v_idx in varchar2)
return "ContactType"."Id"%type is
begin

if p_contype.exists(v_idx) then
return p_contype(v_idx).cid;
else return null;
end if;

end;

/*Function of getting Uids for bulk colletion of ContactIds*/
procedure Pr_getContractId(p_contid in t_PersAccOrgInfo,p_out out t_contracts)
is

p_in t_tab_obj:=t_tab_obj(null,null,null,null);

type t_contractid is table of "Contract"."Id"%type index by pls_integer;
type t_clientid is table of "Contract"."MTSForisOwnerId"%type index by pls_integer;
type t_forisid is table of "Contract"."MTSContractId"%type index by pls_integer;

l_contractid t_contractid;
l_clientid t_clientid;
l_forisid t_forisid;

begin
--List of values for where condition
for i in p_contid.first..p_contid.count loop
p_in.extend();

p_in(i):=t_obj(p_contid(i).mtsaccountid,p_contid(i).mtscontractid,null,null);
end loop;

begin
select contractid,clientid,forisid bulk collect into l_contractid,l_clientid,l_forisid
from
(
select "Id" contractid,"AccountId" clientid,"MTSForisOwnerId"||'~'||"MTSContractId" forisid --!!! contract~client key value
from "Contract"
--where ("MTSForisOwnerId","MTSContractId") in (select contactid,contractid from table(p_in) where 1=1) --List of values
--where ("MTSContractId") in (SELECT contractid from table(p_in) where 1=1)
where ("MTSForisOwnerId","MTSContractId") in (select TO_CHAR(contactid),TO_CHAR(contractid) from table(p_in) where 1=1) --List of values
);

p_out.delete;--erase out collection
--populate out collection
for i in l_contractid.first..l_contractid.last loop

p_out(l_forisid(i)).contractid:=l_contractid(i);
p_out(l_forisid(i)).clientid:=l_clientid(i);
p_out(l_forisid(i)).forisid:=l_forisid(i);
end loop;

exception
when no_data_found then
dbms_output.put_line('No ForsiId at ContactTab');
when others then
dbms_output.put_line('fn_getContactId_errors:'||sqlerrm);
dbms_output.put_line('fn_getContactId_Stack:'||dbms_utility.format_error_stack);
dbms_output.put_line('fn_getContactId_BackTrace:'||dbms_utility.format_error_backtrace);
end;

end;

-----------------------------Main-------------------------------------
begin

--MTSStatusId
for c1 in (select "Id" cid,"MTSForisId" cfid from "MTSInvoiceStateForCS" where "MTSForisId" is not null) loop

l_status(c1.cfid).cid:=c1.cid;
l_status(c1.cfid).cfid:=c1.cfid;

end loop;

--!NoData for MTSClosingReasonId
--!NoData for MTSBlockTypeId

--MTSMethodCalculationId
for c1 in (select "Id" cid,"MTSForisId" cfid from "MTSCalculationMethod" where "MTSForisId" is not null) loop

l_calcmethod(c1.cfid).cid:=c1.cid;
l_calcmethod(c1.cfid).cfid:=c1.cfid;

end loop;

--MTSMethodInvDeliveryId
for c1 in (select "Id" cid,"MTSForisId" cfid from "MTSInvoiceDeliveryType" where "MTSForisId" is not null) loop

l_invoicedelivery(c1.cfid).cid:=c1.cid;
l_invoicedelivery(c1.cfid).cfid:=c1.cfid;

end loop;

--MTSDocumentFormatId
for c1 in (select "Id" cid,"MTSForisId" cfid from "MTSDocumentFormat" where "MTSForisId" is not null) loop

l_documentformat(c1.cfid).cid:=c1.cid;
l_documentformat(c1.cfid).cfid:=c1.cfid;

end loop;

--MTSBillingGroupId
for c1 in (select "Id" cid,"MTSForisID" cfid from "MTSBillingGroup" where "MTSForisID" is not null) loop

l_billinggroup(c1.cfid).cid:=c1.cid;
l_billinggroup(c1.cfid).cfid:=c1.cfid;

end loop;

--MTSPersonalAccountType(!MTSCreditCalcTypeId)
for c1 in (select "Id" cid,"MTSForisId" cfid from "MTSPersonalAccountType" where "MTSForisId" is not null) loop

l_persacctype(c1.cfid).cid:=c1.cid;
l_persacctype(c1.cfid).cfid:=c1.cfid;

end loop;

--WSTrustCathegoryId
for c1 in (select "Id" cid,"MTSForisId" cfid from "MTSTrustCategory" where "MTSForisId" is not null) loop

l_trustcateg(c1.cfid).cid:=c1.cid;
l_trustcateg(c1.cfid).cfid:=c1.cfid;

end loop;


dbms_output.put_line('Open PersAccOrgInfo');
open PersAccOrgInfo;
loop
fetch PersAccOrgInfo bulk collect into l_persaccno limit 1000;

begin
Pr_getContractId(l_persaccno,l_contract);--l_contract - out var.Populatig a collection of Contract\Contact ForisId->Uid values

for i in 1 .. l_persaccno.count loop


v_contractid:=fn_getid(l_contract,l_persaccno(i).mtsaccountid||'~'||l_persaccno(i).mtscontractid,1);--ContractGuid
v_accountid:=fn_getid(l_contract,l_persaccno(i).mtsaccountid||'~'||l_persaccno(i).mtscontractid,2); --AccountGuid

v_status:=fn_getid(l_status,l_persaccno(i).mtsstatusid);
v_calcmethod:=fn_getid(l_calcmethod,l_persaccno(i).mtsmethodcalculationid);
--v_invoicedelivery:=fn_getid(l_invoicedelivery,l_persaccno(i).mtsmethodinvdeliveryid);
--v_documentformat:=fn_getid(l_documentformat,l_persaccno(i).mtsdocumentformatid);
v_billinggroup:=fn_getid(l_billinggroup,l_persaccno(i).mtsbillinggroupid);
v_persacctype:=fn_getid(l_persacctype,l_persaccno(i).mtscreditcalctypeid);
v_trustcateg:=fn_getid(l_trustcateg,l_persaccno(i).wstrustcathegoryid);

l_contr_err:='persacc : '||l_persaccno(i).mtspersonalaccountid||' contr: '||l_persaccno(i).mtscontractid||' account: '||l_persaccno(i).mtsaccountid;

insert into "MTSInvoicePers" (
"MTSName",
"MTSNotes",
"MTSAccountId",
"MTSContractId",
"MTSStatusId",
--"MTSClosingReasonId",
--"MTSBlockTypeId",
"MTSMethodCalculationId",
--"MTSCodeword",
--"MTSMethodInvDeliveryId",
--"MTSInvoiceDeliveryAddress",
--"MTSDocumentFormatId",
"MTSCreditLimit",
"MTSNotDisconnectCrLim",
"MTSBillingGroupId",
"MTSPaymentDay",
"MTSPersonalAccountTypeId",--"MTSCreditCalcTypeId",
"MTSPersonalAccId",
"WSTrustCathegoryId",
"CreatedById",
"ModifiedById",
"MTSForisContractId",
"MTSForisOwnerId"
)
values (
l_persaccno(i).mtsname,
l_persaccno(i).mtsnotes,
v_accountid,--MTSForisOwnerId
v_contractid,--mtscontractid
v_status,--mtsstatusid
--mtsclosingreasonid,
--mtsblocktypeid,
v_calcmethod,--mtsmethodcalculationid
--l_persaccno(i).mtscodeword,
--v_invoicedelivery,--mtsmethodinvdeliveryid
--l_persaccno(i).mtsinvoicedeliveryaddress,
--v_documentformat,--mtsdocumentformatid
l_persaccno(i).mtscreditlimit,
l_persaccno(i).mtsnotdisconnectcrlim,
v_billinggroup,--mtsbillinggroupid
l_persaccno(i).mtspaymentday,
v_persacctype,--mtscreditcalctypeid,
l_persaccno(i).mtspersonalaccountid,
v_trustcateg,--wstrustcathegoryid
l_SuperVisor,
l_SuperVisor,
l_persaccno(i).mtscontractid,
l_persaccno(i).mtsaccountid

);
end loop;
commit;

exit when PersAccOrgInfo%notfound;
end loop;

end loop;
close PersAccOrgInfo;
dbms_output.put_line('Done');

end;
/

SQL*Plus,

Да, действительно удобнее, на платформе первый раз, поэтому не знаю всех тонкостей.
Если в курсоре PersAccOrgInfo есть какие-либо данные, то все записывается. Если же данные отсутствуют, сразу бьется ошибка....