oracle procedure becomes invalid

[amoroder]

Hello,

we have a oracle procedure that we call only from delphi ( not from other oracle procedures or packages )
Often this procedures becomes invalid and we don't understand why this happens.
When we recompile the procedure from sql, then we get no error and the procedures becomes valid, so there is no error in the procedure itself.

Is there a way to discover why this happens or is there a problem with our code we use to call the procedure?

Thanks
Andreas

begin
inherited;
try
sproc:=TOraStoredProc.Create(self);
with sproc do begin
StoredProcName:='sta.op_chk_leistanf';
Prepare;
parambyname('p_id').AsInteger:=Id;
parambyname('p_benuID').AsString:=GetUserData('benu_id');
execute;
if parambyname('p_info').AsString<>'' then
MessageDlg(parambyname('p_info').AsString, mtWarning, [mbOk], -1);
qrquery.refresh;
end;
finally
sproc.Free;
end;

end;

[AlexP]

Hello,

Please send the script for creating the stored procedure, also specify the IDE, ODAC and Oracle Server/Client versions.

[amoroder]

Hallo Alex,

we use
Delphi XE6 Update 1

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

ODAC 9.4.14

The pl/sql procedure ist this one

regards
Andreas

create or replace procedure op_chk_leistanf(p_id in number,p_benuid in number,p_info in out string,p_typ in out number) is
l_anz number;
l_id number;
l_kennr number;
l_leavid number;
l_ressid number;
l_anweid number;
l_error number;
l_result number;
l_info varchar2(4000);
cursor c_daten is
--select * from leist_anforderungen l where nvl(anfo_gen,0)=1
--and l.anfo_aufe_abte_id=l_id;
select *
from leist_anforderungen l
where nvl(anfo_gen, 0) = 1
and l.anfo_id in (select t2.leip_anfp_id from leist_pre_leistungen t2 )
and l.anfo_kennr in (select t5.aufe_kennr from op_doku_kopf t3
left outer join auf_abteilung t4 on t4.abte_id=t3.op_dokk_abte_id
left outer join auf_aufenthalt t5 on t5.aufe_id=t4.abte_aufe_id
where t5.aufe_kennr=l.anfo_kennr
and t3.op_dokk_id=p_id);


cursor c_daten_v is
select *
from leist_anforderungen l
where anfo_gen = 2 and anfo_gen is not null
and l.anfo_datum <= sysdate;


-- Start direkte Anforderung
procedure send_ris_acc (p_anfo_id in number,
p_anfo_kennr in number,
p_typ in string)
is
begin
if p_typ<>'D' then
delete from leist_temp_leistungen;
insert into leist_temp_leistungen (leis_id)
select l.leip_id from leist_pre_leistungen l where l.leip_anfp_id=p_anfo_id;
end if;

leist_gen_temp_leistungen('RXANF',p_anfo_id,null,null,p_anfo_kennr,1);

p_info:='Anforderung erfolgreich abgeschickt!';
delete from leist_pre_leistungen l where l.leip_anfp_id=p_anfo_id;

insert into ris_trs_log(ritr_datins,ritr_trs_id,ritr_typ,ritr_info)
values(sysdate,p_anfo_id,99,'update anfo_gen');
---
update leist_anforderungen l set l.anfo_gen=null where l.anfo_id=p_anfo_id;
exception
when others then
p_info:=sqlerrm;
end;




begin
-------------------------------------------
if nvl(p_typ,0)=10 then
select anfo_kennr,a.anfo_anwe_id into l_kennr,l_anweid from leist_anforderungen a where a.anfo_id=p_id;
send_ris_acc(p_id,l_kennr,'N');
leist_wf_aktion(l_anweid,p_benuid,900,1,l_error,l_result,l_info);
end if;


-------------------------------------------
if nvl(p_typ,0) = 0 then
begin
select k.op_dokk_abte_id into l_id from op_doku_kopf k where k.op_dokk_id=p_id;
for c in c_daten loop
select count(*) into l_anz from leist_pre_leistungen p where p.leip_anfp_id=c.anfo_id;
if l_anz>0 then
-- anfo_aufe_abte_id setzten
update leist_anforderungen t
set t.anfo_aufe_abte_id=l_id
where t.anfo_id=c.anfo_id;
send_ris_acc(c.anfo_id,c.anfo_kennr,'N');
end if;
end loop;
exception when others then
p_info:=sqlerrm;
end;
end if;


---------------------------------------
if nvl(p_typ,0) = 2 then
begin
for c in c_daten_v loop
select count(*) into l_anz from leist_pre_leistungen p where p.leip_anfp_id=c.anfo_id;
if l_anz>0 then
send_ris_acc(c.anfo_id,c.anfo_kennr,'V');
end if;
end loop;
exception when others then
p_info:=sqlerrm;
end;
commit;
end if;

---------------------------------------
if nvl(p_typ,0) = 4 then
begin
select anfo_kennr,anfo_leav_id into l_kennr,l_leavid from leist_anforderungen where anfo_id=p_id;
update leist_anforderungen set anfo_leav_id=null where anfo_id=p_id;
send_ris_acc(p_id,l_kennr,'D');

update leist_anwe_vormerkung t set t.leav_kennr=l_kennr,t.leav_refer_id=p_id,t.leav_info_ext='Geändert durch direkte Anforderung! ('||
p_benuid||')',
leav_benu_id=p_benuid,
leav_datupd=sysdate
where leav_id=l_leavid and l_leavid is not null;
log_and_del_record(p_benuid,l_leavid,'LEIST_ANWE_VORMERKUNG','U');

if l_leavid is not null then -- Reinhold 20120417
select leav_ress_id into l_ressid from leist_anwe_vormerkung where leav_id=l_leavid;
termine.gen_det_termine(l_leavid,l_ressid,p_benuid,p_info);
end if;


exception when others then
p_info:=sqlerrm;
end;
end if;


exception when others then
p_info:=sqlerrm;

end op_chk_leistanf;

[AlexP]

In addition, please send the scripts for creating the tables.